Using a listbox to delete a record (1 Viewer)

camdizzle

New member
Local time
Today, 13:50
Joined
Feb 17, 2010
Messages
1
Hi folks,

Thank you for the wealth of information this site provides. I've have been finding wonderful solutions to small problems I've encountered while teaching myself Access.

I've reached a problem which I can not locate a solution to.

I would like to make a listbox to display records and when you select a record you can click a button to delete that record.

See my attached windows paint graphical representation.

 

DCrake

Remembered
Local time
Today, 20:50
Joined
Jun 8, 2005
Messages
8,632
This is a very simple procedure, once you highlight the desired record you click on the delete button.

Code:
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From Table Where FieldName=" & Me.Listbox & ";"
DoCmd.SetWarnings True
The Me.Listbox will pick up the value from the bound column in your listbox for the chosen record.

Don't forget that once the deletion has taken place you will have to refresh/requery the rowsource of the listbox to remove it from the list.
 

aqif

Registered User.
Local time
Today, 20:50
Joined
Jul 9, 2001
Messages
158
Assumming that the row source of your list box is
"Select ID, Name, Date, Notes from TblMyRecords"

Put this code behind your command button.

Code:
If IsNull(Me.LstRecords) Then
MsgBox "Please select a record from the list to delete", vbCritical
Else

DoCmd.SetWarnings False
If MsgBox("Are you sure you want to remove '" & Me.LstRecords.Column(1) & "' from the list?", vbYesNo + vbDefaultButton2 + vbCritical, "Warning") = vbYes Then
DoCmd.RunSQL "Delete * from TblMyRecords Where ID=" & Me.LstRecords
Me.LstRecords.Requery
End If
DoCmd.SetWarnings True

End If
 

newcomer

New member
Local time
Today, 20:50
Joined
Jan 8, 2013
Messages
9
Hi, everyone.

I'm having the same issue

I'd like to delete a record from a table, based on a listbox. The goal is to select a listbox entry and delete the corresponding record in the table. The problem is that I can only delete all the records in the table or no record at all.

If I use this code...

Dim strSQL As String
strSQL = "DELETE * FROM Feedbacks WHERE [Sending_Date] = #" & lstFeedbacks & "#
CurrentDB.Execute strSQL

... no record is deleted.

If I use this code...

Dim strSQL As String
strSQL = "DELETE * FROM Feedbacks WHERE '[Sending_Date] = #" & lstFeedbacks & "#'
CurrentDB.Execute strSQL

... all the records in the table are deleted.

I've tried other code but without any luck.

The Data:

- FEEDBACKS - Name of the table
- SENDING_DATE - Field to match (General Date type)
- LSTFEEDBACKS - Listbox

I would appreciate if someone could help me.
 

pr2-eugin

Super Moderator
Local time
Today, 20:50
Joined
Nov 30, 2011
Messages
8,494
Hello newcomer.. Welcome to AWF.. :)

This should actually be in a New thread.. In future if there is any question, please create a new thread so many people can help you.. Having said that.. Why have you surrounded the Criteria inside ##? in the first part of the code? # is used for dates only.. Also you seem to have missed a last " (double quotes)
Code:
strSQL = "DELETE * FROM Feedbacks WHERE [Sending_Date] =[B] [COLOR=Red]#[/COLOR][/B]" & lstFeedbacks & "[COLOR=Red][B]#[/B][/COLOR][COLOR=Blue][B]"[/B][/COLOR]
In VBA ' (single quotes) are used for commenting.. So whatever you write after ' will not be considered to be a part of the criteria, hence the second part does not work..
 

newcomer

New member
Local time
Today, 20:50
Joined
Jan 8, 2013
Messages
9
«This should actually be in a New thread.. In future if there is any question, please create a new thread so many people can help you...»

Sorry for the inconvenience. I'll make it right next time. ;)

Well, with the code that you suggested, I get this error:

«Run-time error '3705'

