BEEN TRYING BUT JUST DON'T SEEM TO GET IT

[Vic]

First thanks to everyone for your input. I'm not a programmer so this has
been rather difficult.

Here's my problem I'm still tangling with.

I have a demographics form. On this form there are two fields that I'm
primarily trying to deal with.

1. State field. This pulls up all the appropriate state abreviations from
a table called tbl_States.
2. County field. This pulls up all the appropriate counties from a table
called tbl_counties.

For now we have indicated that state code in the county description when the
user goes to enter county information. i.e. OH-Sandusky, PA-Menter as an
example.

What we're trying to do is restrict the county list to only the state that
is appropriate. So let's say the state is OH then when a user goes to either
enter or edit the record information for the county they would only get a
list of all the counties in OH.

The fields in the county table are CntyCode which is the data that we want
to store, CntyDesc which is the name of the county that we want the user to
see and CntyState which is basically an assigned state to each county.

The field in the form is setup as bound column 1 and number of columns is 3.
The first column is set to 0";1.8".5"

I have tried this code but I just can't seem to get things to work right for
me. So I have no idea what I'm screwing up.

PrspDemoCounty.RowSource = "Select CntyCode, CntyDesc, CntyState from
tbl_Counties Where PrspDemoState = " '" & Me!CntyState & "';"

Keeping it short I just want the user to see the county applicable for the
stated recorded whether it's a new entry or they are editing the record. I
have tried placing this code in the after update event in the state field and
also in the after update event in the county field. I just can't get it
together.

Thanks all for the help.

 

[Arvin Meyer]

In the afterupdate event of the State control, requery the combo box control
of counties. The rowsource of the counties combo should look something like:

Select * From tblCounties Where State = Forms!MyFormName!cboMyStateComboName

The AfterUpdate event code should look something like:

Sub cboState_AfterUpdate ()
Me.Requery
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer]

In your suggestion your referencing two after update events.

Only 1, in the State Combo box.

Are these both
to be in the state combo box field? Somewhat lost.

In addition would this
still work if the user updated the county information without inputting the
state information i.e. the state was originally entered but the county

wasn't.

Not exactly, if you also requeried the county combo in the form's Current
event, the county combo would either be empty (if there were no state) or
filled with the correct counties if the state combo had a value.

Please note also that you cannot use this in continuous forms as the code
affects all the combos, not just the record you are on.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer]

Correct Glenn. Thanks.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access