"Delete Events" in Form Do Not Execute
I searched the archives and didn't see anything that comes close to this, so here goes.
I have a form that makes a temporary table on the fly, and then sets its record source to the temp table. The reason for this is a little too complicated to explain here. The temporary table has a prime key (PK) that is identical to the PK in a permanent table. When I delete records from the temporary table, I need to capture the PK numbers of the temp table records and then delete the equivalent records in the permanent table. The form is set up as a single, columnar form bound to the temp table.
To accomplish this, I use the Form "Delete" event to capture each PK of the records I am deleting from the temp table. I do this by building a string that is scoped at the Form module level and have it put a comma after each number in the string. Each record deleted adds another PK number and a comma (e.g. 34, 46, 56,).
Then, in the Form "Before_Delete_Confirm" event, I use a DoCmd.SetWarnings False to turn off the delete confirmation. I ask the user if they want to delete the records. If they indicate NO, I cancel the event, and set my PK number string to blank and set Warnings back to True. If they indicate YES, I take the string (e.g. "34, 46, 56,"), strip off the last comma and build the string into a string of the form: "WHERE [MyPKNbr] In (34, 46, 56) ;"
My final SQL string looks like this:
str_SQL = "DELETE * FROM <perm table> WHERE [MyPKNbr] In (34, 46, 56);"
In the Form "After_Delete_Confirm" event, I test the "Status" variable to see if the User cancelled. If they did, I exit. If not, I then do the following:
Dim db as DAO.Database
Set db = CurrentDb
set db = Nothing
I tried this with a table and a clone table in a new .MDB file to prototype this methodology and it worked perfectly. The records in the temp table and the permanent table were both deleted with no problem.
When I took my code from this prototype and inserted it into my real form, the Form "Delete" event would fire (once for each record that I had selected), but then it asks me to confirm deletion of the records. If I indicate YES, it deletes the records in the temp table and exits (i.e. it does NOT go to the Form "Before_Delete_Confirm" event or the Form "After_Delete_Confirm" event; it simply exits! If I say NO, it simply exits. In both cases, the other two events are [u]completely ignored!</u>
Can anyone think of any conditions or situations that would cause the Form "Before_Delete_Confirm" or the Form "After_Delete_Confirm" events to be ignored? :(
Your help is greatly appreciated!