Multi-Select ListBox

T

Tom

I use a "Multi-Select" Listbox to select multiple values (consecutive or
non-consecutive)... the selected values are then used as query criteria.
Please refer to function below:

*********************************
Private Sub cmdOK_Click()

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")

' Loop through the selected items in the list box and build a text
string
For Each varItem In Me!lstRegions.ItemsSelected
strCriteria = strCriteria & ",'" &
Me!lstRegions.ItemData(varItem) & "'"
Next varItem

' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If

' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM tblData " & _
"WHERE tblData.Region IN(" & strCriteria & ");"

' Apply the new SQL statement to the query
qdf.SQL = strSQL

' Open the query
DoCmd.OpenQuery "qryMultiSelect"

' Empty the memory
Set db = Nothing
Set qdf = Nothing

End Sub

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



Now, I want to add a 2nd ListBox (ListBoxChooseReport) to the function
above. The rowsource of that ListBox is e.g. "City; State". So, I
introduced a CASE statement into the function a shown in the modified
function below:

*********************************
Private Sub cmdOK_Click()

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")

' Loop through the selected items in the list box and build a text
string
For Each varItem In Me!lstRegions.ItemsSelected
strCriteria = strCriteria & ",'" &
Me!lstRegions.ItemData(varItem) & "'"
Next varItem

' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If

' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)


' INTRODUCED CASE STATEMENT
'=====================

Select Case Me.ListBoxChooseReport.Value

Case "City": 'Shows all but STATE field
' Build the new SQL statement incorporating the string
strSQL = "SELECT ID, Region, City, Date FROM tblData " & _
"WHERE tblData.Region IN(" & strCriteria & ");"

Case "State": 'Shows all but CITY field
' Build the new SQL statement incorporating the string
strSQL = "SELECT ID, Region, State, Date FROM tblData " & _
"WHERE tblData.Region IN(" & strCriteria & ");"
End Select



' Apply the new SQL statement to the query
qdf.SQL = strSQL

' Open the query
DoCmd.OpenQuery "qryMultiSelect"

' Empty the memory
Set db = Nothing
Set qdf = Nothing

End Sub

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

With the CASE statement, I get the Run-Time error 3129: "Invalid SQL
statement; expected DELETE, INSERT, PROCEDURE, SELECT, or UPDATE.

Any ideas how I can fix it?

Tom
 
D

Douglas J. Steele

One possibility is the field Date in your SQL: that's a reserved word, and
should never be used for your own purposes.

If you can't change the database design, try putting square brackets around
that word:

strSQL = "SELECT ID, Region, State, [Date] FROM tblData " & _
"WHERE tblData.Region IN(" & strCriteria & ");"
 
T

Tom

Doug:

I still get the same error... once I click OK, the line "qdf.SQL" is
highlighted.

' Apply the new SQL statement to the query
qdf.SQL = strSQL

BTW, thanks for the info about the reserved word.

Any ideas what else might be wrong?
--
Thanks,
Tom


Douglas J. Steele said:
One possibility is the field Date in your SQL: that's a reserved word, and
should never be used for your own purposes.

If you can't change the database design, try putting square brackets
around that word:

strSQL = "SELECT ID, Region, State, [Date] FROM tblData " & _
"WHERE tblData.Region IN(" & strCriteria & ");"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Tom said:
I use a "Multi-Select" Listbox to select multiple values (consecutive or
non-consecutive)... the selected values are then used as query criteria.
Please refer to function below:

*********************************
Private Sub cmdOK_Click()

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")

' Loop through the selected items in the list box and build a text
string
For Each varItem In Me!lstRegions.ItemsSelected
strCriteria = strCriteria & ",'" &
Me!lstRegions.ItemData(varItem) & "'"
Next varItem

' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If

' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM tblData " & _
"WHERE tblData.Region IN(" & strCriteria & ");"

' Apply the new SQL statement to the query
qdf.SQL = strSQL

' Open the query
DoCmd.OpenQuery "qryMultiSelect"

' Empty the memory
Set db = Nothing
Set qdf = Nothing

End Sub

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



Now, I want to add a 2nd ListBox (ListBoxChooseReport) to the function
above. The rowsource of that ListBox is e.g. "City; State". So, I
introduced a CASE statement into the function a shown in the modified
function below:

*********************************
Private Sub cmdOK_Click()

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")

' Loop through the selected items in the list box and build a text
string
For Each varItem In Me!lstRegions.ItemsSelected
strCriteria = strCriteria & ",'" &
Me!lstRegions.ItemData(varItem) & "'"
Next varItem

' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If

' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)


' INTRODUCED CASE STATEMENT
'=====================

Select Case Me.ListBoxChooseReport.Value

Case "City": 'Shows all but STATE field
' Build the new SQL statement incorporating the string
strSQL = "SELECT ID, Region, City, Date FROM tblData " & _
"WHERE tblData.Region IN(" & strCriteria & ");"

Case "State": 'Shows all but CITY field
' Build the new SQL statement incorporating the string
strSQL = "SELECT ID, Region, State, Date FROM tblData " & _
"WHERE tblData.Region IN(" & strCriteria & ");"
End Select



