update a table

[diloft]

Hi again. You have been very helpful with my previous issue, I am trying you again.

I want to update a main table if a secondary table has a particular value. I am assuming you have to write every field in a table when data is being updated; that you cannot just send one field a new value. If this is true, is there an easy way to pull the matching record from the main table and immediately update from the secondary table?

Such as sql1 = SELECT * FROM myTable WHERE aValue = keyValue
then turn around and sql2 = UPDATE myTable SET field1 = !fieldname, & field2 = !field2, etc,....

This code did not like the !fieldname or recordset!fieldname. So, do I have to read from the main table, put the values into variables then update main table using the variable names?

I appreciate any help you can give me. Even if it is telling me to limber up my fingers and start typing.

 

[GinaWhipp]

Okay, I'm not sure I get this but you are trying to UPDATE one table with the value from another table?

1. You can just UPDATE one field in a record in another table. However, you will need to relate those tables so you are not updating every record on table two. So running this...

UPDATE YourTable SET YourTable1.YourField = [YourTable2]![YourField];

...will update every record in the table to the same value unless you do...

UPDATE YourTable INNER JOIN YourField2 ON YourTable.YourField = YourTable2.YourField2 SET YourTable.YourField = [YourTable2]![YourField];

...or are you trying to do something else?

 

[diloft]

Thanks Gina. I put the code in and it works beautifully. I knew there was a short and sweet way to code it.

You GURUS are AWESOME!!!!!!!!!!!

 

[GinaWhipp]

You're welcome!