Update not targeting correct record

Question

A subform is continuous, and contains a delete and save button for each record. Save seems to be working and changes a checkbox in another form to "-1" for "YES". However it's the delete button having a problem. When clicked, it changes the checkboxes to look as if every other record other than the one that was deleted except the record that truly was deleted for the particular client. Originally, I had wanted the checkbox to get the get check, and lose the check, by looking at the table where the apt exists rather than blindly adding or removing a checkmark. Maybe I should try that method again? But I could not come up with the proper syntax, so I did the following. I need this to work, or I need help make up an IF statement to check the table after delete or save is clicked: IF ApptIDs in Table A = table B, then checkbox=YES, else checkbox=NO.  Any help out there?

Private Sub Delete_Record_Click()

DoCmd.RunCommand acCmdDeleteRecord

DoCmd.SetWarnings False
CurrentDb.Execute "UPDATE [tblAppointment] INNER JOIN tblAccount ON [tblAppointment].[ApptID] = [tblAccount].[AcctApptID] SET [Added]=0"
DoCmd.SetWarnings True

Forms![frmClient]![subAccount].Form![comAccount].Requery

End Sub

Answer 1

I'm having a hard time following what you are doing. Let's focus on the Delete button. It should delete the current row, and:
DoCmd.RunCommand acCmdDeleteRecord
should do that just fine. Just like if you selected that row and press Delete on the keyboard.

So why running the update statement? Did you not just delete that appointment record?
And in that update statement, why joining with tblAccount?
And in the Execute statement, why not using dbFailOnError?
And why using DoCmd.SetWarnings block for a statement that does not pop up a warning?

I'm afraid you cobbled this together from some internet sources without too much understanding.

---

-Tom. Microsoft Access MVP

Answer 2

Hi 985Hunter,

Have your issue been resolved? I made a test with checkbox and acCmdDeleteRecord, the value for checkbox shows correctly. Have you binding checkbox to a field in your table?

Best Regards,

Edward

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].