COMBO BOX RESTRICTION

[Guest]

I've posted this before and was told there would be some information on it
but I can't seem to find anything that really leads me to an answer. If
someone could help I would really appreciate it.

I have a form that contains demographics information. On this is the
address, city, state, zip and county.

When I user enter in the state and then goes to the county field I want them
to only see those counties for that particular state.

The state codes are all in a table and the county codes are in a different
table. The county table lists the state codes that the particular county
applies to.

I've tried some of the suggestions that I found back from July and May but I
can't seem to understand and get it to work correctly.

Could someone please help.

 

[Gerald Stanley]

In the AfterUpdate eventhandler for the State comboBox, you
need to put code along the lines of

cmbCounty.RowSource = "SELECT countyName FROM tblCounty
WHERE stateCode = '" & cmbState.Value & "'"

You will need to change the combo box, table and column
names to suit your application.

Hope This Helps
Gerald Stanley MCSD

 

[fredg]

I've posted this before and was told there would be some information on it
but I can't seem to find anything that really leads me to an answer. If
someone could help I would really appreciate it.

I have a form that contains demographics information. On this is the
address, city, state, zip and county.

When I user enter in the state and then goes to the county field I want them
to only see those counties for that particular state.

The state codes are all in a table and the county codes are in a different
table. The county table lists the state codes that the particular county
applies to.

I've tried some of the suggestions that I found back from July and May but I
can't seem to understand and get it to work correctly.

Could someone please help.

Use a combo box for the [County] control bound to the County Field.
Leave the Rowsource property of the [County] control blank.

What is the underlying datatype of the [State] field?
Is it Text, or Number?

If [State] is text datatype, code the [State] AfterUpdate event:

[County].Rowsource = "Select [County] from YourTable Where [State] =
'" & Me![State] & "';"

If [State] is a Number (Bound column of a Combo Box?), then:

[County].Rowsource = "Select [County] from YourTable Where [State] = "
& Me![State] & ";"

 

[fredg]

Sorry to be a pain but having difficulty. Here's what I've done.

Private Sub PrspDemoState_AfterUpdate()
[PrspDemoCounty].RowSource = "Select [CntyDesc] from tbl_counties Where
[CntyState] = " & Me![PrspDemoState] & "';"

End Sub

PrspDemoCounty is the field name on the form.
CntyDesc is all the counties that I want the user to see for that state.
tbl_counties is the table that houses all the county names and the
associated state
CntyState is the state for which that county is assigned to.
PrspDemoState is the field name on the form that holds the state
information. This is a text value.

I've tried the code and I can't see to figure out what I'm doing wrong. I
receive a Syntex error in string query expression message and not counties
are listed.

Could you please let me know how I'm screwing this up.

Much appreciated.

I've posted this before and was told there would be some information on it
but I can't seem to find anything that really leads me to an answer. If
someone could help I would really appreciate it.

I have a form that contains demographics information. On this is the
address, city, state, zip and county.

When I user enter in the state and then goes to the county field I want them
to only see those counties for that particular state.

The state codes are all in a table and the county codes are in a different
table. The county table lists the state codes that the particular county
applies to.

I've tried some of the suggestions that I found back from July and May but I
can't seem to understand and get it to work correctly.

Could someone please help.

Use a combo box for the [County] control bound to the County Field.
Leave the Rowsource property of the [County] control blank.

What is the underlying datatype of the [State] field?
Is it Text, or Number?

If [State] is text datatype, code the [State] AfterUpdate event:

[County].Rowsource = "Select [County] from YourTable Where [State] =
'" & Me![State] & "';"

If [State] is a Number (Bound column of a Combo Box?), then:

[County].Rowsource = "Select [County] from YourTable Where [State] = "
& Me![State] & ";"

As written, you've left out a single quote (') in the Where clause.
I'll add a space between the quotes for clarity (under the V).
When you re-write it, remove the added space.
V
Where [CntyState] = ' " & Me![PrspDemoState] & "';"

The above assumes the [CntState] field is a TEXT Datatype.
Look at my example under the line "If [State] is text datatype, ...."
Also make sure the [PrspDemoCounty] Row Source Type property is set to
Table/Query.

 

[GlennS]

I'd like to expand on this a bit...

I need the RowSource to return unique values. Envision a
solution where I'm working with cities and states. I'm
trying to populate the state from the city chosen. If I
have 5 records that have 'St. Louis' for the city,
then 'MO' shows up 5 times in the combo box. I only want
it once. Doable?

TIA

-----Original Message-----

Sorry to be a pain but having difficulty. Here's what I've done.

Private Sub PrspDemoState_AfterUpdate()
[PrspDemoCounty].RowSource = "Select [CntyDesc] from tbl_counties Where
[CntyState] = " & Me![PrspDemoState] & "';"

