Update field from a different table

[esskaykay]

I have a cmdTest button in frmPermits where I’m attempting to update the Contr_Contact field in tblPermits from data in tblContractors “Contact” field. I need to update (or insert) the data rather than simply linking it to tblContractors as we need to keep the history of who was the contact person (i.e., contact persons change over time).

If I place a text box in my form with the control being tblContractors.Contact (named txtContact), it works fine if I code the cmdTest button’s OnClick as:

DoCmd.RunSQL "UPDATE tblPermits SET tblPermits.Contr_Contact = txtContact
WHERE ((([tblPermits.Record_No])=[forms]![frmPermits]![Record_No]))"

My question is, is it possible to code without having to create the txtContact text box?
Something like –

DoCmd.RunSQL "UPDATE tblPermits SET tblPermits.Contr_Contact = tblContractors.Contact
WHERE ((([tblPermits.Record_No])=[forms]![frmPermits]![Record_No]))"

It works fine as is, this is just a question as a way to make the form a bit cleaner.

Thanks,
SKK

 

[jsv2002]

Do you mean creating a new record if one does not exist? e.g check to see if a record under that name/number exists and if not create one passing the name from the form?

If so a combination of Dlookup and openargs would do the trick but not quite sure what you are trying to do.

Give a bit more detail might be able to help.

John

Ahh just read the question again think I know what you mean and may have done something similar will dig around and let you know

 

[esskaykay]

Ultimately the code will be placed on a combo box's After Update event. So, it could be a new record, but as my test, it's an existing record.