multi select list box in a search form user selected selected criteria query

[woodey2002]

This problem is driving me crazy.

Hello there,
i am trying to create a search form for records in my access database. The search form will contain text boxes and a multi select list box. The user can enter their search criteria eg. surname, reg num, etc. in the text boxes. The multi select list box allows the user to select multiple counties which they have the option of including in the search. The user should be able to select or omit the criteria as they desire.


I have messed around with the code for the last days but i am unable to intergrate the user selected criteria from a multi select list box.


I have been able to fix to the basics like first name and surname search but i cant manage the user selection from the multi select list box of counties.

This in my attempt it's bad i know.

If Len(Me.List0 & vbNullString) Then
For Each Itm In ctl.ItemsSelected
strSQLWhere = "WHERE [county] Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
strSQLWhere = "WHERE [county] = " & Chr$(39) & Me.List0 & Chr$(39)
End If

strSQLWhere = strSQLWhere & strJoin
End If

Listbox info
The list box is a selection of counties its called list0 its a look up values in my counties table.

Name = List0
Row Source = SELECT [tblCounties].[CountyCode], [tblCounties].[County] FROM tblCounties;
Multi select = simple

I really like the subform the results are displayed in do you think i can make it open in a report format also?

Below is my code minus the failed list box intergration attempt to avoid confusion.

Many thaks once again all the way from Ireland. Kind regards

James

Private Sub cmdPlease_Click()
Dim strSQLHead As String
Dim strSQLWhere As String
Dim strSQLOrderBy As String
Dim strSQL As String
Dim strJoin As String


strJoin = " AND "
strSQLHead = "SELECT * FROM tblMemberDetails "

If Len(Me.txtSurname & vbNullString) Then
If (Me.chkLike) Then
strSQLWhere = "WHERE [surname] Like " & Chr$(39) & "*" & Me.txtSurname & "*" & Chr$(39)
Else
strSQLWhere = "WHERE [surname] = " & Chr$(39) & Me.txtSurname & Chr$(39)
End If

strSQLWhere = strSQLWhere & strJoin
End If

If Len(Me.txtFirstName & vbNullString) Then
If (Me.chkLike) Then
strSQLWhere = "WHERE [FirstName] Like " & Chr$(39) & "*" & Me.txtFirstName & "*" & Chr$(39)
Else
strSQLWhere = "WHERE [FirstName] = " & Chr$(39) & Me.txtFirstName & Chr$(39)
End If

strSQLWhere = strSQLWhere & strJoin
End If

If Len(strSQLWhere) Then
strSQLWhere = Left$(strSQLWhere, Len(strSQLWhere) - (Len(strJoin) - 1))
End If

strSQLOrderBy = "ORDER BY "
Select Case Me.fraOrderBy
Case 1
strSQLOrderBy = strSQLOrderBy & "[surname]"
Case 2
strSQLOrderBy = strSQLOrderBy & "[firstName]"
Case 3
strSQLOrderBy = strSQLOrderBy & "[regNumber]"
End Select

strSQL = strSQLHead & strSQLWhere & strSQLOrderBy

[ADezii]

1. You can build a partial SQL Where Clause based on a Multi-Select List Box with the following code:
   

   Dim strSQL As String
   Dim varItem As Variant
   Dim ctlList As Control
   Dim strSQLPre As String
   
   Set ctlList = Me![lstCountries]
   
   strSQLPre = "Where [Country] In ("
   
   Select Case ctlList.ItemsSelected.Count
     Case 0
       Exit Sub
     Case Else
       For Each varItem In ctlList.ItemsSelected
         strSQL = strSQL & "'" & ctlList.ItemData(varItem) & "',"
       Next varItem
   End Select
   
   strSQL = strSQLPre & Left$(strSQL, Len(strSQL) - 1) & ")"

2. If you selected 4 Countries, the Partial Clause would read:
   

   Where [Country] In ('France','England','Italy','Ireland')

3. There are much better SQL Experts/Moderators/Members than I, I'm sure a better approach will be forthcoming.

[OldBirdman]

listbox ItemData is a two column listbox, with the county numbers in column 0 (probably hidden) and the names in column 1. Decision 1 is to decide whether search is by county name, or county number. Which is in tblMemberDetail s, the name or the number?
In the query design grid, design your query as if it were always the same, and forget the names and region numbers. Just the counties. Use the "Create query in Design View" of the Database window. Select tblMemberDetail s from the Show Table dialog, and close. Drag the * to column 1. Drag the County to column 2. Use name or number as appropriate, I am going to assume name for now. Column 2, of Criteria row, enter a counties you know exists, say "Limerick" and below it, enter another, say "Tipperary" . Press the View button on the Query Design Toolbar (far left button). Do you get the results expected? If no, then you need to find out why, but it should work if you have these counties in the table. If not, pick 2 you know exist.
It works, so click the downarrow next to the View button, and select SQL. This is the SQL statement Access generates. It won't use the IN keyword as suggested by ADezii, but you can do that later.
Put the code from the query window into your code, assigning this string to strSQLHead replacing the loops in lines 6 to 17. Then include your strSQLHead so you have

