Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search Function (2000)

    This is a follow on from post 300379, I have a combo box to search for records in a form, this works fine when searching by one specific field as one would expect. How can I search by a combination of fields?

    Thanks Darren.

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

    Re: Search Function (2000)

    Say that you want to search on a combination of two fields Field1 and Field2. You could create two combo boxes, one for each field. Do you want to allow the user to leave a combo box empty, or do you want to search only if an item has been selected in both combo boxes?

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Function (2000)

    Thanks Hans, yes I would like the search to be either on both fields selected or just one and the other blank.

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

    Re: Search Function (2000)

    Here is example code for three combo boxes; it can easily be adapted for two combo boxes or for more than three combo boxes. The names of the combo boxes are cbxField1, cbxField2 and cbxField3, and they let the user select values for fields named Field1, Field2 and Field3, respectively. For illustration purposes, the first is numeric, the second text and the third a date field. Of course, you must substitute the appropriate names, and adjust for field types.

    The following function must be called in the After Update event of each of the combo boxes.

    Private Function DoSearch()
    Dim rs As Object
    Dim strFilter As String

    ' Assemble filter string

    ' Field1 is numeric
    If Not IsNull(Me.cbxField1) Then
    strFilter = strFilter & _
    " And [Field1] = " & Me.cbxField1
    End If

    ' Field2 is text
    If Not IsNull(Me.cbxField2) Then
    strFilter = strFilter & _
    " And [Field2] = " & Chr(34) & Me.cbxField2 & Chr(34)
    End If

    ' Field3 is a date
    If Not IsNull(Me.cbxField3) Then
    strFilter = strFilter & _
    " And [Field3] = #" & Format(Me.cbxField3, "mm/dd/yy") & "#"
    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 = Me.Recordset.Clone
    rs.FindFirst strFilter
    If Not rs.EOF Then
    Me.Bookmark = rs.Bookmark
    End If

    Set rs = Nothing
    End Function

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Function (2000)

    I am trying to set up a form, frmFolio, which will be used as the search criteria for qryFolio. The form will have 2 combo boxes: cbxCombinedName and cbxRoomNumber. I want to be able to run the query based on just the CombinedName or just the Room Number or based on both CombinedName and RoomNumber.

    At present my form has just the CombinedName combo box and a Command Button, Run Select Query. The On Click Event is as follows:

    Private Sub Run_Select_Query_Click()
    On Error GoTo Err_Run_Select_Query_Click

    Dim stDocName As String

    stDocName = "qryFolio"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    Exit_Run_Select_Query_Click:
    Exit Sub

    Err_Run_Select_Query_Click:
    MsgBox Err.Description
    Resume Exit_Run_Select_Query_Click

    End Sub

    I have tried to adapt the code in post 301130 but thus far have been unsuccessful. The After Update event in cbxCombinedName is as follows:

    Private Sub cbxCombinedName_AfterUpdate()
    Private Function DoSearch()
    Dim rs As Object
    Dim strFilter As String

    ' Assemble filter string

    ' Field2 is text
    If Not IsNull(Me.cbxCombinedName) Then
    strFilter = strFilter & _
    " And [Field2] = " & Chr(34) & Me.cbxCombinedName & 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 = Me.Recordset.Clone
    rs.FindFirst strFilter
    If Not rs.EOF Then
    Me.Bookmark = rs.Bookmark
    End If

    Set rs = Nothing
    End Function

    End Sub


    The criteria in the criteria field of qryFolio is Forms![frmFolio]![cbxCombinedName]

    When the frmfolio is opened and a name is selected from the combo box, I get thefollowing error message:

    Compile Error
    Expected End Sub

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

    Re: Search Function (2000)

    Hello Tom,

    In the first place, you can't nest a function definition within a procedure definition:

    Private Sub cbxCombinedName_AfterUpdate()
    Private Function DoSearch()
    ...
    End Function
    End Sub

    The idea was to define a function

    Private Function DoSearch()
    ...
    End Function

    and call it from the event procedures:

    Private Sub cbxCombinedName_AfterUpdate()
    DoSearch
    End Sub

    In the second place, you're trying to use the code from <post#=301130>post 301130</post#> for a purpose it wasn't intended for. The purpose of the code is to filter the form containing the controls; it can't be used to filter a query. In my databases, I never let the user work with a table or query directly, all interaction is through forms. I would suggest that you do the following (test on a copy of your database!):

    - Remove the criteria from the query.
    - Create a continuous form based on the query.
    - Put the combo boxes in the form header or footer.
    - Use the code to filter the form.

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Function (2000)

    Hans,

    I have a frmFolio which has a text box, txtRoomNumber and a combo box, cbxCombinedName. The form is the source for the selection criteria on qryFolio. The query works fine as long as both values are entered. What I am trying to do is to get it to work when only one value is entered. The query ultimately will the source of a report.

    Any suggestions would be welcomed.

    Tom

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

    Re: Search Function (2000)

    Try the following on a copy of the query:

    - In the first empty column in the query design grid, enter [Forms]![frmFolio]![txtRoomNumber]
    - Clear the Show check box for this column.
    - In the Criteria row for this column, enter [RoomNumber] Or Is Null (I assumed that the text box is meant to filter a field named RoomNumber.)

    - In the next empty column in the query design grid, enter [Forms]![frmFolio]![cbxCombinedName]
    - Clear the Show check box for this column.
    - In the Criteria row for this column, enter [CombinedName] Or Is Null (I assumed that the text box is meant to filter a field named CombinedName.)

    - Remove the original criteria in the columns for RoomNumber and CombinedName.

  9. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Function (2000)

    Many thanks. Works like a charm.

    I modified the Command Button on frmFolio which opens qryFolio and then opens rptFolio in preview to give the operator a chance to review the report before printing.

    Thanks again.

Posting Permissions

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