End Sub

PrspDemoCounty is the field name on the form.
CntyDesc is all the counties that I want the user to see for that state.
tbl_counties is the table that houses all the county names and the
associated state
CntyState is the state for which that county is assigned to.
PrspDemoState is the field name on the form that holds the state
information. This is a text value.

I've tried the code and I can't see to figure out what I'm doing wrong. I
receive a Syntex error in string query expression message and not counties
are listed.

Could you please let me know how I'm screwing this up.

Much appreciated.

On Fri, 6 Aug 2004 13:39:03 -0700, Vic wrote:

I've posted this before and was told there would be some information on it
but I can't seem to find anything that really leads me to an answer. If
someone could help I would really appreciate it.

I have a form that contains demographics information. On this is the
address, city, state, zip and county.

When I user enter in the state and then goes to the county field I want them
to only see those counties for that particular state.

The state codes are all in a table and the county codes are in a different
table. The county table lists the state codes that the particular county
applies to.

I've tried some of the suggestions that I found back from July and May but I
can't seem to understand and get it to work correctly.

Could someone please help.

Use a combo box for the [County] control bound to the County Field.
Leave the Rowsource property of the [County] control blank.

What is the underlying datatype of the [State] field?
Is it Text, or Number?

If [State] is text datatype, code the [State] AfterUpdate event:

[County].Rowsource = "Select [County] from YourTable Where [State] =
'" & Me![State] & "';"

If [State] is a Number (Bound column of a Combo Box?), then:

[County].Rowsource = "Select [County] from YourTable Where [State] = "
& Me![State] & ";"

As written, you've left out a single quote (') in the Where clause.
I'll add a space between the quotes for clarity (under the V).
When you re-write it, remove the added space.
V
Where [CntyState] = ' " & Me![PrspDemoState] & "';"

The above assumes the [CntState] field is a TEXT Datatype.
Look at my example under the line "If [State] is text datatype, ...."
Also make sure the [PrspDemoCounty] Row Source Type property is set to
Table/Query.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.

 

[GlennS]

GRRRR....Disregard my stupidity. Somehow the
word 'DISTINCT' escaped my vocabulary.

-----Original Message-----

Sorry to be a pain but having difficulty. Here's what I've done.

Private Sub PrspDemoState_AfterUpdate()
[PrspDemoCounty].RowSource = "Select [CntyDesc] from tbl_counties Where
[CntyState] = " & Me![PrspDemoState] & "';"

End Sub

PrspDemoCounty is the field name on the form.
CntyDesc is all the counties that I want the user to see for that state.
tbl_counties is the table that houses all the county names and the
associated state
CntyState is the state for which that county is assigned to.
PrspDemoState is the field name on the form that holds the state
information. This is a text value.

I've tried the code and I can't see to figure out what I'm doing wrong. I
receive a Syntex error in string query expression message and not counties
are listed.

Could you please let me know how I'm screwing this up.

Much appreciated.

On Fri, 6 Aug 2004 13:39:03 -0700, Vic wrote:

I've posted this before and was told there would be some information on it
but I can't seem to find anything that really leads me to an answer. If
someone could help I would really appreciate it.

I have a form that contains demographics information. On this is the
address, city, state, zip and county.

When I user enter in the state and then goes to the county field I want them
to only see those counties for that particular state.

The state codes are all in a table and the county codes are in a different
table. The county table lists the state codes that the particular county
applies to.

I've tried some of the suggestions that I found back from July and May but I
can't seem to understand and get it to work correctly.

Could someone please help.

Use a combo box for the [County] control bound to the County Field.
Leave the Rowsource property of the [County] control blank.

What is the underlying datatype of the [State] field?
Is it Text, or Number?

If [State] is text datatype, code the [State] AfterUpdate event:

[County].Rowsource = "Select [County] from YourTable Where [State] =
'" & Me![State] & "';"

If [State] is a Number (Bound column of a Combo Box?), then:

[County].Rowsource = "Select [County] from YourTable Where [State] = "
& Me![State] & ";"

As written, you've left out a single quote (') in the Where clause.
I'll add a space between the quotes for clarity (under the V).
When you re-write it, remove the added space.
V
Where [CntyState] = ' " & Me![PrspDemoState] & "';"

The above assumes the [CntState] field is a TEXT Datatype.
Look at my example under the line "If [State] is text datatype, ...."
Also make sure the [PrspDemoCounty] Row Source Type property is set to
Table/Query.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.

 

[Guest]

I've placed that single quote ' where you informed me. No errors.

However I don't get a listing of any of the counties for the state of OH.
The combo box is totally blank.

What should I look for?

Sorry to be a pain but having difficulty. Here's what I've done.

