Solved

Delete record from a table that is selected in a combobox

Posted on 2013-05-10
11
1,166 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
Technology Partners: 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!

 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

763 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