Remove record via selection from list box and button (1 Viewer)

candyskullsfishbones · May 16, 2019

Hi there, I have a form adding records to a table at the press of a button, and generating a preview of the records on a list box. My question is, is it possible to click on one of the records previewed in the list box and create a command button to remove the record selected on the list.

I have already created a button to completely delete all records from the table and update the preview, however I want to be able to remove just one at a time.

If this functionality is possible what kind of code would I use? Table name is Prep_List_Maker_Tbl and form name is Prep_List_Maker

Thanks in advance for your help <3

theDBguy · May 16, 2019

Hi. Let’s say your table has a PK called ID and the Listbox is bound to it. Then, you could try something like:

Code:

strSQL="DELETE FROM Prep_List_Maker_Tbl WHERE ID=" & Me.ListboxName
CurrentDb.Execute strSQL, dbFailOnError

Minty · May 16, 2019

Assuming your table has a Primary key the you could simply run a delete query on pressing the button. Something along the lines of

Code:

Dim sSql as Sstring

sSql = "DELETE * FROM Prep_List_Maker_Tbl WHERE YourPKID = " & Nz(Me.YourList,0)

Currentdb.Execute sSql, dbFailOnError

candyskullsfishbones · May 16, 2019

I don't have a primary key as I need to allow duplicates in the input fields and any attempt to create a primary key removed the ability to allow duplicate entries. the list is displaying the field name Costume if that alters it whatsoever

theDBguy · May 16, 2019

candyskullsfishbones said:
I don't have a primary key as I need to allow duplicates in the input fields and any attempt to create a primary key removed the ability to allow duplicate entries. the list is displaying the field name Costume if that alters it whatsoever

What is in the row source of the Listbox?

candyskullsfishbones · May 16, 2019

theDBguy said:
What is in the row source of the Listbox?

The row source is the field named Costume

theDBguy · May 16, 2019

candyskullsfishbones said:
The row source is the field named Costume

Sorry, that doesn’t make sense. Does the table only have one field? Normally, the row source would be the name of a table or a query or an SQL statement.

candyskullsfishbones · May 16, 2019

theDBguy said:
Sorry, that doesn’t make sense. Does the table only have one field? Normally, the row source would be the name of a table or a query or an SQL statement.

The Prep_List_Maker_tbl has 5 fields sorry, the list box is only displaying the costume field on the form

theDBguy · May 16, 2019

candyskullsfishbones said:
The Prep_List_Maker_tbl has 5 fields sorry, the list box is only displaying the costume field on the form

Okay, that makes sense. So now, go to the properties window of the Listbox and tell us what is in the row source property. And while you’re at it, also please tell us what number is in the bound column property. Thanks.

candyskullsfishbones · May 16, 2019

theDBguy said:
Okay, that makes sense. So now, go to the properties window of the Listbox and tell us what is in the row source property. And while you’re at it, also please tell us what number is in the bound column property. Thanks.

Row source: SELECT [Prep_List_Maker_Tbl].Costume FROM Prep_List_Maker_Tbl ORDER BY [Costume];

Bound column: 1

theDBguy · May 16, 2019

candyskullsfishbones said:
Row source: SELECT [Prep_List_Maker_Tbl].Costume FROM Prep_List_Maker_Tbl ORDER BY [Costume];

Bound column: 1

Thanks. That's much better. You could try this, but make sure you have a backup first:

Code:

strSQL="DELETE FROM Prep_List_Maker_Tbl WHERE Costume='" & Me.ListboxName & "'"
CurrentDb.Execute strSQL, dbFailOnError

Unfortunately though, this will delete all duplicate records as well. If you want to only delete one record, you should consider adding a primary key to the table.

candyskullsfishbones · May 16, 2019

theDBguy said:
Thanks. That's much better. You could try this, but make sure you have a backup first:

Code:

strSQL="DELETE FROM Prep_List_Maker_Tbl WHERE Costume='" & Me.ListboxName & "'" CurrentDb.Execute strSQL, dbFailOnError

Unfortunately though, this will delete all duplicate records as well. If you want to only delete one record, you should consider adding a primary key to the table.

Thank you! Works perfectly! Thank you so much for all your help!

theDBguy · May 16, 2019

candyskullsfishbones said:
Thank you! Works perfectly! Thank you so much for all your help!

Hi. You're welcome. Glad to hear you got it to work. Minty and I were happy to assist. Good luck with your project.

Remove record via selection from list box and button (1 Viewer)

candyskullsfishbones

Registered User.

theDBguy

I’m here to help

Minty

AWF VIP

candyskullsfishbones

Registered User.

theDBguy

I’m here to help

candyskullsfishbones

Registered User.

theDBguy

I’m here to help

candyskullsfishbones

Registered User.

theDBguy

I’m here to help

candyskullsfishbones

Registered User.

theDBguy

I’m here to help

candyskullsfishbones

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread