Cascading Combo error

T

Techknownothing

I am using Access 2003.
I have a form with 2 combo boxes:
combo1=State
combo2=Submarket

I am using the following code in the After Update Event of combo1 so
that combo2 will populate from one of three table based on the
selection in combo1:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
End Sub

It doesn't work and I am not getting any error messages.
Any suggestions?

Jeff Clyburn
DCide
 
S

Sprinks

What you have is fine; just add

Me![cboCluster].Requery

after setting the RowSource. You should also be aware that putting the same
type of data in different tables is NOT a good idea, and breaks the rules of
database normalization, which will make your database more difficult to
maintain.

Rather add a field to a single table that enables you to distinguish between
the various subregions. Then you can create a query that uses the value of
your first combo box to select the proper submarket values. The query would
have as its critieria (in the field corresponding to cboState):

=Forms!YourFormName![cboState]

Hope that helps.
Sprinks
 
T

Techknownothing

Firstly, thanks for your help.
I will follow your suggestion and add a field to a single table shortly
but I would like to learn why this is not working first.

Right now my code reads:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
End Sub

Private Sub STATE_AfterUpdate()
Me![cboCluster].Requery
End Sub

I am getting the following error:

"Run-time error '2465'
Access can't find the field 'cboCluster' referred to in your
expression"

I checked the form properties and the combo is properly named 'Cluster'
any thoughts as to why I'm getting this error?

Jeff Clyburn
DCide
What you have is fine; just add

Me![cboCluster].Requery

after setting the RowSource. You should also be aware that putting the same
type of data in different tables is NOT a good idea, and breaks the rules of
database normalization, which will make your database more difficult to
maintain.

Rather add a field to a single table that enables you to distinguish between
the various subregions. Then you can create a query that uses the value of
your first combo box to select the proper submarket values. The query would
have as its critieria (in the field corresponding to cboState):

=Forms!YourFormName![cboState]

Hope that helps.
Sprinks

Techknownothing said:
I am using Access 2003.
I have a form with 2 combo boxes:
combo1=State
combo2=Submarket

I am using the following code in the After Update Event of combo1 so
that combo2 will populate from one of three table based on the
selection in combo1:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
End Sub

It doesn't work and I am not getting any error messages.
Any suggestions?

Jeff Clyburn
DCide
 
D

Duncan Bachen

Techknownothing said:
I am using Access 2003.
I have a form with 2 combo boxes:
combo1=State
combo2=Submarket

I am using the following code in the After Update Event of combo1 so
that combo2 will populate from one of three table based on the
selection in combo1:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
End Sub

It doesn't work and I am not getting any error messages.
Any suggestions?

Jeff Clyburn
DCide

In your example, you say that combo2 is called "Submarket", yet your
example code calls it cboCluster.

Make sure to requery the combo box after you set the row source.
cboCluster.Requery
 
T

Techknownothing

I changed the combo name from Submarket to Cluster.
Could that be the source of the problem?

Jeff Clyburn
DCide
 
S

Sprinks

Events are associated with Forms and Controls, not fields. Make sure your
combo box is named cboState, and change your code to:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select

' Requery the cluster combo box
Me![cboCluster].Requery

End Sub

Sprinks

Techknownothing said:
Firstly, thanks for your help.
I will follow your suggestion and add a field to a single table shortly
but I would like to learn why this is not working first.

Right now my code reads:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
End Sub

Private Sub STATE_AfterUpdate()
Me![cboCluster].Requery
End Sub

I am getting the following error:

"Run-time error '2465'
Access can't find the field 'cboCluster' referred to in your
expression"

I checked the form properties and the combo is properly named 'Cluster'
any thoughts as to why I'm getting this error?

Jeff Clyburn
DCide
What you have is fine; just add

Me![cboCluster].Requery

after setting the RowSource. You should also be aware that putting the same
type of data in different tables is NOT a good idea, and breaks the rules of
database normalization, which will make your database more difficult to
maintain.

Rather add a field to a single table that enables you to distinguish between
the various subregions. Then you can create a query that uses the value of
your first combo box to select the proper submarket values. The query would
have as its critieria (in the field corresponding to cboState):

