AfterDelConfirm Not Firing (1 Viewer)

I am so frustrated right now and just can't determine what my problem is! PLEASE HELP!

I am using Access 2007 to access SQL Server database tables. I have a form that just lists values in a table that only has two fields, both of which are displayed on the form. I have code in the Delete event to perform a validation to ensure that it is okay to delete the selected record. That seems to be working just fine.

I also have code in the AfterDelConfirm event to update other information in the table if the record is successfully deleted. This code is performed after an If statement checking the status field to ensure that the delete wasn't canceled. This event never fires and I can't figure out why! In fact, I'm never even getting the warning message to confirm the delete.

To be clear, I have already check that the checkbox for record changes confirm option is checked. I've also ensured that SetWarnings is set to true.

When I try to delete the record using the form, the result is that the form no longer shows the record. However, the physical record still exists in the table. If I close the form and reopen it, the record reappears.

Now for the really weird part -- I decided to create a standard form just using the Create > Multiple Items option for that same table. I simply added an embedded macro with a single msgbox command to each of the 3 delete events -- Delete, BeforeDelConfirm, and AfterDelConfirm. When I try to delete the record using this form, all three events are triggered and present each of the messages I told them to display in the msgbox commands. I also get the standard confirmation message warning me about deleting the record. I confirmed that in this case, the record is physically deleted from the table.

So, why is that form working and my customized one is not? I'm just baffled by this. I have other event procedures for my form, including BeforeUpdate, AfterUpdate, Open, and Dirty for each of my fields. They are all working just fine.

Can anyone help me solve this mystery?

Is the query in the RecordSource of the form based on a 1-many relationship? Are you expecting to delete thd 1-side record or the many-side record because you can only delete rows from the "lowest" level table in a multi-table join.

The AfterDeleteConfirm doesn't run because no record is being deleted.

Actually, the table in question is not related to any other tables. It is simply used as a lookup table for a drop-down list on another form. This form is to enable values to be added, changed, or deleted. In addition, there is a second numeric field in the table to associate the value type to the sort order of those values on reports.

In my Delete event procedure, I check to ensure that the value is not used in any records in another table (remember, there is no link). If the value is used, I don't allow the record to be deleted.

I also have code in the AfterDelConfirm event procedure to renumber the sort order field on the remaining records when a record (or multiple records) are deleted. Those are the only two fields in the table.