strSQL = strSQLHead  * strSQLWhere

Forget for now the ORDER BY and the other conditions. This should work, and you should understand it.
When it works, add the ORDER BY and other WHERE conditions. Continue to refer to your query design window, adding your conditions so you can see an example of an SQL statement that does what you want.

Be aware that your listbox, ItemsSelected, is 2 columns, so you will have to tell access which column to use. ADezii's line 15 should be

strSQL = strSQL & "'" & ctlList.ItemData.column(0,varItem) & "',"

I am still assuming you are using the county name, not number.

Your name search, Like "*surname*" will find occurrances of imbedded names within the name. If you're looking for Oliver Stone, and enter "Sto" in your surname textbox, you will also find Pete Johnston. Is that what you want?

If Len(Me.txtSurname & vbNullString) Then

is always true when I run it. Does this statement really do what you want? Maybe

If Len(Me.txtSurname) <> 0 then
'Add to your SQL statement
Else
'Do nothing
End If

[OldBirdman]

Corrrect 2 Typo on previous post

strSQL = strSQLHead  * strSQLWhere

Should be

strSQL = strSQLHead  & strSQLWhere

~~~and~~~

strSQL = strSQL & "'" & ctlList.ItemData.column(0,varItem) & "',"

Should be

strSQL = strSQL & "'" & ctlList.ItemData.column(1,varItem) & "',"

[woodey2002]

1 error left

Major Thanks!!!

ADezii & OldBirdman

Your information really helped me understand what was going on. This problem was driving me nuts. Thank you so much for your time and knowledge.


I managed to create a Search form to meet my specification exactly but i have one error left to sort out.

The search feature works great it updates the subform of search results perfectly for all criteria. However i have added a option to printPreview a report based on the selected criteria which worked at first, my latest problem came to light after I added a Order By clause to my function which allows me to order results in the search results subform by Surname,FirstNa me and reg number.
The subform results part works fine i can order by Surname etc.

My Problem is if i hit the button to preview the report it get an error message

Run Time error 3075

