Re: Multi-Select ListBox

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:ea%23a%23VNXFHA.3032@xxxxxxxxxxxxxxxxxxxxxxx
> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Tom" <anonomys@xxxxxxxx> wrote in message
> news:enaQfLNXFHA.3996@xxxxxxxxxxxxxxxxxxxxxxx
>>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
>>
>> --
>> Thanks,
>> Tom
>>
>>
>>
>
>


.

Relevant Pages

  • RE: LookUp Form
    ... private sub cmdSearch_OnClick ... dim strWhere as string ... const BASE_SQL as string = "table or query for subform" ... for each ctl in controls ...
    (microsoft.public.access.formscoding)
  • Re: Multi-Select ListBox
    ... the selected values are then used as query criteria. ... > ' Remove the leading comma from the string ... > ' Build the new SQL statement incorporating the string ...
    (microsoft.public.access.forms)
  • Re: Multi-Select ListBox
    ... ' Remove the leading comma from the string ... ' Apply the new SQL statement to the query ... You have not closed the qdf so the Query has not been updated with the ... >> ' Remove the leading comma from the string ...
    (microsoft.public.access.forms)
  • Re: list box sort
    ... If you are already modifying the query, then building the entire SQL statement as a string in code might be even easier. ... > Dim strSQL as string ...
    (microsoft.public.access.formscoding)
  • Re: Error using " in .open with ADODB.Recordset
    ... recordset but I'm getting hung up on the SQL statement. ... query that has a string as a condition. ... Dim mrk As String ...
    (microsoft.public.data.ado)