Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    dropdown (97 sr2)

    I am going nuts. This should be so very simple. I have a dropdown in a form header that has all the cities in the country. I have another dropdown in the same header that contains all the states. I want my user to choose a state and then only the cities in that state will appear in the city dropdown. I can get it to work the first time but when they go to another new record the city dropdown contains the cities from the previous record's state field. I have tried requerying the state field but that did nothing.

    Any insight would be greatly appreciated. : )

    Cathy

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

    Re: dropdown (97 sr2)

    There are two ways to do this:

    One method is to create a query based on the cities table, with a condition (criteria) on the state field that refers to the states combo box on the form, say

    Forms!frmMyForm!cboStates

    Set the Row Source of the cities combo box to the name of the query, and requery the cities combo box in the After Update event of the states comb box.

    Another method is to set the Row Source of the cities combo box in the After Update event of the states combo box, e.g.

    Private Sub cboStates_AfterUpdate()
    If IsNull(Me.cboStates) Then
    Me.cboCities.RowSource = ""
    Else
    Me.cboCities.RowSource = "SELECT City FROM tblCities WHERE State = " & Chr(34) & Me.cboState & Chr(34)
    End If
    End Sub

    You must, of course, substitute the correct names.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dropdown (97 sr2)

    does it make any difference, my tables are linked SQL tables?

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dropdown (97 sr2)

    It works, It was the requery I was requerying the wrong field. Thanks for your help.

    PS It did not make a difference that the tables are linked SQL.

    Cathy

Posting Permissions

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