CODEWITHSUNDEEP
sqlms-accessms-access-2007vba
Patrick
Patrick

Reputation: 113

Updating a record on another table that corresponds to the same record

I am trying to update a record on another table. So I have my first table InfoDetailsTable that has a bunch of records with one of the values being KitNumber. In this InfoDetailsTable table I am updating some checkboxes, and when they change it is supposed to update the Reason value of my table, InfoTable. I'm just not quite sure how to work my UPDATE statement so that I can update my specific InfoTable record based on the record I changed in InfoDetailsTable. Here is the code:

CurrentDb.Execute " UPDATE InfoTable SET Reason = 'blah' WHERE KitNumber = " & InfoDetailsTable.KitNumber

So InfoTable has Reason and KitNumber while InfoDetails also has KitNumber because they are in a relationship where the KitNumber on InfoTable is the primary key. If anyone could help that would be great. Thank you

EDIT: Here is some more code to maybe help.

If USBCheck = True And ThreewayCheck = True And Car1Check = True And Car2Check = True Then
 CurrentDb.Execute " UPDATE InfoTable SET Reason = '' WHERE KitNumber = " & InfoDetailsTable.KitNumber
ElseIf USBCheck = False Or ThreewayCheck = False Or Car1Check = False Or Car2Check = False Then
 CurrentDb.Execute " UPDATE InfoTable SET Reason = 'blah' WHERE KitNumber = " & InfoDetailsTable.KitNumber
End If

Forms!Search!Info.Form.Requery

If I edit the code like CurrentDb.Execute " UPDATE InfoTable SET Reason = 'blah' " then I get everything to work out fine, just that 'blah' in now updated to every record, where I only want it in the record I updated in InfoDetailsTable

Upvotes: 0

Views: 113

Answers (2)

ObieMD5
ObieMD5

Reputation: 2657

Use this to get from fields:

CurrentDb.Execute " UPDATE InfoTable SET Reason = 'blah' WHERE KitNumber = " & Me.KitNumber

When you are working with code from the current form, you need to reference it with Me. If it does not work still provide you link and I will look at it.

Upvotes: 1

basdwarf
basdwarf

Reputation: 422

If KitNumber is the identifying field for InfoTable (Primary Key), then what you have will work.

Upvotes: 0

Related Questions