?
Solved

Unable to Delete Record In Access 2007 Table Using VBA

Posted on 2011-10-25
23
Medium Priority
?
584 Views
Last Modified: 2012-05-12
I cannot seem to delete a particular record from a table using VBA. There are no errors. I get a message saying the deletion took place but it did not. How do I find out what is blocking the deletion?
0
Comment
Question by:VBABeginner
  • 10
  • 10
  • +1
22 Comments
 

Author Comment

by:VBABeginner
ID: 37024598
Urgent help needed.
0
 
LVL 14

Expert Comment

by:Muhammad Ahmad Imran
ID: 37024618
can you post vba
0
 
LVL 2

Expert Comment

by:phill_cahill
ID: 37025029
Are you enforcing referential integrity?  If so have you deleted the dependant records first?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:VBABeginner
ID: 37027844
I have three tables. One table lists software by make and manufaturer. It has an autonumber key field. The second table lists all of the subject matter experts in our company. The third table links them. There will be one software item assigned to a single subject matter expert. But a single subject matter expert can be assigned to many software items. The third linkage table has only two fields; the software ID and the email address of the SME. They are the combinational key for that table. I can write a query that will delete the row. But when I use the exact same SQL in a VBA module it says that it deleted the row but it really did not and it did not give me an error. I will copy the code:

strSQL ="DELETE LinkageTable.ID, LinkageTable.EmailAddress FROM LinkageTable "
strSQL = strSQL & "WHERE (((LinkageTable.ID = " & "" & intID & "" & ")  "
strSQL = strSQL & "AND ((LinkageTable.EmailAddress =  " & """" & strEmailAddress & """" & "));"

StrSQL is a string, intID is a long Integer, and strEmailAddress is a string.  The source of the variables are combo boxes. The form is linked to the LinkageTable. I use Message Boxes to show me the ID, email address, and SQL code. All look normal. Nothing happens! Thank you both.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37028749
Youre deleting an entire record vs individual fields, so try this cleaned up version:


strSQL ="DELETE * FROM LinkageTable WHERE ID = " &  intID &  "AND EmailAddress =  " & chr(34) & strEmailAddress & chr(34)
0
 

Author Comment

by:VBABeginner
ID: 37028750
I appreciate it. Thank you.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37028752
And run it using CurrentDB.Execute:

strSQL ="DELETE * FROM LinkageTable WHERE ID = " &  intID &  "AND EmailAddress =  " & chr(34) & strEmailAddress & chr(34)
CurrentDB.Execute strSQL, dbFailOnError

0
 
LVL 61

Expert Comment

by:mbizup
ID: 37028761
Oops - You were missing an important space before the AND, too...

strSQL ="DELETE * FROM LinkageTable WHERE ID = " &  intID &  " AND EmailAddress =  " & chr(34) & strEmailAddress & chr(34)
CurrentDB.Execute strSQL, dbFailOnError
0
 

Author Comment

by:VBABeginner
ID: 37028865
Dear mbizup,
     I followed your directions. This time I did not receive a notice that the deletion of one record took place. I did not get an error. But, the stubborn record remains. I can manually delete it from the table. I can use the query wizard and give it the ID and emailaddress and delete the record. But VBA seems to be unable to. Any other ideas would be appreciated. Is there a possible record lock? I turned off Referenctial Integrity on the relationships to see if that would help. No luck!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37028876
That should work...

If you can post a sample of your database with any sensitive data masked or removed, I'll take a look.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37028893
Just a quick note -

If this is code running 'in the background' and you are observing records on a form, you will have to requery that form for the deletion to be noticeable (it should be noticeable right away if you look at the records directly in the table).

<This time I did not receive a notice that the deletion of one record took place. I did not get an error.>

CurrentDB.Execute does not give the 'warning' that RunSQL does.  However, the dbfailOnError parameter associated with CurrentDB.Execute will let you know if your query fails, by giving you any error message encountered (this is a benefit of the Execute method - runSQL is not so informative).
0
 

Author Comment

