Unable to Delete Record In Access 2007 Table Using VBA

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?
VBABeginnerAsked:
Who is Participating?
 
mbizupCommented:
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
 
VBABeginnerAuthor Commented:
Urgent help needed.
0
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
can you post vba
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
phill_cahillCommented:
Are you enforcing referential integrity?  If so have you deleted the dependant records first?
0
 
VBABeginnerAuthor Commented:
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
 
mbizupCommented:
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
 
VBABeginnerAuthor Commented:
I appreciate it. Thank you.
0
 
mbizupCommented:
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
 
mbizupCommented:
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
 
VBABeginnerAuthor Commented:
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
 
mbizupCommented:
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
 
mbizupCommented:
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
 
VBABeginnerAuthor Commented:
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
 
mbizupCommented:
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
 
VBABeginnerAuthor Commented:
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
 
mbizupCommented:
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
 
VBABeginnerAuthor Commented:
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
 
mbizupCommented:
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
 
VBABeginnerAuthor Commented:
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
 
VBABeginnerAuthor Commented:
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
 
VBABeginnerAuthor Commented:
You are awesome AND a Genius. Thank you.
0
 
mbizupCommented:
I'm glad that worked out for you.

Thank you for the good words :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.