Combo Box Dependent on previous


K

Kat5813

I am fairly new at creating forms etc and I am trying to have a combo box
that populates based on the results of the previous combo box in tab order.
Fox example, if I choose the state of MO in the first comb box, I want the
second combo box to populate a list of Missouri counties. I can get this to
work using a query for the row source in the second box that limits the
county to MO counties but when i go to add another new record using the form,
the counties will show up MO counties again even if I choose AR for this new
record.
 
Ad

Advertisements

K

Klatuu

This technique is called Cascading Combos.
The County combo needs to have a row source query that is filtered on the
value of the State combo. For example:

SELECT [CountyName] FROM SomeTable WHERE [State] = Me.cboState;

Then in the After Update event of the state combo, requery the county combo.
 
K

Kat5813

When I try this method, I get the message

Run-time error'438'

Object doesn't support this property or method.

The code I have in the after update event is:private Sub StateNM_AfterUpdate()
Me!County.Requery
End Sub


And the code I have in the row source of the County is:
SELECT [County] FROM Query2 WHERE [State]=Me.StateNM;

Any thoughts where I might be wrong?


Klatuu said:
This technique is called Cascading Combos.
The County combo needs to have a row source query that is filtered on the
value of the State combo. For example:

SELECT [CountyName] FROM SomeTable WHERE [State] = Me.cboState;

Then in the After Update event of the state combo, requery the county combo.

Kat5813 said:
I am fairly new at creating forms etc and I am trying to have a combo box
that populates based on the results of the previous combo box in tab
order.
Fox example, if I choose the state of MO in the first comb box, I want
the
second combo box to populate a list of Missouri counties. I can get this
to
work using a query for the row source in the second box that limits the
county to MO counties but when i go to add another new record using the
form,
the counties will show up MO counties again even if I choose AR for this
new
record.
 
K

Klatuu

County is the correct name for the combo?

Kat5813 said:
When I try this method, I get the message

Run-time error'438'

Object doesn't support this property or method.

The code I have in the after update event is:private Sub
StateNM_AfterUpdate()
Me!County.Requery
End Sub


And the code I have in the row source of the County is:
SELECT [County] FROM Query2 WHERE [State]=Me.StateNM;

Any thoughts where I might be wrong?


Klatuu said:
This technique is called Cascading Combos.
The County combo needs to have a row source query that is filtered on the
value of the State combo. For example:

SELECT [CountyName] FROM SomeTable WHERE [State] = Me.cboState;

Then in the After Update event of the state combo, requery the county
combo.

Kat5813 said:
I am fairly new at creating forms etc and I am trying to have a combo
box
that populates based on the results of the previous combo box in tab
order.
Fox example, if I choose the state of MO in the first comb box, I
want
the
second combo box to populate a list of Missouri counties. I can get
this
to
work using a query for the row source in the second box that limits the
county to MO counties but when i go to add another new record using the
form,
the counties will show up MO counties again even if I choose AR for
this
new
record.
 
K

Kat5813

You were right, it wasn't named right. So, I changed the name of the combo
to be County but now when I go through the form, when I get to the county
box I get a pop up box asking to Enter the parameter value Me.StateNM. I
have verified that this comb is named correctly.

Klatuu said:
County is the correct name for the combo?

Kat5813 said:
When I try this method, I get the message

Run-time error'438'

Object doesn't support this property or method.

The code I have in the after update event is:private Sub
StateNM_AfterUpdate()
Me!County.Requery
End Sub


And the code I have in the row source of the County is:
SELECT [County] FROM Query2 WHERE [State]=Me.StateNM;

Any thoughts where I might be wrong?


Klatuu said:
This technique is called Cascading Combos.
The County combo needs to have a row source query that is filtered on the
value of the State combo. For example:

SELECT [CountyName] FROM SomeTable WHERE [State] = Me.cboState;

Then in the After Update event of the state combo, requery the county
combo.

I am fairly new at creating forms etc and I am trying to have a combo
box
that populates based on the results of the previous combo box in tab
order.
Fox example, if I choose the state of MO in the first comb box, I
want
the
second combo box to populate a list of Missouri counties. I can get
this
to
work using a query for the row source in the second box that limits the
county to MO counties but when i go to add another new record using the
form,
the counties will show up MO counties again even if I choose AR for
this
new
record.
 
Ad

Advertisements

J

John W. Vinson

You were right, it wasn't named right. So, I changed the name of the combo
to be County but now when I go through the form, when I get to the county
box I get a pop up box asking to Enter the parameter value Me.StateNM. I
have verified that this comb is named correctly.

VBA code recognizes the Me! syntax, but a Query does not. Change

SELECT [County] FROM Query2 WHERE [State]=Me.StateNM;

to

SELECT [County] FROM Query2 WHERE [State]=Forms!YourFormName!StateNM;
 
Ad

Advertisements

K

Kat5813

That worked!!!! Thank you so much!!

John W. Vinson said:
You were right, it wasn't named right. So, I changed the name of the combo
to be County but now when I go through the form, when I get to the county
box I get a pop up box asking to Enter the parameter value Me.StateNM. I
have verified that this comb is named correctly.

VBA code recognizes the Me! syntax, but a Query does not. Change

SELECT [County] FROM Query2 WHERE [State]=Me.StateNM;

to

SELECT [County] FROM Query2 WHERE [State]=Forms!YourFormName!StateNM;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top