By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,398 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

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

P: 15
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.
Expand|Select|Wrap|Line Numbers
  1. If Len(Me.List0 & vbNullString) Then
  2. For Each Itm In ctl.ItemsSelected
  3. strSQLWhere = "WHERE [county] Chr(34) & ctl.ItemData(Itm) & Chr(34)
  4. Else
  5. strSQLWhere = "WHERE [county] = " & Chr$(39) & Me.List0 & Chr$(39)
  6. End If
  7.  
  8. strSQLWhere = strSQLWhere & strJoin
  9. 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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPlease_Click()
  2. Dim strSQLHead As String
  3. Dim strSQLWhere As String
  4. Dim strSQLOrderBy As String
  5. Dim strSQL As String
  6. Dim strJoin As String
  7.  
  8.  
  9. strJoin = " AND "
  10. strSQLHead = "SELECT * FROM tblMemberDetails "
  11.  
  12. If Len(Me.txtSurname & vbNullString) Then
  13. If (Me.chkLike) Then
  14. strSQLWhere = "WHERE [surname] Like " & Chr$(39) & "*" & Me.txtSurname & "*" & Chr$(39)
  15. Else
  16. strSQLWhere = "WHERE [surname] = " & Chr$(39) & Me.txtSurname & Chr$(39)
  17. End If
  18.  
  19. strSQLWhere = strSQLWhere & strJoin
  20. End If
  21.  
  22. If Len(Me.txtFirstName & vbNullString) Then
  23. If (Me.chkLike) Then
  24. strSQLWhere = "WHERE [FirstName] Like " & Chr$(39) & "*" & Me.txtFirstName & "*" & Chr$(39)
  25. Else
  26. strSQLWhere = "WHERE [FirstName] = " & Chr$(39) & Me.txtFirstName & Chr$(39)
  27. End If
  28.  
  29. strSQLWhere = strSQLWhere & strJoin
  30. End If
  31.  
  32. If Len(strSQLWhere) Then
  33. strSQLWhere = Left$(strSQLWhere, Len(strSQLWhere) - (Len(strJoin) - 1))
  34. End If
  35.  
  36. strSQLOrderBy = "ORDER BY "
  37. Select Case Me.fraOrderBy
  38. Case 1
  39. strSQLOrderBy = strSQLOrderBy & "[surname]"
  40. Case 2
  41. strSQLOrderBy = strSQLOrderBy & "[firstName]"
  42. Case 3
  43. strSQLOrderBy = strSQLOrderBy & "[regNumber]"
  44. End Select
  45.  
  46. strSQL = strSQLHead & strSQLWhere & strSQLOrderBy
Feb 19 '09 #1
Share this Question
Share on Google+
11 Replies


ADezii
Expert 5K+
P: 8,623
  1. You can build a partial SQL Where Clause based on a Multi-Select List Box with the following code:
    Expand|Select|Wrap|Line Numbers
    1. Dim strSQL As String
    2. Dim varItem As Variant
    3. Dim ctlList As Control
    4. Dim strSQLPre As String
    5.  
    6. Set ctlList = Me![lstCountries]
    7.  
    8. strSQLPre = "Where [Country] In ("
    9.  
    10. Select Case ctlList.ItemsSelected.Count
    11.   Case 0
    12.     Exit Sub
    13.   Case Else
    14.     For Each varItem In ctlList.ItemsSelected
    15.       strSQL = strSQL & "'" & ctlList.ItemData(varItem) & "',"
    16.     Next varItem
    17. End Select
    18.  
    19. strSQL = strSQLPre & Left$(strSQL, Len(strSQL) - 1) & ")"
  2. If you selected 4 Countries, the Partial Clause would read:
    Expand|Select|Wrap|Line Numbers
    1. 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.
Feb 20 '09 #2

100+
P: 675
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 tblMemberDetails, 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 tblMemberDetails 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
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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?

Expand|Select|Wrap|Line Numbers
  1. If Len(Me.txtSurname & vbNullString) Then
is always true when I run it. Does this statement really do what you want? Maybe
Expand|Select|Wrap|Line Numbers
  1. If Len(Me.txtSurname) <> 0 then
  2. 'Add to your SQL statement
  3. Else
  4. 'Do nothing
  5. End If
Feb 20 '09 #3

100+
P: 675
Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQLHead  * strSQLWhere
Should be
Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQLHead  & strSQLWhere  
~~~and~~~
Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQL & "'" & ctlList.ItemData.column(0,varItem) & "',"  
Should be
Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQL & "'" & ctlList.ItemData.column(1,varItem) & "',"  
Feb 20 '09 #4

