Okay, that's an interesting idea. How do I write that VBA code?
Okay, that's an interesting idea. How do I write that VBA code?
What is the primary key of the other table?
The primary key of Table A is the autonumbered ID generated by Access. The Primary key of Table X is the ID number of the participant.
How would you search for the right record to update in Table X?
Can you just tell me why this isn't working? I got this far in the code and it looks like it runs fine (If I enter a new number in the school bds# field no errors occur and the form says "calculating," but when I look at Table B the field has not updated.
Private Sub School_BDS___BeforeUpdate(Cancel As Integer)
CurrentDb.Execute "UPDATE [Table A] INNER JOIN [Table B] ON [Table A].ID = [Table B].ID SET [Table B].[School BDS #] = [Table A].[School BDS #]"
End Sub
I guess you do not care for the way I assist people since you did not answer my question. Sorry.
I just don't understand your question. I was hoping to make it simpler.
Is there more than one field in the two tables that they share in common?
Table A and Table B have entirely identical fields.
In that case you can create a query for the RecordSource of your Form A that joins the two tables and includes the Table X field we need so it is easy to update making the UPDATE query unnecessary. Have you tried using the query builder to make a query that joins the two tables in a 1:1 relationship and returns the [School BDS #] field from both table so we can change them? The user need only see one of them. The 2nd field would be change in the BeforeUpdate event of the form as has been discussed earlier.
If you do not understand my instructions or simply want clarification on what I said, please post back and let me know.
Okay, I finally got this to work for anyone else who wants to know:
Private Sub School_BDS___AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
CurrentDb.Execute "UPDATE [table A] INNER JOIN [table b] ON [table A].ID=[table b].ID SET [table b].[School BDS #] = [table a].[School BDS #];", dbFailOnError
End Sub
Great! Are you ready to follow the link in my sig and mark this thread as Solved?