Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering Data (2003)

    I have two combo boxes on a form. I am trying to adapt some code I got from this forum but it isn't working. The first combo box has County and State. Once you pick the County and State the next combo box contains only the clients from the chosen County and State but the client combo box is not working: The following is the code behind the Combo boxes:

    Private Sub cboClient_AfterUpdate()
    FilterMe
    End Sub

    Private Sub cboCty_AfterUpdate()
    FilterMe
    Me.cboClient.Requery
    End Sub

    Private Sub FilterMe()
    Dim strFilter As String
    strFilter = MakeFilter
    Me.Filter = strFilter
    Me.FilterOn = Not (strFilter = "")
    End Sub

    Function MakeFilter() As String
    If Not IsNull(Me.cboCty) Then
    strFilter = " AND [County]=" & Chr(34) & Me.cboCty & Chr(34)

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

    Re: Filtering Data (2003)

    I would give each county a unique ID (for example an AutoNumber field). Use the ID as hidden first column in the combo box. That way, there can be no confusion: Adams county in Illinois would have a different ID from Adams county in Indiana.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Data (2003)

    Do you mean in the qryCounty, put an autonumber field? How do you put an autonumber field in a query if it isn't in the table? Sorry for drawing a blank.

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

    Re: Filtering Data (2003)

    You can't put an autonumber in a query. I meant in the table.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Data (2003)

    That doesn't work because now the county dropdown list shows every record because of the autonumber field. For instance Allegheny County, Pa appears as many times as it appears in the table - which is many. I thought the following code would take care of the filtering for the County and particular State:

    Function MakeFilter() As String
    If Not IsNull(Me.cboCty) Then
    strFilter = " AND [County]=" & Chr(34) & Me.cboCty & Chr(34)

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

    Re: Filtering Data (2003)

    You must create a separate table with all distinct state/county combinations, with an AutoNumber field as primary key. This tab;e (or a query based on it) should be the row source of the county combo box.
    Your main data table should contain a number (long integer) field to specify the country.

Posting Permissions

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