Cascading 3 combo boxes help

[Joanne]

I mistakenly posted this to the excel programming group. Please excuse
this double posting.

I have these two cboboxes in my app and the 'cascading' works great.
What I need is the coding for ThirdCboBox that would be using both of
the first two cboboxes to limit the choices available to the userin
the third cbobox , but I don't know how to construct the sql statement
to include two 'limit' criterias in one statement.
Please help me with this - I sure appreciate your time and expertise,
as always. You guys have made this project a fun and successful deal
for me.

First CboBox
Private Sub cboCoName_AfterUpdate()
Me!cboLocation.RowSource = "select tblmain.[location] from tblmain
where tblmain.[CoName] ='" & Me!cboCoName & "'"
Me.cboCoName.Requery
End Sub


SecondCboBox
Private Sub cboLocation_AfterUpdate()
Me!cboStreet.RowSource = "select tblmain.[street] from tblmain
where tblmain.[Location] ='" & Me!cboLocation & "'"
Me.cboStreet.Requery
End Sub

TIA
Joanne

 

[Douglas J. Steele]

Without knowing anything about your tables, you probably want something
like:

Private Sub cboLocation_AfterUpdate()
Me!cboStreet.RowSource = "select tblmain.[street] from tblmain " & _
"where tblmain.[Location] ='" & Me!cboLocation & _
"' and tblmain.[CoName] ='" & Me!cboCoName & "'"
Me.cboStreet.Requery
End Sub

You might also want to use "select distinct" rather than simply "select"
(and you might want to put an "order by" clause in there so that they're
returned in some logical order)

 

[Joanne]

thanks Douglas
Works like a charm, and I added your suggestions.
What does adding the word Distinct to the select statement do?

 

[Douglas J. Steele]

If there are any duplicates in the table, it only returns one instance,
rather than having them appear multiple times.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

thanks Douglas
Works like a charm, and I added your suggestions.
What does adding the word Distinct to the select statement do?

Without knowing anything about your tables, you probably want something
like:

Private Sub cboLocation_AfterUpdate()
Me!cboStreet.RowSource = "select tblmain.[street] from tblmain " & _
"where tblmain.[Location] ='" & Me!cboLocation & _
"' and tblmain.[CoName] ='" & Me!cboCoName & "'"
Me.cboStreet.Requery
End Sub

You might also want to use "select distinct" rather than simply "select"
(and you might want to put an "order by" clause in there so that they're
returned in some logical order)