Simple Cascading Combo Box

[hshaker]

Hi there,

I don't understand why linking two combo boxes is so complicated.

Here is my table called "tblDemo"

PK Reason1 Reason2 Reason3
1 Soccer Goalie Lack of training
2 Soccer Goalie AB
3 Soccer Forward AC
4 FootballMidfielder AD
5 Football Midfielder AB
6 Football Defender AB
7 Baseball Midfielder AC
8 Baseball Goalie AC



For now I want to implement two combo boxes for reason 1 and 2.

I created the first combo box from tableDemo field "Reason1" and therefore I get duplicated values.

The After Update event of first combo box is:

Dim strSQL As String
'Dim strSQLSF As String

cboReason2 = Null
cboReason3 = Null

strSQL = "SELECT DISTINCT tblDemo.Reason2 FROM tblDemo"
strSQL = strSQL & " WHERE tblDemo.Reason1 = '" & cboReason1 & "'"
strSQL = strSQL & " ORDER BY tblDemo.Reason2;"

cboReason2.RowSource = strSQL

'strSQLSF = "SELECT * FROM tblDemo "
'strSQLSF = strSQLSF & " WHERE tblDemo.RowField = '" & cboRowField & "'"

'Me!sfrmForm.LinkChildFields = "RowField"
'Me!sfrmForm.LinkMasterFields = "RowField"
'Me.RecordSource = strSQLSF
Me.cboReason2.Requery

What is the problem now?

 

[KyleS]

What error are you getting? Is the secongd combo box not requerying, or is it giving the wrong info...?

Kyle

 

[TheAceMan1]

How are ya hshaker . . .

Realize since your data is not normalized using distinct in your SQL will eliminate all but one of each group (soccer, football, baseball). Hence you'll be missing reasons!

Look into normalizing your tables!:

Normalizing Tables

Table Relationships

See Ya! . . . . . .

Be sure to see FAQ219-2884: