Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 11th, 2003, 10:16 PM
Authorized User
Points: 306, Level: 6
Points: 306, Level: 6 Points: 306, Level: 6 Points: 306, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , .
Posts: 54
Thanks: 2
Thanked 0 Times in 0 Posts
Default "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
    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
Reply With Quote
  #2 (permalink)  
Old November 11th, 2003, 10:40 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old November 12th, 2003, 09:49 PM
Authorized User
Points: 306, Level: 6
Points: 306, Level: 6 Points: 306, Level: 6 Points: 306, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , .
Posts: 54
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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


--- Tom
Reply With Quote
  #4 (permalink)  
Old November 12th, 2003, 10:06 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It still gets me a lot of times.





Sal
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Form events pavel VB How-To 3 April 22nd, 2004 07:06 AM
Delete script doesnt execute kosla78 Classic ASP Basics 1 November 19th, 2003 07:44 PM
capturing form closed events alexferrie C# 2 June 17th, 2003 10:53 AM



All times are GMT -4. The time now is 01:30 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.