Update query gives wrong results

[Guest]

I have been trying to create an update query but I can't get it to run right.
When it does run it updates all of the fields for all records with the same
information from record 1..

I have a table for states and regions ie: Minnesota Central, California
Western.

I have a combo box that selects the state and region from tblStates, and
only displays the state. I then have a text box that does a DLOOKUP and
displays the region. This works perfect after I select the state I click on
the refresh button and the region changes to the correct region ie: Minnesota
/ Central.

I created an update query to update the region field in the table named
tblContacts. But when it runs it changes all of the records and when I
specify criteria it does not update any fields at all or deletes all the
field entries.

My update query is as follows:

Field: Region StateID
Table: tblContacts tblContacts
Update to: [Forms]![fmsContacts]![Region]
[Forms]![fmsContacts]![Combo59]

The Combo box 59 is the box that lists the state.

I don't know why this is not working as I am following other update queries
that I made in the past. Can you tell me where I am going wrong with this.

Thanks
Bill_De

 

[John Spencer]

1) When you reference a control on a form, you are only going to get the
value in the current record.
2) Why do you need to store both the state and the region? If you have the
state, you can get the region when you need it by simply including tbl
states in your queries.

If you still feel you need to update tblContacts, then add tblStates to the
update query. Join tblStates to TblContacts on the StateId fields.

Change UpDate To Under tblContacts.Region to [TblStates].[Region]
Update To: [TblStates].[Region]

Before you run the query (Menu Query: Run) save a backup copy of the data.
As you know, UPDATEs are not undo-able.