by:VBABeginner
ID: 37028934
I will try to find a way to send you a version that doesn't have real email addresses in them. I trust a Genius but it would be irresponsible of me to not be prudent.

I have a form with combo boxes. The entire function of this form is to remove the link between the software item and the SME. This happens because my SMEs go to other projects and I have to unlink them and then link new SMEs. First I select the SME from the Table of SMEs. That table cascades the list of software that that SME is responsible for. I select the software item and then click on the 'Delete Link' command button. That button initiates the VBA code that takes the software ID and the SME email address and puts them into variables. The variables are used in the SQL statement to select the correct record for deletion. I don't rely on the form to confirm the deletion that is why I didn't requery it. I put a MsgBox in to show me the SQL code before execution and it looks exactly like what you sent me with the variable values included. It may take a day to figure out how to send a version without real names. But I do appreciate your help.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37029974
No trouble - anything posted out here is publically visible, not just to EE Members so while samples are a tremendous help in getting problems resolved, you do have to be very careful.
0
 

Author Comment

by:VBABeginner
ID: 37035778
You helped. Your code suggestion allowed for a 75% solution. If I try to delete one link, close the form, and then look in the table there is no change. If I go back into the form and do two unlinks in a row and close the form, only the first of the two is deleted. If I keep the form open and do any number of unlinks they are deleted. It seems that closing the form replaces the last delete. I am attaching the database. I use the unlink form to delete the link. You will see all when you see the code. This is all very unusual. Please look into this. Thank you team of experts and you mbizup for the code. Support-Copy-PreProd-N-1TrackerV.accdb
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37035894
Hi-
I'll take a look at it first thing  in the morning.   Offhand, it sounds like an issue with refreshing and/or requerying the form.
0
 

Author Comment

by:VBABeginner
ID: 37037797
I think you are right. It seems that I can delete one or more records but the last record I delete is always refreshed in the table. I played with it for hours last night and came to this conclusion. Thank you mbizup.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37038020
Can you briefly describe in terms of forms opened, items selected, buttons clicked, etc the steps that you are following (a list of steps to mimic what you are doing would be ideal)
0
 

Author Comment

by:VBABeginner
ID: 37038600
I open form frm RemoveSMESoftwareLinkage090611
Using drop downs I select the SME (the E-Mail_Address field at the botton of the form is filled in). Then I select the Software Manufacturer, then the Software Make and then the Software Version. When I select the software version the ID is populated by the record ID of the software. Now I have the two components for the linkage table. Finally I select 'Delete Record'. If I close out of the form the record will not be deleted (possibly refreshed). If I delete two records then close the form, the first record is deleted and the second is not deleted. It seems that when the form is closed it is refreshing the linkage table with the very last record.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 37040337
Okay - that's what I thought, but wanted to make sure.

Your issue actually makes good sense.  Your form is bound to the same table you are deleting records from.  Upon opening, the code takes you to a new record.  So as you make your selections, you are actually creating a new duplicate record.

When you click the delete button, the original record really does get deleted... but as soon as you close the form, you commit that new record you are creating to the table, so it appears as if nothing has happened.

To resolve this, you need to work with an unbound form (with a blank recordsource).  So, do the following:

- Unbind your form by removing its recordsource.
- Unbind all of your textboxes and combos by removing their control source
- Remove the code that takes you to a new record
- Set the Control source property of your ID textbox to the following:
    = [cboSMEVersion]
- Set the Control Source property of your Email textbox to:
   = [cboSME]

Both control sources should include the = sign.  Setting them up like this will make them display the combo values selected for version and sme.

Once you do that, your deletions will behave as expected.


0
 

Author Closing Comment

by:VBABeginner
ID: 37051258
I can understand exactly why you hold the rank of Genius! Thank you very much. I would have never thought of the cause of the issue on my own.
0
 

Author Comment

by:VBABeginner
ID: 37051260
You are awesome AND a Genius. Thank you.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37054613
I'm glad that worked out for you.

Thank you for the good words :)
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question