Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo box Question (XP)

    I have a table with three fields: ST, State, County. In a form, I have two combo boxes. The first one displays a list of States (Unique property set to yes) from which to choose. When the State is chosen, it will input ST for the field value. I would like to have the second combo box provide a list of counties for the State selected in combo box 1. I can't figure out how to make this work.. any ideas?

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

    Re: Combo box Question (XP)

    You can use the after update event of the first combo box to change the row source of the second combo box:

    Private Sub ComboBox1_AfterUpdate()
    ComboBox2.RowSource = "SELECT County From TableName WHERE ST = " & Me.ComboBox1
    End Sub

    This assumes that ST is numeric. If it is a text field, change the code to

    ... WHERE ST = " & Chr(34) & Me.ComboBox1 & Chr(34)

    Replace TableName, ComboBox1 and ComboBox2 by the appropriate names.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box Question (XP)

    Can anyone explain the difference in this code? The first procedure works fine, the second one doesn't. These are comboboxes. They work well after update but I wanted to have them work before update in the event a user wanted to add an additional county for a state that has been previously entered (and no update is needed). Below is the error msg I receive. I am unable to determine what is wrong.

    Private Sub State_BeforeUpdate()
    County.RowSource = "SELECT County From [states-counties] WHERE ABBR= " & Chr(34) & Me.State & Chr(34)
    County1a.RowSource = "SELECT County From [states-counties] WHERE ABBR= " & Chr(34) & Me.State & Chr(34)
    County1b.RowSource = "SELECT County From [states-counties] WHERE ABBR= " & Chr(34) & Me.State & Chr(34)
    County1c.RowSource = "SELECT County From [states-counties] WHERE ABBR= " & Chr(34) & Me.State & Chr(34)
    County1d.RowSource = "SELECT County From [states-counties] WHERE ABBR= " & Chr(34) & Me.State & Chr(34)
    End Sub

    Private Sub State2_BeforeUpdate()
    County2.RowSource = "SELECT County From [states-counties] WHERE ABBR= " & Chr(34) & Me.State2 & Chr(34)
    County2a.RowSource = "SELECT County From [states-counties] WHERE ABBR= " & Chr(34) & Me.State2 & Chr(34)
    County2B.RowSource = "SELECT County From [states-counties] WHERE ABBR= " & Chr(34) & Me.State2 & Chr(34)
    County2c.RowSource = "SELECT County From [states-counties] WHERE ABBR= " & Chr(34) & Me.State2 & Chr(34)
    County2d.RowSource = "SELECT County From [states-counties] WHERE ABBR= " & Chr(34) & Me.State2 & Chr(34)
    End Sub
    Attached Images Attached Images

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Combo box Question (XP)

    The BeforeUpdate Event is missing the Cancel As Integer argument. It should look like this:

    Private Sub ComboName_BeforeUpdate(Cancel As Integer)
    MsgBox "Message", vbInformation, "BeforeUpdate Event"
    End Sub

    If Sub declaration was changed to read:

    Private Sub ComboName_BeforeUpdate()

    An error similar to the one you depicted resulted. Recommend use Code Builder from form or control's Property Sheet (Event tab) when creating event procedures (see att'd pic). This will ensure proper Sub declaration is created for the specified event procedure.

    HTH
    Attached Images Attached Images

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box Question (XP)

    Thanks for the response. I did make the change you suggested and it did not make any difference except that I did not receive the error message. The combo box still does not display any data. Interesting, though. The first procedure also did not have the "cancel as interger" statement and it worked fine. What am I missing?

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo box Question (XP)

    Which "first procedure"? If you mean the one Hans posted, it is an AfterUpdate procedure, and that does not have a Cancel argument. You have to follow the syntax requirements of the particular event. Not all of them have arguments.

    When you say the combobox does not display any data, do you mean that the list is empty or the combobox is empty?
    Charlotte

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box Question (XP)

    The after update procedure works okay. However, I found that i need a before update procedure as well. For example, if I have previously entered a state and one county then later return to the form in order to add another county for that same state, I would like the datasource for the county combobox to be appropriately set based on the state that is already entered. What I found is that I must update the state field by reselecting the same state (obviously to trigger the after update event). I thought I could avoid that by using the before update event.

    When I said the first procedure, I was referring to the two I had in my last post.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo box Question (XP)

    <hr>When I said the first procedure, I was referring to the two I had in my last post. <hr>
    In that case, it is impossible that the first one worked correctly as posted because there was no Cancel argument.
    Charlotte

  9. #9
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box Question (XP)

    I love this forum. I went back and checked again and.. I was wrong - it doesn't work. In any case, i added the cancel argument and it still doesn't work. At least they are consistent now. Any idea why they don't work?

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo box Question (XP)

    Try the following after setting the RowSource:
    County.Requery
    County1a.Requery
    County1b.Requery
    County1c.Requery
    County1d.Requery

Posting Permissions

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