Update Query Using A Form

[SeaTigr]

Good day, all.

I'm designing an inventory database. At the moment, I have a form where users can enter an identifying piece of information about a piece of equipment (serial #, service tag #, etc.) and run a Select query. Another form shows the results of the query. I would like users to be able to utilize the results form to change information. As I understand things, this requires an Update query. Unfortunately, my Update query is updating all the records in the database. If I'm understanding correctly, this means my Criteria is not set correctly.

Is my hypothesis about the Criteria being set incorrectly correct? And, if so, how should I set the Criteria to ensure the Update query only updates the one record set?

[orange]

It sure sounds correct, if all records are being updated.

Review this info on UPDATE query.
So either backup your table, and/or create a select query with CRITERIA to show only the records you intend to UPDATE.

Then convert the Select query to an UPDATE query using the wizard.

see http://www.techonthenet.com/sql/update.php
and
http://www.w3schools.com/sql/sql_update.asp

[SeaTigr]

Thanks for the links. Such a silly error - my Criteria and Update To entries were in the same column...but I wasn't trying to update the same field.

[SeaTigr]

Okay, now there's another interesting problem.

So, the table being updated is the Equipment Table. The record is the System Name. The record stores a foreign key because the system names are stored in another table, Systems List.

The Query Results Form, which users update the table with (not enter new pieces of equipment), has a combo box which shows the system names. Updates are saved to the Equipment Table.

When I change the system name and run the Update query, the foreign key in the Equipment Table changes to the appropriate # of the new system. However, the record containing the name of the system being changed in the System List is being changed to the primary key number of the new system.

So, if the current system name is X and I use the Update query to change it to Y, the foreign key stored in the Equipment Table is changed from X to Y. But the record in the System List containing the text 'X' is changed to the ID # for Y.

How do I stop this?