Problem Updating Field from External Table

Copper Contributor

I have a form name [Jobs] which has subform [JobDetails subform] connected. The subform has a field [EquipmentID] which displays the names of electronic equipment which is set to run an update query AfterUpdate, to pull in the price of that equipment in the [Equipment] table with matching [EquipmentID]. In order that only this record gets updated, I add to the update query the autonumber field [ID] in the [JobDetails] table where the associated [ID] on [JobDetails subform] matches. When the query is run however, I get the prompt asking me to enter the value for that field even though an ID number is displayed. 


As I want to be able to change the value of the price that is populated, I need for only the current records price to be updated after I select the name of the equipment.#


Any help on this would be greatly appreciated.

2 Replies
If your subform is Bound to JobDetails table, then you do not need to run an Update query. changing the price on the subform will automatically be saved to JobDetails table. with what you are doing, there is a chance that you will get various "messages" from the system. since the Form is Dirty and you are updating Same Record using SQL. the system will know that the table has the latest version, therefore it will warn you with that. Remember what is showing in the Form is a Snapshot of the one in the physical Table. therefore if you show the record in the form, you must Edit it using the same form.
Hi Arnel,

Thank you for taking the time to reply. Actually I wasn't clear in how I have the tables set up - the Equipment table is the one that has the rental price of each piece of equipment which I want to populate into the unit price field of the Job Details subform. The job details table has the unit price field which I want to be able to alter in the case where I would like to change the price on a case by case basis.

Can you think of how I can achieve this?