Delete record from a table that is selected in a combobox

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.
dbfromnewjerseyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Eric ShermanConnect With a Mentor Accountant/DeveloperCommented:
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
 
dbfromnewjerseyAuthor Commented:
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
 
Eric ShermanAccountant/DeveloperCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
dbfromnewjerseyAuthor Commented:
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
 
Eric ShermanAccountant/DeveloperCommented:
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
 
dbfromnewjerseyAuthor Commented:
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
 
dbfromnewjerseyAuthor Commented:
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
 
Eric ShermanAccountant/DeveloperCommented:
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
 
Eric ShermanAccountant/DeveloperCommented:
>>>>>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
 
dbfromnewjerseyAuthor Commented:
That's what it was. Thanks. Everything works now. Much appreciated.
0
 
Eric ShermanAccountant/DeveloperCommented:
Glad to help.

ET
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.