Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search Form (2007)

    I have a table that includes three fields: Customer, State, Zipcode. I want the create a form with a list box that allows the user sort through the data by one, two, or all three fields. For example the user can select a State and the list box will be updated. Then they can select a Customer, but only customers that are selected and that also are from the selected State. When I try this now, it will update the list box, but it includes all records instead of records from the selected state.
    Thanks,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search Form (2007)

    You can write code in the After Update event of the combo boxes that checks whether each of the combo boxes is populated, and if so adds to a where-condition string. Use this where-condition in the row source of the list box.
    See the code in <post:=301,130>post 301,130</post:>. It's used to filter a form, but you can adapt it to change the row source of a list box instead.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2007)

    Thanks Hans. I want the user to be able to search by keyword also. For example if they type in "Station" in the customer name, I want to display all customer names that have the word station in it. Is this possible with a combo box?
    Thanks,

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search Form (2007)

    You could use a text box instead of a combo box, and a condition with Like:

    For example, in the post I referred to, you could change
    <code>
    ' Field2 is text
    If Not IsNull(Me.cbxField2) Then
    strFilter = strFilter & _
    " And [Field2] = " & Chr(34) & Me.cbxField2 & Chr(34)
    End If
    </code>
    which uses a combo box cbxField2 to
    <code>
    ' Field2 is text
    If Not IsNull(Me.txtField2) Then
    strFilter = strFilter & _
    " And [Field2] Like " & Chr(34) & "*" & Me.txtField2 & "*" & Chr(34)
    End If
    </code>
    where txtField2 is a text box.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2007)

    Here is my code:

    Private Function DoSearch()
    Dim rs As Object
    Dim strFilter As String
    Set dbs = CurrentDb

    ' Assemble filter string

    ' Field1 is string
    If Not IsNull(Me.txtCustName) Then
    strFilter = strFilter & _
    " And [Customer Name] Like " & Chr(34) & "*" & Me.txtCustName & "*" & Chr(34)
    End If

    If strFilter = "" Then
    ' Nothing to search for
    Exit Function
    End If

    ' Get rid of first " And "
    strFilter = Mid(strFilter, 6)

    ' This is the search code
    Set rs = dbs.OpenRecordset("tblJobNumbersLoc")
    rs.FindFirst strFilter
    If Not rs.EOF Then
    Me.Bookmark = rs.Bookmark
    End If

    Set rs = Nothing
    End Function

    It blows up when it gets to the me.bookmark line. How do I get the results to display in my list box?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search Form (2007)

    I assume that you want to expand the code later on to search on the other fields as well. Try this:

    Private Function DoSearch()
    Dim strFilter As String
    Dim strSQL As String

    ' Assemble filter string

    ' Field1 is string
    If Not IsNull(Me.txtCustName) Then
    strFilter = strFilter & " And <!t>[Customer Name]<!/t> Like " & _
    Chr(34) & "*" & Me.txtCustName & "*" & Chr(34)
    End If

    ' Code for other search boxes goes here
    ' ...

    ' You must substitute the correct SQL for the row source of the list box
    strSQL = "SELECT * FROM tblSomething"
    If Not strFilter = "" Then
    strSQL = strSQL & " WHERE " & Mid(strFilter, 6)
    End If

    ' Replace lboList with the name of the list box
    Me.lboList.RowSource = strSQL
    End Function

    See the instructions in the remarks within the code.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form (2007)

    thanks hans.. thats what i need.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •