Combo box Question (XP)

[Don_Sadler]

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?

[HansV]

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.

[Don_Sadler]

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

[MarkD]

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

[Don_Sadler]

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?

[charlotte]

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?

[Don_Sadler]

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.

[charlotte]

<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.

[Don_Sadler]

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?

[patt]

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