Link to home
Create AccountLog in
Avatar of Jenkins
JenkinsFlag for United States of America

asked on

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.
Avatar of Jenkins
Jenkins
Flag of United States of America image

ASKER

An additional piece of information:  My table has 4 fields.  the first 3 fields make up the primary key.  The combobox has 4 columns.
Avatar of Eric Sherman
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
Avatar of Jenkins

ASKER

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
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
ASKER CERTIFIED SOLUTION
Avatar of Eric Sherman
Eric Sherman
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Jenkins

ASKER

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.
Avatar of Jenkins

ASKER

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.
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
>>>>>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
Avatar of Jenkins

ASKER

That's what it was. Thanks. Everything works now. Much appreciated.
Glad to help.

ET