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