P: 15
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,FirstName 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.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub btnPreviewReport_Click()
  5.  
  6. Dim strSQLOrderBy As String
  7. Dim stDocName As String
  8.  
  9. stDocName = "rptsearchresults1"
  10. DoCmd.OpenReport stDocName, acPreview, WhereCondition:=BuildFilter & strSQLOrderBy
  11.  
  12. End Sub
  13.  
  14. Private Sub btnClear_Click()
  15.     Dim intIndex As Integer
  16.  
  17.     ' Clear all search items
  18.     Me.txtFirstName = ""
  19.     Me.txtSurname = ""
  20.     Me.txtRegNumber = ""
  21.  
  22.     ' De-select each item in County List (multiselect list)
  23.     For intIndex = 0 To Me.lstCountyCode.ListCount - 1
  24.         Me.lstCountyCode.Selected(intIndex) = False
  25.     Next
  26.  
  27. End Sub
  28.  
  29. Private Sub btnSearch_Click()
  30.  
  31.     ' Update the record source
  32.     'Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter
  33.  
  34.     ' Update the record source
  35.     If BuildFilter = "" Then
  36.         Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter
  37.     Else
  38.         Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew WHERE " & BuildFilter
  39.     End If
  40.  
  41.     'Requery the subform
  42.     Me.sbfrmSearchResults1.Requery
  43.  
  44.  
  45. End Sub
  46.  
  47.  
  48. Private Function BuildFilter() As Variant
  49.     Dim varWhere As Variant
  50.     Dim CountyCode As Variant
  51.     Dim varItem As Variant
  52.     Dim intIndex As Integer
  53.  
  54. Dim strSQLOrderBy As String
  55.  
  56.     varWhere = Null  ' Main filter
  57.     CountyCode = Null  ' Subfilter used for countyCode
  58.  
  59.     ' Check for LIKE First Name
  60.     If Me.txtFirstName > "" Then
  61.         varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName & "*"" AND "
  62.     End If
  63.  
  64.     ' Check for LIKE Last Name
  65.     If Me.txtSurname > "" Then
  66.         varWhere = varWhere & "[surname] LIKE """ & Me.txtSurname & "*"" AND "
  67.     End If
  68.     ' Check for  reg Number
  69.     If Me.txtRegNumber > "" Then
  70.         varWhere = varWhere & "[regnumber] like """ & Me.txtRegNumber & """  And "
  71.    End If
  72.  
  73.  
  74.      ' Check for CountyCode in multiselect list
  75.     For Each varItem In Me.lstCountyCode.ItemsSelected
  76.         CountyCode = CountyCode & " [tblMemberDetails_CountyCode] = """ & _
  77.                     Me.lstCountyCode.ItemData(varItem) & """ OR "
  78.  
  79.     Next
  80.  
  81.     'Test to see if we have subfilter for CountyCode...
  82.     If IsNull(CountyCode) Then
  83.         ' do nothing
  84.     Else
  85.         ' strip off last "OR" in the filter
  86.         If Right(CountyCode, 4) = " OR " Then
  87.             CountyCode = Left(CountyCode, Len(CountyCode) - 4)
  88.         End If
  89.  
  90.         'Add some parentheses around the subfilter
  91.         varWhere = varWhere & "( " & CountyCode & " )"
  92.     End If
  93.  
  94.      'Check if there is a filter to return...
  95.     If IsNull(varWhere) Then
  96.         varWhere = "''"
  97.     Else
  98.  
  99.         ' strip off last "AND" in the filter
  100.         If Right(varWhere, 5) = " AND " Then
  101.             varWhere = Left(varWhere, Len(varWhere) - 5)
  102.         End If
  103.  
  104.     End If
  105.  
  106.     strSQLOrderBy = "ORDER BY "
  107.     Select Case Me.fraOrderBy
  108.     Case 1
  109.         strSQLOrderBy = strSQLOrderBy & "[surname]"
  110.     Case 2
  111.         strSQLOrderBy = strSQLOrderBy & "[firstName]"
  112.     Case 3
  113.         strSQLOrderBy = strSQLOrderBy & "[regNumber]"
  114.     End Select
  115.  
  116. BuildFilter = varWhere & strSQLOrderBy
  117.  
  118.     End Function
  119.  
Many Thanks!
Have a Nice weekend

JAMES
Feb 21 '09 #5

ADezii
Expert 5K+
P: 8,623
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:
Expand|Select|Wrap|Line Numbers
  1. 'After Line# 9 but Before Line# 10:
  2. Debug.Print BuildFilter & strSQLOrderBy :Exit Sub
Feb 21 '09 #6

P: 15
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


Expand|Select|Wrap|Line Numbers
  1. '' ORDER BY [surname]
  2. '' ORDER BY [surname]
  3. '' ORDER BY [surname]
  4. '' ORDER BY [surname]
  5. '' ORDER BY [surname]
  6. '' ORDER BY [surname]
  7. '' ORDER BY [surname]
  8. '' ORDER BY [surname]
  9. '' ORDER BY [surname]
  10. '' ORDER BY [surname]
  11. '' ORDER BY [firstName]
  12. '' ORDER BY [firstName]
  13. '' ORDER BY [firstName]
  14. '' ORDER BY [firstName]
  15. '' ORDER BY [firstName]
  16. '' ORDER BY [firstName]
  17. '' ORDER BY [firstName]
  18. '' ORDER BY [firstName]
  19. '' ORDER BY [firstName]
  20. '' ORDER BY [firstName]
  21. '' ORDER BY [firstName]
  22. '' ORDER BY [surname]
  23. '' ORDER BY [surname]
  24. '' ORDER BY [surname]
  25. '' ORDER BY [surname]
  26. '' ORDER BY [surname]
  27. '' ORDER BY [surname]
  28. '' ORDER BY [surname]
  29. '' ORDER BY [surname]
  30. '' OR(  [tblMemberDetails_CountyCode] = "c" ) ORDER BY [surname]
  31. DER BY [surname]
  32.  
Feb 21 '09 #7

P: 15
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( [tblMemberDetails_CountyCode] = "c" ) ORDER BY [surname]
DER BY [surname]
Feb 21 '09 #8

NeoPa
Expert Mod 15k+
P: 31,418
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.
Feb 21 '09 #9

NeoPa
Expert Mod 15k+
P: 31,418
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(?).ControlSource property (array) of the report.
Feb 21 '09 #10

P: 15
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSearch_Click()
  2.  
  3.     ' Update the record source
  4.     'Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter
  5.  
  6.     ' Update the record source
  7.     If BuildFilter = "" Then
  8.         Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter
  9.     Else
  10.         Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew WHERE " & BuildFilter
  11.     End If
  12.  
  13.     'Requery the subform
  14.     Me.sbfrmSearchResults1.Requery
  15.     End Sub
  16. Private Function BuildFilter() As Variant
  17.     Dim varWhere As Variant
  18.     Dim CountyCode As Variant
  19.     Dim varItem As Variant
  20.     Dim intIndex As Integer
  21.     Dim NationalityCode As Variant
  22.     Dim strSQLOrderBy As String
  23.  
  24.     varWhere = Null  ' Main filter
  25.     CountyCode = Null  ' Subfilter used for colors
  26.     NationalityCode = Null
  27.     ' Check for LIKE First Name
  28.  
  29. If Me.txtFirstName > "" Then
  30.         varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName & "*"" AND "
  31.     End If
  32.  
  33.     ' Check for LIKE Last Name
  34.     If Me.txtSurname > "" Then
  35.         varWhere = varWhere & "[surname] LIKE """ & Me.txtSurname & "*"" AND "
  36.     End If
  37.  
  38.     If Me.txtRegNumber > "" Then
  39.         varWhere = varWhere & "[regnumber] like """ & Me.txtRegNumber & """  And "
  40.    End If
  41.  
  42.  ' Check for Colors in multiselect list
  43.     For Each varItem In Me.lstCountyCode.ItemsSelected
  44.         CountyCode = CountyCode & " [tblMemberDetails_CountyCode] = """ & _
  45.                     Me.lstCountyCode.ItemData(varItem) & """ OR "
  46.  
  47.     Next
  48.  
  49.     'Test to see if we have subfilter for colors...
  50.     If IsNull(CountyCode) Then
  51.         ' do nothing
  52.     Else
  53.         ' strip off last "OR" in the filter
  54.         If Right(CountyCode, 4) = " OR " Then
  55.             CountyCode = Left(CountyCode, Len(CountyCode) - 4)
  56.         End If
  57.  
  58.         'Add some parentheses around the subfilter
  59.         varWhere = varWhere & "( " & CountyCode & " )"
  60.     End If
  61.  
  62.      'NationalityCode
  63.  
  64.         ' Check for Nationality in multiselect list
  65.     For Each varItem In Me.lstNationality.ItemsSelected
  66.         NationalityCode = NationalityCode & " [tblmemberdetails.NationalityCode] = """ & _
  67.                     Me.lstNationality.ItemData(varItem) & """ OR "
  68.  
  69.     Next
  70.  
  71.     'Test to see if we have subfilter for colors...
  72.     If IsNull(NationalityCode) Then
  73.         ' do nothing
  74.     Else
  75.         ' strip off last "OR" in the filter
  76.         If Right(NationalityCode, 4) = " OR " Then
  77.             NationalityCode = Left(NationalityCode, Len(NationalityCode) - 4)
  78.         End If
  79.  
  80.         'Add some parentheses around the subfilter
  81.         varWhere = varWhere & "( " & NationalityCode & " )"
  82.     End If
  83.  
  84.      'Check if there is a filter to return...
  85.     If IsNull(varWhere) Then
  86.         varWhere = "''"
  87.     Else
  88.  
  89.         ' strip off last "AND" in the filter
  90.         If Right(varWhere, 5) = " AND " Then
  91.             varWhere = Left(varWhere, Len(varWhere) - 5)
  92.         End If
  93.  
  94.     End If
  95.      BuildFilter = varWhere
  96.      End Function
  97.  
Feb 24 '09 #11

P: 15
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
Feb 24 '09 #12

Post your reply

Sign in to post your reply or Sign up for a free account.