Trying to Populate a Combo Box using the value from another Combo Box

[SteveM]

Apologies up front, but I'm a newbie trying to run some VBA code to simply populate a combo box using a value queried from a second combo box. Even though the code doesn't give me an error, when the script finishes, there is nothing populated in the second combo box, even after doing a requery. State is the 1st combo box and cbo_GACC is the 2nd combo box I'm trying to populate.




Private Sub Form_Load()




' Clear the form for the next user




DoCmd.GoToRecord , , acNewRec




End Sub




Private Sub State_Change()
Dim StateName As String
Dim StateAbbrSQL As String
Dim GACC_Name As String


StateName = [State].Value
StateAbbrSQL = "SELECT States.State_Abbr FROM States WHERE (((States.State_Name) = 'Arizona'))"
cbo_GACC.RowSourceType = "Table/Query"
cbo_GACC.RowSource = StateAbbrSQL
cbo_GACC.Requery
End Sub






Any suggestions would be greatly appreciated.


Thanks in advance.

[Micron]

You're setting the row source for a combo. You're not setting the combo value to anything. Maybe explain what you want to have happen from the beginning. I'm not seeing what that code has to do with the 1st combo. Seems like you need to set the 2nd combo value to a list item from the first, but that wouldn't make sense either.

[moke123]

Seems to me you want to select a state in the first combo and autofill the second to the abbreviation for the state.

You could just set the rowsource of both to "Select StateID, StateName, State_Abbr FROM tblStates "
then set your column width on cbo_States to 0;1;0 and cbo_GACC to 0;0;1

then in the after update event of cbo_States set

Me.cbo_GACC = Me.cbo_States

you should use the afterupdate event not the change event.

[jojowhite]

You may also try this:

Private Sub Form_Load()
	' Clear the form for the next user
	DoCmd.GoToRecord , , acNewRec
End Sub








Private Sub State_Change()
	Dim StateName As String
	Dim StateAbbrSQL As String
	Dim GACC_Name As String




	StateName = [State].Value
	StateAbbrSQL = "SELECT States.State_Abbr FROM States WHERE (((States.State_Name) = 'Arizona'))"
	With Me.cbo_GACC
    		.RowSourceType = "Table/Query"
    		.RowSource = StateAbbrSQL
    		.Requery
    		' Set the Value of the combo to the first Item on the List
    		.Value = .ItemData(0)
	End With
End Sub

[June7]

You want to offer user two ways to select a state? One combobox for full state name and another combobox for input of just state abbreviation?

If user selects state by input to cbo_GACC, do you want to then set value of State combobox?

Are these UNBOUND comboboxes used to input search criteria, not save value to record?

[moke123]

JoJo,
I'm curious what happens when a user types a single character like x in the combo using the change event.

[jojowhite]

did you see the OP's code?
the first combobox is Not doing anything on the Filtering.
whether he type X, Y, Z...
still the combo is being filtered on "State_Name='Arizona'".

[moke123]

did you see the OP's code?
the first combobox is Not doing anything on the Filtering.
whether he type X, Y, Z...
still the combo is being filtered on "State_Name='Arizona'".

I think that was just OP trying to get it to do something.
He wants to filter on the first combo so I'd assume he meant

StateAbbrSQL = "SELECT States.State_Abbr FROM States WHERE (((States.State_Name) = """ & me.cbo_State & """))"

Personally my state table looks like:



I store the StateID so I can easily display any of the 3 fields.
It also simplifies using Cascading combo boxes.

Additionally i wouldn't use the code in the OP's form load event as that precludes you from re-using the same form for data editing.
I would use the datamode argument for the open form method.

Not sure why the OP is using a combo as it's presumably only going to return one record.

[SteveM]

I think I should have provided more background information regarding my post.

Two of the tables I have in my database are States and GACC (Geographic Area Coordination Center). States contains the state abbreviation and state name. GACC contains GACC Code, GACC Name, and states that belong to the GACC. What I'm looking to do is filter or populate my GACC combo box with the correct GACC name based on the state name selected in the state combo box. For example, if 'Arizona' is selected as the state, AZ would be used to query the GACC table to select 'Southwest' which would be the only value shown in the GACC combo box.

I'm trying to use the Select statement listed below to query for the state abbreviation, but I can't get past a syntax error that VBA keeps telling me I have. I've tried many variations of this Select statement, but I can't figure out what's wrong with it. Very aggravating.

"SELECT State_Abbr FROM States WHERE State_Name = '" & [State].Value & "'"


Any suggestions??

[June7]

Did you read post #5?

If you want to select GACC name then why are you referencing States table in GACC combobox RowSource - did you show the wrong SQL? If state abbreviation is in first column (hidden) of State combobox, GACC RowSource would be like:

SELECT GACC_Name FROM GACC WHERE State_Abb = '" & Me.State & "'";

You haven't provided exact data structure, so I am just winging it on object names. You could provide your db for analysis. Follow instructions at bottom of my post.

Why even have a GACC combobox? State combobox RowSource can be a query that joins the tables and has GACC name as a column. Then expression in textbox can reference that column to display "Southwest".