Solved

Delete record from a table that is selected in a combobox

Posted on 2013-05-10
11
1,030 Views
Last Modified: 2013-05-10
I have an Access form with a combobox on it that I use to look up records in a table. What code do I put under a command button to delete the selected combobox record? Thank you.
0
Comment
Question by:dbfromnewjersey
  • 6
  • 5
11 Comments
 

Author Comment

by:dbfromnewjersey
ID: 39156494
An additional piece of information:  My table has 4 fields.  the first 3 fields make up the primary key.  The combobox has 4 columns.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39156550
Post your table and field names with examples of the type of data stored in each field.

Do you want this to happen immediately after the user selects a value in the Combo Box???

ET
0
 

Author Comment

by:dbfromnewjersey
ID: 39156672
Here's what I'm trying to do:

MyTable has 4 fields:

Field1
Field2
Field3
Field4

Fields 1, 2 and 3 make up the primary key, so Field4 isn't really relevant.

I have a combobox to select a record.  The combobox has a Column Count of 4.  Column0 displays Field1, Column1 displays Field2, Column2 displays Field3 and Column3 displays Field4.

Note: I think the column counts for comboboxes start at 0. That's why I'm using 0 instead of 1 for my description.

Let's say I make a combobox selection.  The combobox is now populated.  I want to click a command button to delete the record appearing in the combobox.

In plain English:    If ((Field1 = Combobox.Column(0)) and
                                (Field2 = Combobox.Column(1)) and
                                (Field3 = Combobox.Column(2)))
                               then delete record

Thank you
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39156752
Use the OnClick Event of your Command Button as shown below ....

CurrentDB.Execute  "DELETE FROM YourTable WHERE Field1 = '" & Me.Combobox.Column(0) & "' AND Field2 = '" & Me.Combobox.Column(1) & "' AND Field3 = '" & Me.Combobox.Column(2) & "';"

ET
0
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 300 total points
ID: 39156761
More specific ...

CurrentDB.Execute  "DELETE * FROM YourTable WHERE Field1 = '" & Me.Combobox.Column(0) & "' AND Field2 = '" & Me.Combobox.Column(1) & "' AND Field3 = '" & Me.Combobox.Column(2) & "';"

Also, let me know if Fields 1,2 and 3 are not text field.

ET
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:dbfromnewjersey
ID: 39156809
I haven't tried your code yet but to answer your question, Fields 1, 2 and 3 are text fields and I'm already limiting what can be entered into through code and "limit to list" comboboxes for my seperate Add Records procedure.
0
 

Author Comment

by:dbfromnewjersey
ID: 39156881
OK. It's working except for one thing.  After a record gets deleted, the combobox shows:

#Deleted  #Deleted #Deleted #Deleted  for each record I delete.

 Any thoughts on how to clear that out? Is there a way to refresh or requery after clicking the delete button so those line items won't appear in the combobox?  Thanks.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39156885
Ok, then this should work ....

CurrentDB.Execute  "DELETE * FROM YourTable WHERE Field1 = '" & Me.Combobox.Column(0) & "' AND Field2 = '" & Me.Combobox.Column(1) & "' AND Field3 = '" & Me.Combobox.Column(2) & "';"

Also, "Me" refers to the current form object.  You can also specify the form name as shown below.

Forms!YourFormName!Combobox.Column(1)


ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39156892
>>>>>OK. It's working except for one thing.  After a record gets deleted, the combobox shows:
#Deleted  #Deleted #Deleted #Deleted  for each record I delete.

 Any thoughts on how to clear that out? Is there a way to refresh or requery after clicking the delete button so those line items won't appear in the combobox?  Thanks.<<<<<

Try this ... after you run the delete SQL.

Me.Combobox.Requery

ET
0
 

Author Comment

by:dbfromnewjersey
ID: 39156927
That's what it was. Thanks. Everything works now. Much appreciated.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39157066
Glad to help.

ET
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now