=Forms!YourFormName![cboState]

Hope that helps.
Sprinks

Techknownothing said:
I am using Access 2003.
I have a form with 2 combo boxes:
combo1=State
combo2=Submarket

I am using the following code in the After Update Event of combo1 so
that combo2 will populate from one of three table based on the
selection in combo1:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
End Sub

It doesn't work and I am not getting any error messages.
Any suggestions?

Jeff Clyburn
DCide
 
T

Techknownothing

Now I am getting the following error:

"The expression After Update you entered as the event property setting
produced the following error:
Ambiguous name detected: cboState_AfterUpdate."

It seems Access 2003 is having trouble locating the combo boxes.
Any thoughts?

Jeff Clyburn
DCide

It seems
Sprinks said:
Events are associated with Forms and Controls, not fields. Make sure your
combo box is named cboState, and change your code to:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select

' Requery the cluster combo box
Me![cboCluster].Requery

End Sub

Sprinks

Techknownothing said:
Firstly, thanks for your help.
I will follow your suggestion and add a field to a single table shortly
but I would like to learn why this is not working first.

Right now my code reads:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
End Sub

Private Sub STATE_AfterUpdate()
Me![cboCluster].Requery
End Sub

I am getting the following error:

"Run-time error '2465'
Access can't find the field 'cboCluster' referred to in your
expression"

I checked the form properties and the combo is properly named 'Cluster'
any thoughts as to why I'm getting this error?

Jeff Clyburn
DCide
What you have is fine; just add

Me![cboCluster].Requery

after setting the RowSource. You should also be aware that putting the same
type of data in different tables is NOT a good idea, and breaks the rules of
database normalization, which will make your database more difficult to
maintain.

Rather add a field to a single table that enables you to distinguish between
the various subregions. Then you can create a query that uses the value of
your first combo box to select the proper submarket values. The query would
have as its critieria (in the field corresponding to cboState):

=Forms!YourFormName![cboState]

Hope that helps.
Sprinks

:

I am using Access 2003.
I have a form with 2 combo boxes:
combo1=State
combo2=Submarket

I am using the following code in the After Update Event of combo1 so
that combo2 will populate from one of three table based on the
selection in combo1:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
End Sub

It doesn't work and I am not getting any error messages.
Any suggestions?

Jeff Clyburn
DCide
 
S

Sprinks

This error is caused by having two AfterUpdate event procedures with the same
name. Search your module and remove the unneeded one.

Sprinks

Techknownothing said:
Now I am getting the following error:

"The expression After Update you entered as the event property setting
produced the following error:
Ambiguous name detected: cboState_AfterUpdate."

It seems Access 2003 is having trouble locating the combo boxes.
Any thoughts?

Jeff Clyburn
DCide

It seems
Sprinks said:
Events are associated with Forms and Controls, not fields. Make sure your
combo box is named cboState, and change your code to:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select

' Requery the cluster combo box
Me![cboCluster].Requery

End Sub

Sprinks

Techknownothing said:
Firstly, thanks for your help.
I will follow your suggestion and add a field to a single table shortly
but I would like to learn why this is not working first.

Right now my code reads:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
End Sub

Private Sub STATE_AfterUpdate()
Me![cboCluster].Requery
End Sub

I am getting the following error:

"Run-time error '2465'
Access can't find the field 'cboCluster' referred to in your
expression"

I checked the form properties and the combo is properly named 'Cluster'
any thoughts as to why I'm getting this error?

Jeff Clyburn
DCide

Sprinks wrote:
What you have is fine; just add

Me![cboCluster].Requery

after setting the RowSource. You should also be aware that putting the same
type of data in different tables is NOT a good idea, and breaks the rules of
database normalization, which will make your database more difficult to
maintain.

Rather add a field to a single table that enables you to distinguish between
the various subregions. Then you can create a query that uses the value of
your first combo box to select the proper submarket values. The query would
have as its critieria (in the field corresponding to cboState):

=Forms!YourFormName![cboState]

Hope that helps.
Sprinks

:

I am using Access 2003.
I have a form with 2 combo boxes:
combo1=State
combo2=Submarket

