Solved

Delete record from a table that is selected in a combobox

Posted on 2013-05-10
11
1,210 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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
 

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

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

710 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