Delete multiple Records from Table by listbox

[kabilliu]

Hi all,
I have the following code for adding multiple records in a table (Tbl1), selecting by a form listbox (list0) and works fine:

Private Sub BtnAddRec_Click()
Dim rst As DAO.Recordset
Dim VItm As Variant
For Each VItm In Me!List0.ItemsSelected
Set rst = CurrentDb.OpenRecordset("Tbl1", dbOpenDynaset)
rst.AddNew
rst.Fields("DatePar") = Me.ParDate
rst.Fields("ID1") = Me.List0.ItemData(VItm)
rst.Fields("Tem") = Me.List1
rst.Update
Me.List0.SetFocus
Me.List2.Requery
Next
End Sub

List2 is another listbox linking with a query showing the added records, the DatePar is a dete format field and the List1 is a listbox for adding quantity.

So...I made the following code to delete records:

Private Sub BtnDelRec_Click()
Dim vItem As Variant
For Each vItem In Me!List0.ItemsSelected
DoCmd.RunSQL "Delete from Tbl2 Where ID1= " & Me.List0.ItemData(vItem)
Next
Me.List0.Requery
Me.List2.Requery
End Sub

Also this code works fine but when I change the SQL so delete selected records by a specific date defined from date form field (ParDate) I receive an error message (the DB can't find the field "|" etc). The code change is:

DoCmd.RunSQL "Delete from Tbl2 Where ID1= " & Me.List0.ItemData(vItem) And DatePar= " & Me.ParDate"

I think that the problem is on SQL syntax, or the date format, or something like that. I would like to inform you that the date format is a short date as dd/mm/yyyy. I can't find a solution. Any help is appreciated.
Thank you in advance.

 

[pbaldy]

Re: Dlete multiple Records from Table by listbox

I see a couple of problems. See if this helps:

http://www.baldyweb.com/BuildSQL.htm

 

[kabilliu]

Re: Dlete multiple Records from Table by listbox

Thanks Paul for your reply. I will look the site that you suggest and I will try...

 

[pbaldy]

Re: Dlete multiple Records from Table by listbox

No problem; post back if you're still stuck. Your concatenation is off and you need the date delimiters.

 

[kabilliu]

Re: Dlete multiple Records from Table by listbox

Unfortunately I can not find a solution. I tried delimiters also but nothing. Any other help ?

 

[pbaldy]

Re: Dlete multiple Records from Table by listbox

Hard to fix without seeing your effort.

 

[kabilliu]

Re: Dlete multiple Records from Table by listbox

2 of my efforts without success:

DoCmd.RunSQL "Delete from Tbl2 Where ID1= " & Me.List0.ItemData(vItem) And DatePar="#" & Me.ParDate & "#"

And

DoCmd.RunSQL "Delete from Tbl2 Where ID1= " & Me.List0.ItemData(vItem) And DatePar= "  & Format(Me.ParDate, dd/mm/yyyy)"

 

[pbaldy]

Re: Dlete multiple Records from Table by listbox

You aren't restarting the string after the first variable. Try

DoCmd.RunSQL "Delete * from Tbl2 Where ID1= " & Me.List0.ItemData(vItem) & " And DatePar=#" & Me.ParDate & "#"

 

[kabilliu]

Re: Dlete multiple Records from Table by listbox

Yes it works! Thank you very much Paul. You saved my day and my night ! Thanks again !

 

[pbaldy]

Happy to help!