Update a specific record based on matching primary key ID

[zhao]

Hello everyone, I'm an extreme beginner with visual basic scripting.

My problem is that I am trying to update a field (called 'Sold' which is a yes/no checkbox column) for a specific record whenever an event is triggered. I have two forms (derived from two tables), one is called frmInventory and the other is called frmSales. In frmSales, I made a combo box called 'cboItemID' that allows the user to select from a list of items from my inventory table. Each selection from the list has 4 properties, the first of which is the 'Item ID' from the inventory table. Lastly, I have a field in both frmSales and frmInventory called 'Sold' as mentioned above. What I want to do is that whenever I check/uncheck the box in the 'Sold' field in frmSales, I want the 'Sold' field in frmInventory to check/uncheck as well, but only in the record with an 'Item ID' that matches the 'Item ID' from the combo box selection. In other words, I want to match the 'Sold' field in frmInventory with the 'Sold' field in frmSales, but for only the record that has the same 'Item ID' primary key as the one I picked from my selection in the combo box from frmSales.

What I know right now is that I have to make an event in the property sheet for the 'on_click' action with code builder. I'm just very confused about how to reference another table and check whether or not it's 'Item ID' primary key is identical to the one I specified from the combo box, and then take action to update the 'Sold' field if the IDs match.

 

[CJ_London]

I have to make an event in the property sheet for the 'on_click' action with code builder

Not necessarily - having 'clicked' the user might click again or abort their edit

I would suggest the proper location for it is the beforeupdate event for the form

The code required will be something like this

currentdb.execute("Update tblInventory SET Sold=" & me.sold & " WHERE ItemID=" & me.cboitemID)

However it does sound like your table design needs to be improved - I can't see why you need to have the sold value in two tables - you should simply be referencing it when required