COMPILE ERROR

[Guest]

The following code give me a compile error and I can't figure out why

Private Sub PrspDemoState_AfterUpdate()

PrspDemoCounty.RowSource = "SELECT CntyDesc From tbl_Counties" where
CntyState = "'" & PrspDemoState.Value & "'"

End Sub


What I'm trying to do is the following:

I want to restrict the list of counties to only those counties that the user
has entered the appropriate state abreviation for.

PrspDemoCounty is the name of the field on my form that is a list box for
the counties names.
CntyDesc is the field that I want the user to see. This information is
located in a table called tbl_Counties.
tbl_Counties. This table contains all the county names for each state.
CntyState located in the table tbl_Counties references what county name is
assigned to that particular state.
PrspDemoState is the field on the form that the user enter in the state
information. The state code is coming from another table called tbl_States.

The reason I need to have the counties separated out in a different table is
because I have representatives attached to specific counties in each state.
However, some states have the same county name.

Can anyone help with this. Thx

 

[Jim Allensworth]

See in-line ...

The following code give me a compile error and I can't figure out why

Private Sub PrspDemoState_AfterUpdate()

PrspDemoCounty.RowSource = "SELECT CntyDesc From tbl_Counties" where
CntyState = "'" & PrspDemoState.Value & "'"

End Sub

Try this ...

Private Sub PrspDemoState_AfterUpdate()
PrspDemoCounty.RowSource = "SELECT CntyDesc From tbl_Counties " _
& "WHERE CntyState = '" & PrspDemoState & "'"
End Sub

There a double quotes in strange places and no line continuation
characters (maybe that is news reader wrap).

- Jim

 

[Guest]

That worked. Thanks so much. Have one question though. The syntex works
great if your coming out of the state field. It restricts the list of the
counties to the appropriate state etc.

However I noticed if your editing the county information in the form without
having entered the state it provides a list saying no no no no and so on.

How could I fix this particular part.

Thanks so much

 

[Jim Allensworth]

See in-line ...

That worked. Thanks so much. Have one question though. The syntex works
great if your coming out of the state field. It restricts the list of the
counties to the appropriate state etc.

However I noticed if your editing the county information in the form without
having entered the state it provides a list saying no no no no and so on.

If I understand the problem correctly, you ought not allow the county
to be enabled until the state is selected.

Have it not enabled by default and set it to enabled in the same
procedure you supply the new Row Source.

Me.PrspDemoCounty.Enabled = True

 

[Jim Allensworth]

You would need to use the form's On Current event to correct that.
Essentially, the same code that you used previously.

- Jim