Update Query Current Record Only ?

[gabriellec]

I am running an Update query which executes on the close of a form. The form is used for engineers to enter units into the table and the query calculates the units into fees. Everything is working fine with the update query.

We are changing our fees soon. Does anyone have the answer as to how I make the Update Query only update the current record?. So when the form is closed only the record I was working in updates to the new fees?

 

[tlbroadbent]

You simply need to establish the criteria (Where Clause) in order to limit the scope of the Update. If there is a control (or controls) on your form that contains the criteria then include the value that control in your query. Terry

http://members.home.net/tlbroadbent/index.htm

 

[Amiel]

If you must rely on an update query, rather than using code in the 'Form_Close()' event in the form, the table that you are updating will have to have a unique value for each record.

Good table design will include an identity value for every row in the table. Access provides an 'autonumber' for this purpose. If your table doesn't have an autonumber go ahead and create a copy of your existing table (structure only), then edit the table and add the autonumber field (I've called it 'MyTableRecordID' here).

Then create an insert query using your data as the source and the new table as the destination, and fill the destination table. The Autonumber field will be filled automatically. Last. Rename the old table and give the new table the old tables name.

Now... your update query will use that unique value (the autonumber field) to perform the update. as in:

DoCmd.RunSQL "UPDATE [MyTable] Set [Field1] = "Updated In May" WHERE [MyTableRecordID]= Form![UniqueID]"

You may substitute other values for the 'unique' key for the table. Point is, you need to have a unique key for that table.

Enjoy.



Amiel
amielzz@netscape.net