Search using value received from listbox

[bitech]

I have a listbox on my Search page with a list of all of the zip codes of the businesses in the system. I would like for the user to be able to select a zip code or many zip codes and hit SUBMIT and the system should return the companies that match the selection(s) from the zip codes in the list box.

 

[TudorSmith]

I can do the single search without thinking too hard!!!

Let's assume your list box is called lstMyList, and your searchable details are held in a table called tblSearchableStuff

Your list box holds details like this:
54212
65985
01236
25456

Youruser highlights 65985 and clicks the submit button:

sub cmdSubmit_Click()
  Dim db as Database
  Dim rst as DAO.Recordset
  Dim strMsg as String

  Set db = CurrentDB()
  Set rst = db.OpenRecordSet("SELECT * FROM tblSearchableStuff WHERE tblSearchableStuff.ZIPCODE = '" & Me!lstMyList & "'")

' Now I have interrogated the table, and the record set is 
' only that which has a unique ZIP code identical to the 
' selected one in the list box. You can now do what
' ever you liek wiht the data. Here I just display
' the results in a MsgBox

  strMsg = " Customer: " & rst!field1 & Chr(13) & Chr(13)
  strMsg = strMsg & "Address: " & rst!field2 & Chr(13) & Chr(13)
  strMsg = strMsg & "Zip Code: " &rst!field3 & Chr(13) & Chr(13)
  MsgBox strMsg, 64, "The results of your selected item"
  rst.Close
End Sub

This is elementary, and can of course be built upon. To run through the list box, capture the MyList.Index number, when you jump to the first record, enter a loop, and then capture the record details for the first index, then increment the index by one, which will cause the list to jump to the next item, then run the loop again and caprture the next record details etc.

Hope this gives you some starting point!

birklea

 

[JeffH]

Try this...this is a function that will build a where clause based on selections of a list box. You can use this where clause as part of a SQL statement, or you can use it as a filter when opening reports/forms.

Just plug it in to a module, and call it passing in the paramaters. If you have any questions over its use, please let me know.

jeff

'****BEGIN CODE****PASTE INTO MODULE

'***********************************************************************************
'*** Build Where Clause
'***
'*** Description
'*** Builds the where clase for an SQL statement based on items selected in a list
'*** box (with multiselect set to true) which uses an OR operator
'***
'*** Parameters
'*** pstrFormName - the form name where the list box calls home
'*** pstrCurrentListBox - The list box name that contains the items for the SQL statement
'*** pstrFieldName - The field name used in the Query that the items are compared to
'*** Returns
'*** Returns the where clause.
'*** Example Call:
'*** strSQL = "Select * from tblMain WHERE " & BuildWhereClause (frmPeople, lstItems, "IDNumber&quot
'*** Returns
'*** "IDNumber = 1 OR IDNumber = 2 OR IDnumber = 3;"
'*** value of strSQL:
'*** "Select * from tblMain WHERE IDNumber = 1 OR IDNumber = 2 OR IDnumber = 3;"
'*** Procedures called
'***
'*** Revisions
'*** Created 4-13-1999 - Jeff Hammond jthammond@onebox.com
'***********************************************************************************
Public Function BuildWhereClause(ByVal pstrFormName As String, _
ByVal pstrCurrentListBox As String, _
ByVal pstrFieldName As String) As String


Const MaxSelection As Integer = 15 'Maximum number of items
On Error GoTo ErrorHandler

Dim varItemSelected As Variant
Dim astrItemsToRemove(MaxSelection) As String
Dim intItemCounter As Integer 'Used to track number in array
Dim intLastArrayItem As Integer 'Last item in array

'Set initial variables
intItemCounter = -1
For Each varItemSelected In Forms(pstrFormName).Controls(pstrCurrentListBox).ItemsSelected
If intItemCounter < MaxSelection - 1 Then
intItemCounter = intItemCounter + 1
astrItemsToRemove(intItemCounter) = Forms(pstrFormName).Controls(pstrCurrentListBox).ItemData(varItemSelected)
Else
MsgBox &quot;Only &quot; & MaxSelection & &quot; items can be selected. Only the first &quot; & MaxSelection & &quot; items selected will be used.&quot;, vbInformation, &quot;Error...&quot;
Exit For
End If
Next varItemSelected
'Set the last item in the array
intLastArrayItem = intItemCounter
'build SQL statement where clase for all but last item in array (if only 1 item, is skipped)
For intItemCounter = 0 To intLastArrayItem - 1
BuildWhereClause = BuildWhereClause & pstrFieldName & &quot; = &quot; & astrItemsToRemove(intItemCounter) & &quot; OR &quot;
Next intItemCounter
'Add last itme in array to SQL statement
'BuildWhereClause = BuildWhereClause & pstrFieldName & &quot; = &quot; & astrItemsToRemove(intLastArrayItem) & &quot;;&quot;
BuildWhereClause = BuildWhereClause & pstrFieldName & &quot; = &quot; & astrItemsToRemove(intLastArrayItem)
Exit Function
ErrorHandler:
Select Case Err.Number
Case 9 'sub script out of range
Resume Next
Case Else
MsgBox Err.Number & Chr(10) & Err.Description
End Select
End Function