I am using the following code in the After Update Event of combo1 so
that combo2 will populate from one of three table based on the
selection in combo1:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
End Sub

It doesn't work and I am not getting any error messages.
Any suggestions?

Jeff Clyburn
DCide
 
T

Techknownothing

Thanks.
That got rid of the error....but "cboCluster" still won't populate.
Sorry to bother you with such a minor problem but I am learning and
want to understand everything.

Here is the code I am using now:
**********************************************
Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
Me![cboCluster].Requery
End Sub
*************************************************
any thoughts?

Jeff Clyburn
DCide


This error is caused by having two AfterUpdate event procedures with the same
name. Search your module and remove the unneeded one.

Sprinks

Techknownothing said:
Now I am getting the following error:

"The expression After Update you entered as the event property setting
produced the following error:
Ambiguous name detected: cboState_AfterUpdate."

It seems Access 2003 is having trouble locating the combo boxes.
Any thoughts?

Jeff Clyburn
DCide

It seems
Sprinks said:
Events are associated with Forms and Controls, not fields. Make sure your
combo box is named cboState, and change your code to:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select

' Requery the cluster combo box
Me![cboCluster].Requery

End Sub

Sprinks

:

Firstly, thanks for your help.
I will follow your suggestion and add a field to a single table shortly
but I would like to learn why this is not working first.

Right now my code reads:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
End Sub

Private Sub STATE_AfterUpdate()
Me![cboCluster].Requery
End Sub

I am getting the following error:

"Run-time error '2465'
Access can't find the field 'cboCluster' referred to in your
expression"

I checked the form properties and the combo is properly named 'Cluster'
any thoughts as to why I'm getting this error?

Jeff Clyburn
DCide

Sprinks wrote:
What you have is fine; just add

Me![cboCluster].Requery

after setting the RowSource. You should also be aware that putting the same
type of data in different tables is NOT a good idea, and breaks the rules of
database normalization, which will make your database more difficult to
maintain.

Rather add a field to a single table that enables you to distinguish between
the various subregions. Then you can create a query that uses the value of
your first combo box to select the proper submarket values. The query would
have as its critieria (in the field corresponding to cboState):

=Forms!YourFormName![cboState]

Hope that helps.
Sprinks

:

I am using Access 2003.
I have a form with 2 combo boxes:
combo1=State
combo2=Submarket

I am using the following code in the After Update Event of combo1 so
that combo2 will populate from one of three table based on the
selection in combo1:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
End Sub

It doesn't work and I am not getting any error messages.
Any suggestions?

Jeff Clyburn
DCide
 
S

Sprinks

No need to apologize--we were all beginners once, and the people who answer
questions here do so because they want to spread what they've learned and see
the one in trouble get their problem solved.

I suspect that your problem is with the RowSourceType, ColumnCount or
ColumnWidths properties. RowSourceType should be set to "Table/Query", since
your RowSource is the name of a table. When it has this setting, ColumnCount
means is the number of columns included, ordered by their order in table
design view. ColumnWidths is the space allocated on screen in the drop-down
to each column. If, for example, you have the ColumnCount set to 0, NO
fields will be included. If all ColumnWidths of the selected columns are set
to zero, they will be selected but not visible. If this doesn't resolve your
problem, please post the property values of the submarket combo box.

By the way, a good way to use a combo box is set the Bound Column to 1,
which, assuming the Primary Key is the first column, will store the PK in the
field. If you additionally set the first column width to 0", the 2nd column
will display after the selection. Meaning the user sees more meaningful
text, but your database efficiently stores only the numeric primary key.
Note, however, that this strategy would not work with your current design,
since you'd have no way of knowing which table the primary key came from.

The following sources have good information on many topics, including
"Database Normalization", which I believe is the most important topic to
master in relational database design.

Getting Started:

http://www.mvps.org/access/tencommandments.htm

Glossary of database terms:
http://www.dhdursoassociates.com/database-glossary-3.html

Where to find information about designing a database in Microsoft Access:
http://support.microsoft.com/?id=289533

