pull data from two tables in VBA

Question

Ok I'm sure this sort of thing has been asked a billion times but I'm needing something a little different than a normal Join.  I have two tables some field names that are the same.  These fields contain a list of possible data that needs to be filled into the other table based upon choices that are made on a form.  For example if the CustomerName is "John" then the address and filling would be read from table1 and put into table2.  Now I would assume that the proper way to do this would be some sort of SQL statement or making my open record set to reflect the two tables and then through some code magic I would let one field equal the other.  However if I just update the record set I'm going to have some issues because I have about 600 lines of code that is not referenced to the table name only the field name so instead of doing  

record_set.Fields("table.field1") = whatever

I did

record_set.Fields("field1") =whatever

So if I start calling anything from another table I will end up getting all sorts of errors because access wont know which CustomerName I am talking about.  I really don't want to have to re-code all 600 some lines of code if I dont have to.  I did think about renaming the fields so that there wouldn't be any overlapping.  Anyone out there have any good ideas for this one?

Answer 1

Sounds like you need an Update query. First create a SELECT query that joins table 1 and 2 on the keyfield. Then turn it into an Update query, updating the fields from table 1 with the values from table2.