Update a Table Question

[autiger58]

Hi:



I have a VBA that will add a city and state when you enter a zip code using a form. What I sometimes need to do is import the same data in access (data with a zip code but without the city and state) and then run some kind of code that will look at the zip code field in the first table, look up the zip code in a second table and then return to the first table and add the city and state that matches that zip code in the city and state fields.

With the VBA I currently have, it does this as I enter the zip on a form. Can anyone point me in the direction of how I can accomplish this same task under the situation I described above?

Thanks in advance.

Michael

[rpeare]

You can't run code at the table level you either need to run code on a form or through a module.

If you want to run your process without having a form open create a module, add a function to the module then run the module.

[ssanfu]

Two options:
1) loop through each record and update (one at a time)
or
2) use an update query to update all of the city and state fields for a specific Zip. If you have 5 records with the same Zip, all 5 would be updated at the same time.

The pseudo code would be like
One record at a time:

'Open a records set to select the records
Single record: "SELECT Zip From TABLE1 WHERE Not IsNull(Zip ) and IsNull(City) and IsNull(State) Order By Zip"

If recordset returns records

'Start loop
'open a second recordset on Table2 to get the city and State
"SELECT City, State FROM TABLE2 WHERE Zip = '" & RS1("Zip") & "';"

If recordset returns records

rs.Edit
rs("City") = varCity  ',- variable name
rs("City") = varState  ',- variable name
rs.Update

Close second recordset (table 2)

Loop

'do cleanup stuff

Group records:

'Open a records set to select the records
Group records: "SELECT DISTINCT Zip From TABLE1 WHERE Not IsNull(Zip ) and IsNull(City) and IsNull(State) Order By Zip"

If recordset returns records

'Start loop

'open a second recordset on Table2 to get the city and State
"SELECT City, State FROM TABLE2 WHERE Zip = '" & RS1("Zip") & "';"

If recordset returns records


CurrentdB.Execute "UPDATE Table1 SET Table1.City = '" & varCity & "', Table1.State = '"& varState &"';"

Close second recordset (table 2)

Loop

'do cleanup stuff

This can be run from a button on the form.....

[autiger58]

Thanks Steve. I think I understand it now. I really appreciate it.