Use selection from one combo box to fill-in another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My apologies if this topic has been discussed, but I searched and could not
find an answer to my specific inqury.

I have two tables (tblSeries and tblDocTypes). The only thing they have in
common is a field "Series Code".

I also have a form on which I have two combo boxes. One for Record Series
and one for Document Type. These boxes are linked to the corresponding field
in their table ("Record Series" = "Series Name" from tblSeries and "Document
Type" = "Series Name" from tblDocTypes).

What I would like is when a selection is made from the combo box "Document
Type" it will list only that "Record Series" it belongs with. Here is some
data from the two tables:

tblDocTypes tblSeries
Series Code Series Name Series Code Series Name
ACC Assets ACC Accounting
ACC Banking DEV Development
ACC Capital COR Corporate
COR Stocks INS Insurance
COR Partnership HR Human Resources
DEV Associations
DEV Building

So, if "Assets" was chosen as a "Document Type" then "Accounting" would be
returned in the "Record Series" box.

Hope this makes sense. Thanks in advance for the help!

Ember
 
While waiting for a specific response, try searching the discussion group
with the search criteria "dependent combo boxes" or "cascading combo boxes".

Good luck, I am in a similar boat.
 
I took your advise and find more information. It seems to almost work,
however I am now getting an error: "The value you entered doesn't meet the
validation rule defined for the field for control."

Here is the code I have written:

Private Sub Document_Type_AfterUpdate()
Dim strSQL As String
Dim strSQLSF As String

Record_Series = Null

strSQL = "SELECT DISTINCT tblDocTypes.Series_Code FROM tblDocTypes"
strSQL = strSQL & " WHERE tblDocTypes.Series_Name = '" & Document_Type &
"'"
strSQL = strSQL & " ORDER BY tblDocTypes.Series_Code;"

Record_Series.RowSource = strSQL

strSQLSF = "SELECT DISTINCT tblSeries.Series_Name FROM tblSeries "
strSQLSF = strSQLSF & " WHERE tblSeries.Series_Code = '" & Record_Series
& "'"
strSQLSF = strSQLSF & " ORDER BY tblSeries.Series_Code;"

Me.RecordSource = strSQLSF
Me.Requery

End Sub

Can anyone see what I have done wrong?

Thanks!
 
Back
Top