Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter on Multiple Lookup Combo Box (XP)

    Hi,

    I created a lookup combo box, using the combo box wizard on a form. However, when I use it, it goes to the first record in the form that contains the selected value. What I need it to do is actually filter the records based on the selection made from the combo box. I thought this combo box lookup did that in prior versions, though I could be mistaken.

    I want a user to be able to, by using two combo box lookups, get to a specific set of filtered records. Specifically, use one combo box to look up a name and get a filtered list, displayed in the form, based on the selection made in the combo box, then lookup a date in another combo box and find all the records from the current filtered set that match that selection. Kind of like using autofilter in excel multiple times. You can keep filtering different fields to get to specific records.

    They can then click a remove filter/sort button to diplay all of the records.

    I appreciate any help...

    Thanks,
    Steve

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Filter on Multiple Lookup Combo Box (XP)

    Hi Steve,
    Unfortunately the combo box generated by the wizard doesn't use filters - it does a clone recordset operation, and under some circumstances will misbehave. Take a look at the code generated for the combo box on the AfterUpdate event and you will see what is going on. You can easily enough change that and apply a filter instead - we frequently use that when we want to display just one record, but you can set it so you see a set of records as well. If you need assistance in creating the code to filter the results shown in the form, let us know and we will do our best to help.
    Wendell

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

    Re: Filter on Multiple Lookup Combo Box (XP)

    In my memory, the Combo Box Wizard offered an option to let the combo box search, not filter from Access 95 to 2002. It is possible, however, to write your own code to filter instead of search. The following procedure could be called from the After Update event of combo boxes cbxNameField and cbxDateField:

    Private Sub Handler2()
    Dim strFilter As String
    Dim strName As String
    Dim strDate As String

    On Error GoTo Err_Handler

    ' Initialize (not really necessary)
    strFilter = ""

    ' Handle name combo box
    If Not IsNull(Me.cbxNameField) Then
    strName = Chr(34) & Me.cbxNameField & Chr(34)
    strFilter = strFilter & " AND [NameField]=" & strName
    End If

    ' Handle date combo box
    If Not IsNull(Me.cbxDateField) Then
    strDate = "#" & Format(Me.cbxDateField, "mm/dd/yyyy") & "#"
    strFilter = strFilter & " AND [DateField]=" & strDate
    End If

    ' Filter the form - or not
    If strFilter <> "" Then
    Filter = Mid$(strFilter, 6)
    FilterOn = True
    Else
    FilterOn = False
    End If

    Exit_Handler:
    Exit Sub

    Err_Handler:
    MsgBox Err.Description, vbExclamation
    Resume Exit_Handler
    End Sub

    Substitute the names of the fields and combo boxes you are using. The command button to remove the filter would set Me.FilterOn = False, and clear the combo boxes by setting them to Null.

  4. #4
    Star Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter on Multiple Lookup Combo Box (XP)

    Thanks so much Hans. Works like a charm. I am not familiar with writing code so I would have had no idea. This was very helpful.

    Best,
    Steve

  5. #5
    Star Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter on Multiple Lookup Combo Box (XP)

    Thank you for your assistance Wendell.

    Steve

Posting Permissions

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