SQL - How to delete according to the form's list box value

[Martin \(Martin Lee\)]

Why this VBA SQL can't work :

DoCmd.RunSQL "delete * from CUSTABLE1 where
DEALERNAME=[ABCFORM].LIST65.value"


explain:

DEALERNAME is a field of the table CUSTABLE1

[ABCFORM] is a form's name

LIST65 is a list box in the form [ABCFORM], this LIST BOX is available for
multichoises.


for example, I have Dealer1, Dealer2, Dealer3 in the LIST65 ( LIST BOX ). I
choose Dealer1 and Dealer2 together (I use "Ctr - mouse click choice" to
multiple choice two of the dealer in the LIST65 box). And then I run the
SQL. I want the SQL to delete Dealer1 and Dealer2 records, leaving Dealer3
untouched.

Why my SQL can't work ? How to solve this problem ?


Thanks!

Martin LEE
2007-02-27

 

[Damian S]

Hi Martin,

Your syntax is somewhat correct, but not quite. You will need to build a
list of items from your listbox. Assuming you have a single selected item,
your syntax would be:

DoCmd.RunSQL "delete * from CUSTABLE1 where DEALERNAME= '" &
forms![ABCFORM].LIST65.value & "'"

If you have multiple selected items, work out which items are selected and
join them into a variable so that they are represented as 'Dealer1', 'Dealer2'
then use the following syntax:

DoCmd.RunSQL "delete * from CUSTABLE1 where DEALERNAME in (" & VARIABLENAME
& ")"

Hope this helps.

Damian.

 

[Allen Browne]

For a multi-select list box, you will need to loop through the ItemsSelected
collection to build the WHERE clause of the query.

For an example of how to build the string, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

You will end up concatenating that expression into the WHERE clause, and
executing it. To know if it succeded or not, you might appreciate this also:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[storrboy]

Why this VBA SQL can't work :

DoCmd.RunSQL "delete * from CUSTABLE1 where
DEALERNAME=[ABCFORM].LIST65.value"

explain:

DEALERNAME is a field of the table CUSTABLE1

[ABCFORM] is a form's name

LIST65 is a list box in the form [ABCFORM], this LIST BOX is available for
multichoises.

for example, I have Dealer1, Dealer2, Dealer3 in the LIST65 ( LIST BOX ). I
choose Dealer1 and Dealer2 together (I use "Ctr - mouse click choice" to
multiple choice two of the dealer in the LIST65 box). And then I run the
SQL. I want the SQL to delete Dealer1 and Dealer2 records, leaving Dealer3
untouched.

Why my SQL can't work ? How to solve this problem ?

Thanks!

Martin LEE
2007-02-27

A quote from the help files...You can use the ListIndex property to return the index number for the
selected item. When the MultiSelect property is set to Extended or
Simple, you can use the list box's Selected property or ItemsSelected
collection to determine the items that are selected. In addition, when
the MultiSelect property is set to Extended or Simple, the value of
the list box control will always be Null.
<

One way around it is to enumerate the ItemsSelected collection, make a
comma separated list by concatenatng the ItemData values then
modifying your Delete query to include an IN clause. You'll need to do
this in a vb function or sub.

Dim varItem As variant
Dim itemList As String
For Each varItem in list65.ItemsSelected
itemList = itemList & "'" & List65.ItemData(varItm) & "',"
Next varItm

If itemList <> "" Then
itemList=Left(itemList,Len(itemList)-1)
DoCmd.RunSQL "DELETE * FROM custable WHERE DealerName IN ( " &
itemList & " );"
End If

 

[Martin \(Martin Lee\)]

Thanks for the reply of Damian, Allen and Storrboy.

However, the code can't work. It results in only delete 0 record. Please see
the below VBA
***************************
Dim varItem As Variant
Dim itemList As String
For Each varItem In List65.ItemsSelected
itemList = itemList & "'" & List65.ItemData(varItm) & "',"
Next varItem

