Opening a form inside a loop in Access VBA (1 Viewer)

theDBguy · Jun 20, 2019

GregoryWest said:
No problem, here is what I am dealing with. I have a huge file that comes from the government once a quarter. It contains property onership information. There are 7 records types all stored together is a CSV flat file.

Now on one of the record types is the name an address information about the actual over. Two issues on these records. Even tho each 'owner' (Multiple records for joint ownership) has a unique owner code, the addresses are free form. This means I have to, in program code, make sure all the addresses are correct and there is one per person. Also the government likes to suffix the name field with codes like JT (Joint ownership) 1/4 (one quarter ownership) these all have to be removed.

What this part of the code looks at the owner record. if the record is new (I already have code to tell me if the record is new) then the code break up the owner record and put the information in to the access DB fields. At this point I need the user to be able to fine tune the editing of the new information. Mostly the suffixes of the name, but some times the street address.

I know my first thought was get the base dataset to be accurate. This is a non starter as the government will not/can not change the land titles system.

Hi. Thank you for the clarification. Okay, so let's say you have a recordset to parse the incoming data and then you display the address information on a form, so the user can tweak it, then it sounds like all you need to do is give the user a button to "save" the changes to the table. So, I think we're halfway there. You parse the data, put the address information to TempVars, then open the form, assign the TempVars to the form for display and user edit. Now, all we need to do is "save" the form data into the table. You can use an APPEND query for that or use code as well. Here's an example of using an APPEND query.

Code:

strSQL = "INSERT INTO TableName(Address, City, State, Zip) " _
    & " VALUES('" & Me.Address & "','" & Me.City & "','" & Me.State & "','" & Me.Zip & "')"
CurrentDb.Execute strSQL, dbFailOnError

GregoryWest · Jun 20, 2019

AH I see what you are saying. If I get this right, when a new record comes along. Put all the data up on the screen for the user to tweek, When the user is finished, have them click on the button that run script on form to do the updates, not have the calling program try and deal with them. That would work for sure!

theDBguy · Jun 20, 2019

GregoryWest said:
AH I see what you are saying. If I get this right, when a new record comes along. Put all the data up on the screen for the user to tweek, When the user is finished, have them click on the button that run script on form to do the updates, not have the calling program try and deal with them. That would work for sure!

Well, I couldn't see the whole picture because I am not familiar with your process, but I was thinking when you open the form and pause the code, I was expecting for you to have the user fix the data, if there's any problem with it. After the user is done verifying and fixing the data, the user would then confirm the data by saving it (one at a time). When all the data has been reviewed, then you're done. But, if you want to store all the incoming data into a temporary table first with all the corrections before adding them to the final table, then that's also possible. You can change the APPEND query to store the form's data to the temporary table instead of the real table. You'll just have to re-save the data from the temp table to the real table later on.

Opening a form inside a loop in Access VBA (1 Viewer)

theDBguy

I’m here to help

GregoryWest

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread