help with delete and update queries

[larkeyshark]

I am trying to update addresses in another database when records in my database are changed. I do not need to check which actual field is changed, so I simply delete (via query) the corresponding addresses in the other database and replace with the current ones from my database (both databases use same primary key). This works fine on inserts and updates using "after insert" and "after update" to fire the 2 queries, but not with "On delete". I tried also "before del confirm" and "after del Confirm" but could not get the records to update. When I run the queries manually they work fine. I suspect it has to do with timing the event?
Thanks for helping.

 

[dhookom]

Could you try remove the ability to delete a record from the form? Then add a button that performs two delete queries.

Duane
MS Access MVP

 

[mok44]

Sorry Duane but I am not sure I understand your suggestion. I am using a main form (say with name info) and the addresses are in a subform. I want use the subform events to update the other database whenever a user updates addresses in mine.
Thanks.

 

[dhookom]

Why not put a command button on the main form. You can identify the pk value from the subform. Then delete records from the subform and the other table.

Dim strSQL as String
strSQL = "DELETE FROM tblA WHERE ID = " & Me.sfrmB.Form.txtID
DoCmd.RunSQL strSQL
strSQL = "DELETE FROM tblB WHERE ID = " & Me.sfrmB.Form.txtID
DoCmd.RunSQL strSQL

You might want to set warnings False then True to disable the confirmation dialogs.

Duane
MS Access MVP