T
Tom
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 completely ignored.
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!
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 completely ignored.
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!