Private Sub PrspDemoState_AfterUpdate()
[PrspDemoCounty].RowSource = "Select [CntyDesc] from tbl_counties Where
[CntyState] = " & Me![PrspDemoState] & "';"

End Sub

PrspDemoCounty is the field name on the form.
CntyDesc is all the counties that I want the user to see for that state.
tbl_counties is the table that houses all the county names and the
associated state
CntyState is the state for which that county is assigned to.
PrspDemoState is the field name on the form that holds the state
information. This is a text value.

I've tried the code and I can't see to figure out what I'm doing wrong. I
receive a Syntex error in string query expression message and not counties
are listed.

Could you please let me know how I'm screwing this up.

Much appreciated.

On Fri, 6 Aug 2004 13:39:03 -0700, Vic wrote:

I've posted this before and was told there would be some information on it
but I can't seem to find anything that really leads me to an answer. If
someone could help I would really appreciate it.

I have a form that contains demographics information. On this is the
address, city, state, zip and county.

When I user enter in the state and then goes to the county field I want them
to only see those counties for that particular state.

The state codes are all in a table and the county codes are in a different
table. The county table lists the state codes that the particular county
applies to.

I've tried some of the suggestions that I found back from July and May but I
can't seem to understand and get it to work correctly.

Could someone please help.

Use a combo box for the [County] control bound to the County Field.
Leave the Rowsource property of the [County] control blank.

What is the underlying datatype of the [State] field?
Is it Text, or Number?

If [State] is text datatype, code the [State] AfterUpdate event:

[County].Rowsource = "Select [County] from YourTable Where [State] =
'" & Me![State] & "';"

If [State] is a Number (Bound column of a Combo Box?), then:

[County].Rowsource = "Select [County] from YourTable Where [State] = "
& Me![State] & ";"

As written, you've left out a single quote (') in the Where clause.
I'll add a space between the quotes for clarity (under the V).
When you re-write it, remove the added space.
V
Where [CntyState] = ' " & Me![PrspDemoState] & "';"

The above assumes the [CntState] field is a TEXT Datatype.
Look at my example under the line "If [State] is text datatype, ...."
Also make sure the [PrspDemoCounty] Row Source Type property is set to
Table/Query.

 

[fredg]

I've placed that single quote ' where you informed me. No errors.

However I don't get a listing of any of the counties for the state of OH.
The combo box is totally blank.

What should I look for?

Sorry to be a pain but having difficulty. Here's what I've done.

Private Sub PrspDemoState_AfterUpdate()
[PrspDemoCounty].RowSource = "Select [CntyDesc] from tbl_counties Where
[CntyState] = " & Me![PrspDemoState] & "';"

End Sub

PrspDemoCounty is the field name on the form.
CntyDesc is all the counties that I want the user to see for that state.
tbl_counties is the table that houses all the county names and the
associated state
CntyState is the state for which that county is assigned to.
PrspDemoState is the field name on the form that holds the state
information. This is a text value.

I've tried the code and I can't see to figure out what I'm doing wrong. I
receive a Syntex error in string query expression message and not counties
are listed.

Could you please let me know how I'm screwing this up.

Much appreciated.

:

On Fri, 6 Aug 2004 13:39:03 -0700, Vic wrote:

I've posted this before and was told there would be some information on it
but I can't seem to find anything that really leads me to an answer. If
someone could help I would really appreciate it.

I have a form that contains demographics information. On this is the
address, city, state, zip and county.

When I user enter in the state and then goes to the county field I want them
to only see those counties for that particular state.

The state codes are all in a table and the county codes are in a different
table. The county table lists the state codes that the particular county
applies to.

I've tried some of the suggestions that I found back from July and May but I
can't seem to understand and get it to work correctly.

Could someone please help.

Use a combo box for the [County] control bound to the County Field.
Leave the Rowsource property of the [County] control blank.

What is the underlying datatype of the [State] field?
Is it Text, or Number?

If [State] is text datatype, code the [State] AfterUpdate event:

[County].Rowsource = "Select [County] from YourTable Where [State] =
'" & Me![State] & "';"

If [State] is a Number (Bound column of a Combo Box?), then:

[County].Rowsource = "Select [County] from YourTable Where [State] = "
& Me![State] & ";"

As written, you've left out a single quote (') in the Where clause.
I'll add a space between the quotes for clarity (under the V).
When you re-write it, remove the added space.
V
Where [CntyState] = ' " & Me![PrspDemoState] & "';"

The above assumes the [CntState] field is a TEXT Datatype.
Look at my example under the line "If [State] is text datatype, ...."
Also make sure the [PrspDemoCounty] Row Source Type property is set to
Table/Query.

Does it work for all the other states?

Try adding error handling to the AfterUpdate procedure that will
return an error number and a message if there is an error.
Perhaps you have something else going on.