If itemList <> "" Then
itemList = Left(itemList, Len(itemList) - 1)

DoCmd.RunSQL "DELETE * FROM CUSTABLE1 WHERE DEALERNAME in (" & itemList
& ")"

*******************************


ON THE OTHER HAND, THE BELOW CODE CAN WORK. It results in deleting the
records of 'dealer1' and 'dealer2' :

DoCmd.RunSQL "DELETE * FROM CUSTABLE1 WHERE DEALERNAME in
('dealer1','dealer2')"



Please help me Thanks!

Martin

 

[storrboy]

Thanks for the reply of Damian, Allen and Storrboy.

However, the code can't work. It results in only delete 0 record. Please see
the below VBA
***************************
Dim varItem As Variant
Dim itemList As String
For Each varItem In List65.ItemsSelected
itemList = itemList & "'" & List65.ItemData(varItm) & "',"
Next varItem

If itemList <> "" Then
itemList = Left(itemList, Len(itemList) - 1)

DoCmd.RunSQL "DELETE * FROM CUSTABLE1 WHERE DEALERNAME in (" & itemList
& ")"

*******************************

ON THE OTHER HAND, THE BELOW CODE CAN WORK. It results in deleting the
records of 'dealer1' and 'dealer2' :

DoCmd.RunSQL "DELETE * FROM CUSTABLE1 WHERE DEALERNAME in
('dealer1','dealer2')"

Please help me Thanks!

Martin

Why this VBA SQL can't work :

DoCmd.RunSQL "delete * from CUSTABLE1 where
DEALERNAME=[ABCFORM].LIST65.value"

explain:

DEALERNAME is a field of the table CUSTABLE1

[ABCFORM] is a form's name

LIST65 is a list box in the form [ABCFORM], this LIST BOX is available for
multichoises.

for example, I have Dealer1, Dealer2, Dealer3 in the LIST65 ( LIST BOX ).
I choose Dealer1 and Dealer2 together (I use "Ctr - mouse click choice"to
multiple choice two of the dealer in the LIST65 box). And then I run the
SQL. I want the SQL to delete Dealer1 and Dealer2 records, leaving Dealer3
untouched.

Why my SQL can't work ? How to solve this problem ?

Martin LEE
2007-02-27

I didn't realize I spelled a variable wrong.
Could be because & List65.ItemData(varItm) & "',"
should be & List65.ItemData(varItem) & "',"

 

[Martin \(Martin Lee\)]

Thanks Storrboy!

It really works! Yet, it is quite headache to find out a mistaken word in
the VBA. Is there any good method to find out a mistake word efficiently ?

And, your program vba really help me! Thank you very much!


Martin

 

[storrboy]

Thanks Storrboy!

It really works! Yet, it is quite headache to find out a mistaken word in
the VBA. Is there any good method to find out a mistake word efficiently?

And, your program vba really help me! Thank you very much!


Martin

Why this VBA SQL can't work :

DoCmd.RunSQL "delete * from CUSTABLE1 where
DEALERNAME=[ABCFORM].LIST65.value"

explain:

DEALERNAME is a field of the table CUSTABLE1

[ABCFORM] is a form's name

LIST65 is a list box in the form [ABCFORM], this LIST BOX is available for
multichoises.

for example, I have Dealer1, Dealer2, Dealer3 in the LIST65 ( LIST BOX ).
I choose Dealer1 and Dealer2 together (I use "Ctr - mouse click choice"to
multiple choice two of the dealer in the LIST65 box). And then I run the
SQL. I want the SQL to delete Dealer1 and Dealer2 records, leaving Dealer3
untouched.

Why my SQL can't work ? How to solve this problem ?

Martin LEE
2007-02-27

Do your code modules include the statement Option Explicit (usually
found in the top few lines)? This statement tells the compiler that
all variables must be declared (using a Dim statement). If this
statement is there and you attempted to run or compile the code,
Access would flag it as an undeclared variable. I didn't catch it as I
was typing that right in the post message.