ACC: Database Normalization Basics
http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Support WebCast: Database Normalization Basics
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc060600/wcblurb060600.asp

Database Normalization:
http://burks.bton.ac.uk/burks/foldoc/35/28.htm

5 Rules of Database Normalization:
http://www.datamodel.org/NormalizationRules.html

"Understanding Relational Database Design" Document Available in Download
Center:
http://support.microsoft.com/?id=283698
http://support.microsoft.com/?id=164172

ACC2000: "Understanding Relational Database Design"
http://support.microsoft.com/?id=234208

Fundamentals of Relational Database Design:
http://support.microsoft.com/?id=129519

Database Deisgn Principles:
http://msdn.microsoft.com/library/en-us/dndbdes/html/ch04DDP.asp


Hope that helps.
Sprinks

Techknownothing said:
Thanks.
That got rid of the error....but "cboCluster" still won't populate.
Sorry to bother you with such a minor problem but I am learning and
want to understand everything.

Here is the code I am using now:
**********************************************
Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
Me![cboCluster].Requery
End Sub
*************************************************
any thoughts?

Jeff Clyburn
DCide


This error is caused by having two AfterUpdate event procedures with the same
name. Search your module and remove the unneeded one.

Sprinks

Techknownothing said:
Now I am getting the following error:

"The expression After Update you entered as the event property setting
produced the following error:
Ambiguous name detected: cboState_AfterUpdate."

It seems Access 2003 is having trouble locating the combo boxes.
Any thoughts?

Jeff Clyburn
DCide

It seems
Sprinks wrote:
Events are associated with Forms and Controls, not fields. Make sure your
combo box is named cboState, and change your code to:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select

' Requery the cluster combo box
Me![cboCluster].Requery

End Sub

Sprinks

:

Firstly, thanks for your help.
I will follow your suggestion and add a field to a single table shortly
but I would like to learn why this is not working first.

Right now my code reads:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
End Sub

Private Sub STATE_AfterUpdate()
Me![cboCluster].Requery
End Sub

I am getting the following error:

"Run-time error '2465'
Access can't find the field 'cboCluster' referred to in your
expression"

I checked the form properties and the combo is properly named 'Cluster'
any thoughts as to why I'm getting this error?

Jeff Clyburn
DCide

Sprinks wrote:
What you have is fine; just add

Me![cboCluster].Requery

after setting the RowSource. You should also be aware that putting the same
type of data in different tables is NOT a good idea, and breaks the rules of
database normalization, which will make your database more difficult to
maintain.

Rather add a field to a single table that enables you to distinguish between
the various subregions. Then you can create a query that uses the value of
your first combo box to select the proper submarket values. The query would
have as its critieria (in the field corresponding to cboState):

=Forms!YourFormName![cboState]

Hope that helps.
Sprinks

:

I am using Access 2003.
I have a form with 2 combo boxes:
combo1=State
combo2=Submarket

I am using the following code in the After Update Event of combo1 so
that combo2 will populate from one of three table based on the
selection in combo1:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
End Sub

It doesn't work and I am not getting any error messages.
Any suggestions?

Jeff Clyburn
DCide
 
T

Techknownothing

Very cool...it works.
I had improperly changed the "Control Source" instead of the "Name"
Once I changed the names correctly, everything fell into place.
Thanks again for your help.
Now I'm gonna trash it and build it correctly.

Jeff Clyburn
DCide
No need to apologize--we were all beginners once, and the people who answer
questions here do so because they want to spread what they've learned and see
the one in trouble get their problem solved.

I suspect that your problem is with the RowSourceType, ColumnCount or
ColumnWidths properties. RowSourceType should be set to "Table/Query", since
your RowSource is the name of a table. When it has this setting, ColumnCount
means is the number of columns included, ordered by their order in table
design view. ColumnWidths is the space allocated on screen in the drop-down
to each column. If, for example, you have the ColumnCount set to 0, NO
fields will be included. If all ColumnWidths of the selected columns are set
to zero, they will be selected but not visible. If this doesn't resolve your
problem, please post the property values of the submarket combo box.

