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

    Combo Box (2000)

    I have a combo box on a form. The Combo box contains the field "County". With the County field, I brought in the "State" field. My problem is if there are counties with the same name and different states, if you choose say "Butler" and there is a Butler, OH and a Butler, PA, I get them both when I want to get them separately. I have some code but not sure how to edit it to include the "State" field.

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

    If Not strFilter = "" Then
    ' Omit first " AND "
    strFilter = Mid(strFilter, 6)
    End If
    MakeFilter = strFilter
    End Function

    I think this is where I have to include the "State" field in this code. Any help is appreciated.

    Thanks

  2. #2
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (2000)

    Hi Linda,
    First I don't understand why you are starting your filter string with an AND and then proceding to remove it, but as you have it.........
    You need to reference the [State] column (.Column(X) below) in your combobox remembering the column is zero based; i.e. the first column is referenced 0 (zero), the second column 1, etc., and filter by the [State] value as well.

    If Not IsNull(Me.cboCounty) Then
    strFilter = " AND [County]=" & Chr(34) & Me.cboCounty & Chr(34)
    'added
    strFilter = strFilter & " [State] = " & Chr(34) & Me.cboCounty.Column(X) & Chr(34)
    End If
    <the rest of your code>

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

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

    Re: Combo Box (2000)

    Now I get an error in the following code: It highlights the strFilter = MakeFilter

    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.cboCounty) Then
    strFilter = " AND [County]=" & Chr(34) & Me.cboCounty & Chr(34)
    'added
    strFilter = strFilter & " [Ad St C] = " & Chr(34) & Me.cboCounty.Column(1) & Chr(34)


    End If

    If Not strFilter = "" Then
    ' Omit first " AND "
    strFilter = Mid(strFilter, 6)
    End If
    MakeFilter = strFilter
    End Function

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

    Re: Combo Box (2000)

    Try changing

    strFilter = strFilter & " [Ad St C] = " & Chr(34) & Me.cboCounty.Column(1) & Chr(34)

    to

    strFilter = strFilter & " AND [Ad St C] = " & Chr(34) & Me.cboCounty.Column(1) & Chr(34)

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

    Re: Combo Box (2000)

    That works perfectly - Thank you both very much......

  6. #6
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box (2000)

    whoops <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15> forgot MY "AND" <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>

    Thanks Hans.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

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