very basic query - auto-update 1 field based on another table

[iainm]

It's been a long time since I did any access work, and I can't remember how to do this, even though I know it should be easy. I've got two tables in concern here - one is a set of equipment records, and the other contains the history of those units. The equipment table has an EQ# (primary key) and an old ID (unique). The History table has an EQ# and is related to equipment table on this. My users want me to add a field to the data entry form of the history table so that they can confirm when they're entering a new record that they have the right piece of equipment - they're all used to dealing with the old id. What I want to do is set it up so that the history table has an old ID field , and after update of the EQ# in the history form the old ID is automatically set to the value of the old ID associated with that EQ# in the equipment table. I figured I could write the SQL for this easily, but it hasn't proven to be so. Any help you can give would be apreciated.
cheers,
-Iain

 

[Isadore]

iainm: You can pull this off quite simply by using a DLookUp.

In the Source criteria of a textbox on the DataEntry Form just use the EQ# to get the old Id from the Equipment table.

=DLookUp("[ID]","tblEquipment","[EQ#]=Forms![myform]![mytextbox]&quot

..and by the way, I would be a bit cautious about using machine charcters in the field names. You can always set your caption in the table properties but leave the field name as EQNo or EQID.

 

[Isadore]

The DLookUp should read:

=DLookUp("[ID]","tblEquipment","[EQ#]=Forms![myform]![EQ#]&quot

...if you use EQ# for the name of that textbox; so my reference in the first post to "mytextbox" is the NAME of the textbox that has the EQ# value.

 

[Geekmomz]

If your form is based on a query, you can add the Equipment table to the query, join on EQ#, and add a column in the query for the old ID. Then you can add the old ID on your form in a read-only text box so your users can see it.