By the way, a good way to use a combo box is set the Bound Column to 1,
which, assuming the Primary Key is the first column, will store the PK in the
field. If you additionally set the first column width to 0", the 2nd column
will display after the selection. Meaning the user sees more meaningful
text, but your database efficiently stores only the numeric primary key.
Note, however, that this strategy would not work with your current design,
since you'd have no way of knowing which table the primary key came from.

The following sources have good information on many topics, including
"Database Normalization", which I believe is the most important topic to
master in relational database design.

Getting Started:

http://www.mvps.org/access/tencommandments.htm

Glossary of database terms:
http://www.dhdursoassociates.com/database-glossary-3.html

Where to find information about designing a database in Microsoft Access:
http://support.microsoft.com/?id=289533

ACC: Database Normalization Basics
http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Support WebCast: Database Normalization Basics
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc060600/wcblurb060600.asp

Database Normalization:
http://burks.bton.ac.uk/burks/foldoc/35/28.htm

5 Rules of Database Normalization:
http://www.datamodel.org/NormalizationRules.html

"Understanding Relational Database Design" Document Available in Download
Center:
http://support.microsoft.com/?id=283698
http://support.microsoft.com/?id=164172

ACC2000: "Understanding Relational Database Design"
http://support.microsoft.com/?id=234208

Fundamentals of Relational Database Design:
http://support.microsoft.com/?id=129519

Database Deisgn Principles:
http://msdn.microsoft.com/library/en-us/dndbdes/html/ch04DDP.asp


Hope that helps.
Sprinks

Techknownothing said:
Thanks.
That got rid of the error....but "cboCluster" still won't populate.
Sorry to bother you with such a minor problem but I am learning and
want to understand everything.

Here is the code I am using now:
**********************************************
Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
Me![cboCluster].Requery
End Sub
*************************************************
any thoughts?

Jeff Clyburn
DCide


This error is caused by having two AfterUpdate event procedures with the same
name. Search your module and remove the unneeded one.

Sprinks

:

Now I am getting the following error:

"The expression After Update you entered as the event property setting
produced the following error:
Ambiguous name detected: cboState_AfterUpdate."

It seems Access 2003 is having trouble locating the combo boxes.
Any thoughts?

Jeff Clyburn
DCide

It seems
Sprinks wrote:
Events are associated with Forms and Controls, not fields. Make sure your
combo box is named cboState, and change your code to:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select

' Requery the cluster combo box
Me![cboCluster].Requery

End Sub

Sprinks

:

Firstly, thanks for your help.
I will follow your suggestion and add a field to a single table shortly
but I would like to learn why this is not working first.

Right now my code reads:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
End Sub

Private Sub STATE_AfterUpdate()
Me![cboCluster].Requery
End Sub

I am getting the following error:

"Run-time error '2465'
Access can't find the field 'cboCluster' referred to in your
expression"

I checked the form properties and the combo is properly named 'Cluster'
any thoughts as to why I'm getting this error?

Jeff Clyburn
DCide

Sprinks wrote:
What you have is fine; just add

Me![cboCluster].Requery

after setting the RowSource. You should also be aware that putting the same
type of data in different tables is NOT a good idea, and breaks the rules of
database normalization, which will make your database more difficult to
maintain.

Rather add a field to a single table that enables you to distinguish between
the various subregions. Then you can create a query that uses the value of
your first combo box to select the proper submarket values. The query would
have as its critieria (in the field corresponding to cboState):

=Forms!YourFormName![cboState]

Hope that helps.
Sprinks

:

I am using Access 2003.
I have a form with 2 combo boxes:
combo1=State
combo2=Submarket

I am using the following code in the After Update Event of combo1 so
that combo2 will populate from one of three table based on the
selection in combo1:

Private Sub cboState_AfterUpdate()
On Error Resume Next
Select Case cboState.Value
Case "DC"
cboCluster.RowSource = "tblCLUSTERDC"
Case "VA"
cboCluster.RowSource = "tblCLUSTERVA"
Case "MD"
cboCluster.RowSource = "tblCLUSTERMD"
End Select
End Sub

It doesn't work and I am not getting any error messages.
Any suggestions?

Jeff Clyburn
DCide
 

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