Syntax error (missing operator) in a query expression '''ORDER BY [regNumber]',

My code is below any advice would be hugely apperiacted.

Option Compare Database
Option Explicit

Private Sub btnPreviewReport_Click()

Dim strSQLOrderBy As String
Dim stDocName As String

stDocName = "rptsearchresults1"
DoCmd.OpenReport stDocName, acPreview, WhereCondition:=BuildFilter & strSQLOrderBy

End Sub

Private Sub btnClear_Click()
    Dim intIndex As Integer
    
    ' Clear all search items
    Me.txtFirstName = ""
    Me.txtSurname = ""
    Me.txtRegNumber = ""
    
    ' De-select each item in County List (multiselect list)
    For intIndex = 0 To Me.lstCountyCode.ListCount - 1
        Me.lstCountyCode.Selected(intIndex) = False
    Next
    
End Sub

Private Sub btnSearch_Click()
    
    ' Update the record source
    'Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter
   
    ' Update the record source
    If BuildFilter = "" Then
        Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter
    Else
        Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew WHERE " & BuildFilter
    End If
    
    'Requery the subform
    Me.sbfrmSearchResults1.Requery
    
    
End Sub


Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim CountyCode As Variant
    Dim varItem As Variant
    Dim intIndex As Integer

Dim strSQLOrderBy As String
    
    varWhere = Null  ' Main filter
    CountyCode = Null  ' Subfilter used for countyCode
    
    ' Check for LIKE First Name
    If Me.txtFirstName > "" Then
        varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName & "*"" AND "
    End If
    
    ' Check for LIKE Last Name
    If Me.txtSurname > "" Then
        varWhere = varWhere & "[surname] LIKE """ & Me.txtSurname & "*"" AND "
    End If
    ' Check for  reg Number
    If Me.txtRegNumber > "" Then
        varWhere = varWhere & "[regnumber] like """ & Me.txtRegNumber & """  And "
   End If
    
  
     ' Check for CountyCode in multiselect list
    For Each varItem In Me.lstCountyCode.ItemsSelected
        CountyCode = CountyCode & " [tblMemberDetails_CountyCode] = """ & _
                    Me.lstCountyCode.ItemData(varItem) & """ OR "
        
    Next
    
    'Test to see if we have subfilter for CountyCode...
    If IsNull(CountyCode) Then
        ' do nothing
    Else
        ' strip off last "OR" in the filter
        If Right(CountyCode, 4) = " OR " Then
            CountyCode = Left(CountyCode, Len(CountyCode) - 4)
        End If
    
        'Add some parentheses around the subfilter
        varWhere = varWhere & "( " & CountyCode & " )"
    End If
    
     'Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = "''"
    Else
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
                   
    End If
    
    strSQLOrderBy = "ORDER BY "
    Select Case Me.fraOrderBy
    Case 1
        strSQLOrderBy = strSQLOrderBy & "[surname]"
    Case 2
        strSQLOrderBy = strSQLOrderBy & "[firstName]"
    Case 3
        strSQLOrderBy = strSQLOrderBy & "[regNumber]"
    End Select
    
BuildFilter = varWhere & strSQLOrderBy
    
    End Function

Many Thanks!
Have a Nice weekend

JAMES

[ADezii]

Enter the following Line of code between Lines 9 and 10 above, but be sure to REM it out or remove it later. Then, Copy-N-Paste the results here for us to View:

'After Line# 9 but Before Line# 10:
Debug.Print BuildFilter & strSQLOrderBy :Exit Sub

[woodey2002]

debug results

Hello these are my results.

Kind regards and thanks so much!


Someone mentioned to me that you can't add an order by clause to the wherecondition argument.

Is that true? if so whats a easy way to add grouping options ot my report and keep the ordering in my subform.

JAMES

'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' OR(  [tblMemberDetails_CountyCode] = "c" ) ORDER BY [surname]
DER BY [surname]

[woodey2002]

hello again and thanks so much for all your time.

My friend tole me i can't add an order by clause to the wherecondition argument. is that true? if so is there an easy way for me to keep the order by option to my subform and add some degree of user selected ordering to my report.

Kind Regards James

'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' OR( [tblMemberDetail s_CountyCode] = "c" ) ORDER BY [surname]
DER BY [surname]

[NeoPa]

No. It's not possible to add any ORDER BY info to the WhereCondition parameter.

WhereCondition Optional Variant. A string expression that's a valid SQL WHERE clause without the word WHERE.

ORDER BY is a separate clause in SQL.

Sorting is managed in a report within the design of the report itself.

[NeoPa]

I just had a quick dig around in case you're interested, and changing the sorting within a report using code is done using the GroupLevel(?).C ontrolSource property (array) of the report.

[woodey2002]

lost the order option now stuck with 2 multiselects listBx

Hi Guys and thanks for all the input.

I decided to lose the order by feature so that makes more straight forward.
However i have encountered a new problem.

I successfully integrated a multi select listbox for users to select and search for counties.

On the same page however I would like to integrate a similar multiselect box for nationality. I would like the user to be able to search for nationality with county or individually.

After inserting my nationality list box and adding the code. i can now only search for either
nationality or county and cant perform a combined search.

I think it may be something to do striping off last "OR".

Any help would be greatly appreciated


Many thanks for the lifeline. JAMES

Private Sub btnSearch_Click()
    
    ' Update the record source
    'Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter
   
    ' Update the record source
    If BuildFilter = "" Then
        Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter
    Else
        Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew WHERE " & BuildFilter
    End If
    
    'Requery the subform
    Me.sbfrmSearchResults1.Requery
    End Sub
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim CountyCode As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
    Dim NationalityCode As Variant
    Dim strSQLOrderBy As String
    
    varWhere = Null  ' Main filter
    CountyCode = Null  ' Subfilter used for colors
    NationalityCode = Null
    ' Check for LIKE First Name
    
If Me.txtFirstName > "" Then
        varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName & "*"" AND "
    End If
    
    ' Check for LIKE Last Name
    If Me.txtSurname > "" Then
        varWhere = varWhere & "[surname] LIKE """ & Me.txtSurname & "*"" AND "
    End If
     
    If Me.txtRegNumber > "" Then
        varWhere = varWhere & "[regnumber] like """ & Me.txtRegNumber & """  And "
   End If
    
 ' Check for Colors in multiselect list
    For Each varItem In Me.lstCountyCode.ItemsSelected
        CountyCode = CountyCode & " [tblMemberDetails_CountyCode] = """ & _
                    Me.lstCountyCode.ItemData(varItem) & """ OR "
        
    Next
    
    'Test to see if we have subfilter for colors...
    If IsNull(CountyCode) Then
        ' do nothing
    Else
        ' strip off last "OR" in the filter
        If Right(CountyCode, 4) = " OR " Then
            CountyCode = Left(CountyCode, Len(CountyCode) - 4)
        End If
    
        'Add some parentheses around the subfilter
        varWhere = varWhere & "( " & CountyCode & " )"
    End If
    
     'NationalityCode
        
        ' Check for Nationality in multiselect list
    For Each varItem In Me.lstNationality.ItemsSelected
        NationalityCode = NationalityCode & " [tblmemberdetails.NationalityCode] = """ & _
                    Me.lstNationality.ItemData(varItem) & """ OR "
        
    Next
    
    'Test to see if we have subfilter for colors...
    If IsNull(NationalityCode) Then
        ' do nothing
    Else
        ' strip off last "OR" in the filter
        If Right(NationalityCode, 4) = " OR " Then
            NationalityCode = Left(NationalityCode, Len(NationalityCode) - 4)
        End If
    
        'Add some parentheses around the subfilter
        varWhere = varWhere & "( " & NationalityCode & " )"
    End If
     
     'Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = "''"
    Else
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
                   
    End If
     BuildFilter = varWhere
     End Function

[woodey2002]

Hi guys,

My problem is now resolved thanks to some advice

Changed

varWhere = varWhere & "( " & CountyCode & " ) AND "

to

varWhere = varWhere & "( " & CountyCode & " ) "


Many thanks for all the time.

James