Syntax error (missing operator) in query expression '[Sending_Date] = 08-01-2013 16:15:16'.»

Believe me, I'm having a hard time finding the right code to this «delete query. :(
 

pr2-eugin

Super Moderator
Local time
Today, 20:50
Joined
Nov 30, 2011
Messages
8,494
Okay, what is the Data Type of the field [Sending_Date] in the table? Did you copy the code above exactly as it is?

Syntax error denotes that we are almost there..
 

pr2-eugin

Super Moderator
Local time
Today, 20:50
Joined
Nov 30, 2011
Messages
8,494
Okay before performing the DELETE via VBA.. Use the Query in a normal Query window and see if you actually get any data.. Also make use of the Debug.Print statement to see if you can see the List value inside the variable..

BTW.. Did you copy the Code I gave exactly? Because in the error you have presented I cannot see the ## symbols..
 

newcomer

New member
Local time
Today, 20:50
Joined
Jan 8, 2013
Messages
9
That's the point. If I use a «query object» I can easily make it work, but not within VBA and I want the query to run within VBA. I'm really stubborn as hell... :p

Didn't you tell me to remove the «##»? I did as you told me to:

strSQL = "DELETE * FROM Feedbacks WHERE [Sending_Date] = " & lstFeedbacks & ""

Or did I get you wrong?

I'll use the «Debug» command and let you know the results.

Thanks.
 

newcomer

New member
Local time
Today, 20:50
Joined
Jan 8, 2013
Messages
9
In the immediate window, I have exactly this:

Delete * From Feedbacks WHERE [Sending_Date] = 08-01-2013 16:15:16

I think some quotes are missing in the right part of the SQL clause. Which ones? :(
 

pr2-eugin

Super Moderator
Local time
Today, 20:50
Joined
Nov 30, 2011
Messages
8,494
Well I mentioned that the ## are used only for dates.. Since your criteria is a Date type.. you have to surround them with the symbols.. So copy the code as in post #5 and try again..
 

newcomer

New member
Local time
Today, 20:50
Joined
Jan 8, 2013
Messages
9
Now it's correct (ir it should be). The immediate windows shows...

Delete * From Feedbacks WHERE [Sending_Date] = #08-01-2013 16:15:16#

... but nothing happens. No message errors, but no deletion either. The record is still in the table. :(

The listbox (lstFeedbacks) gets the data directly from the Feedback table. Is there any possible conflict?
 

pr2-eugin

Super Moderator
Local time
Today, 20:50
Joined
Nov 30, 2011
Messages
8,494
Okay try to see if you get the data using a normal Qyery window..
Code:
SELECT * FROM Feedbacks WHERE [Sending_Date] = #08-01-2013 16:15:16#;
 

newcomer

New member
Local time
Today, 20:50
Joined
Jan 8, 2013
Messages
9
No records returned with the code you mentioned. But if I build a query from scratch and call it from VBA, it works:

QUERY NAME: Anyone
QUERY TYPE: Delete query
TABLE CHOSEN: Feedbacks
FIELD CHOSEN: Sending_Date
CRITERIA: Where Sending_Date = Forms!Name_of_the_form!lstFeedbacks
 

newcomer

New member
Local time
Today, 20:50
Joined
Jan 8, 2013
Messages
9
Sorry, my bad.

I can get the record using a normal query window.
 

newcomer

New member
Local time
Today, 20:50
Joined
Jan 8, 2013
Messages
9
Yeah, that's what I was suspecting when I changed the criteria field - a date formatting issue. VBA always expects dates to be in american format.

Anyway, with this new criteria field (a text type one), everything works fine, once this field also has unique records. From now on, I'll have to use:

DELETE * FROM <table_name> WHERE <field_name> = #" & Format(Regional_format_Date, "mm\/dd\/yyyy") & "#

Thank you both of you, pr2-eugin and spikepl. Your help was more than precious. :)
 

Users who are viewing this thread

Top Bottom