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
    db.Execute str_SQL
    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.

THE 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!






--- Tom

__________________
--- Tom

Show all code for form.

When you copied the code from one form to the other, you may have forgotten to go to the form properties and from there click the elipsis ... next to the Before_Delete_Comfirm event. This would explain why the event is not firing.

Try it and let us know.



Sal

Sal,
   I checked ... you were correct! That fixed the problem ... Thanks!


--- Tom

It still gets me a lot of times.





Sal