' Apply the new SQL statement to the query
qdf.SQL = strSQL

' Open the query
DoCmd.OpenQuery "qryMultiSelect"

' Empty the memory
Set db = Nothing
Set qdf = Nothing

End Sub

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

With the CASE statement, I get the Run-Time error 3129: "Invalid SQL
statement; expected DELETE, INSERT, PROCEDURE, SELECT, or UPDATE.

Any ideas how I can fix it?

Tom
Click to expand...
Click to expand...
 
V

Van T. Dinh

I got confused with your code but it looks to me there are 2 problems:

1. The colon after each Case value should not be there. This probably
won't be the cause of the error since colons are used as statement separator
also.

2. You have not closed the qdf so the Query has not been updated with the
new SQL String. Hence, when you use the OpenQuery Method, you actually run
whatever was saved in the previously saved Query and not the SQL String you
have just created.

3. Of course, the Field "Date" should be encosed in square brackets just to
be sure that there is no mix-up with the Date() function as per Douglas'
reply..
 
T

Tom

Van T. Dinh:

Currently, I have the following... and I still get the same error: "Invalid
SQL statement..." while "qdf.SQL = strSQL" is highlighted.

Tom


.....
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

Select Case Me.ListBoxChooseReport.Value

Case "City"
' Build the new SQL statement incorporating the string
strSQL = "SELECT ID, Region, [City], [Date] FROM tblData " & _
"WHERE tblData.Region IN(" & strCriteria & ");"

Case "State"
' Build the new SQL statement incorporating the string
strSQL = "SELECT ID, Region, [State], [Date] FROM tblData " & _
"WHERE tblData.Region IN(" & strCriteria & ");"
End Select

' Apply the new SQL statement to the query
qdf.SQL = strSQL
....
....
 
V

Van T. Dinh

Is the original Query "qryMultiSelect" a SELECT Query? If it is not, change
it to a SELECT Query and save it.

Post the SQL String of the saved Query.

Before the statement "qdf.SQL = strSQL", add the statement:

Debug.Print qdf.SQL

Check the Immediate Window for the result of the constructed SQL and post
this also. The new error message indicates something wrong with the
constructed SQL String.
 
T

Tom

I added the "Debug.Print qdf.SQL"... now the query executes. The SQL code
of "qryMultiSelect" is shown below.

SELECT *
FROM tblData
WHERE (((tblData.Region) In ('North')));


.... but the results are not what I expected (even though, I believe to know
why... not sure how to solve it though).

I was under the impression that only the SQL code of the "matching CASE"
would be executed. So, if I select "City" in listbox 1, and select "East,
West" in listbox 2, then I only would get East/West coast cities.

On the contrary, if I had selected "State" in listbox 1, and then select
"East, West" in listbox 2, I though I would see East/West coast states.

Now -- with the DEBUG in front of the qdf.SQL, the query executes but I
actually execute the "qryMultiSelect" with all of its fields... so, both
Cities and States are shown.

I don't want that... again based on the selection of Listbox1, I want to
show Cities OR States... not both.

Any ideas how to overcome this?

Tom




Van T. Dinh said:
Is the original Query "qryMultiSelect" a SELECT Query? If it is not,
change
it to a SELECT Query and save it.

Post the SQL String of the saved Query.

Before the statement "qdf.SQL = strSQL", add the statement:

Debug.Print qdf.SQL

Check the Immediate Window for the result of the constructed SQL and post
this also. The new error message indicates something wrong with the
constructed SQL String.

--
HTH
Van T. Dinh
MVP (Access)





Tom said:
Van T. Dinh:

Currently, I have the following... and I still get the same error: "Invalid
SQL statement..." while "qdf.SQL = strSQL" is highlighted.

Tom


....
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

Select Case Me.ListBoxChooseReport.Value

Case "City"
' Build the new SQL statement incorporating the string
strSQL = "SELECT ID, Region, [City], [Date] FROM tblData " & _
"WHERE tblData.Region IN(" & strCriteria & ");"

Case "State"
' Build the new SQL statement incorporating the string
strSQL = "SELECT ID, Region, [State], [Date] FROM tblData " & _
"WHERE tblData.Region IN(" & strCriteria & ");"
End Select

' Apply the new SQL statement to the query
qdf.SQL = strSQL
...
...
Click to expand...
Click to expand...
 
V

Van T. Dinh

1. Check the Immediate Window and you will see the actual SQL String that
you constructed. Post the constructed SQL here (as I asked in the previous
post).

2. Did you add code to close the qdf so that the SQL String is updated into
the saved Query? (as I stated in one of mt earlier replies).

The Debug statement doesn't change the Query. If the Query didn't work
before and it works now, it is not because of the Debug statement.

If you answer my questions and do what I asked, it will probably be quicker
to solve the problem...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Requery after you modify the SQL statement in VBA Access? 3
MS ACCESS: SEARCH in a split form 0
Problem with Multi Select List Box 1
Multi Selection Combo Box Query 1
Multi Selection List Box Query 6
data type mismatch 4
Query Inside an SQL statement 3
Problem with form opening From List Box 3

Top