Combo box question

[Guest]

Greetings all. I am using Access 2003. I have four combos, cboRegion,
cboRoute, cboCO, and cboEWO. They cascade from cboRegion down to cboEWO with
the following code in the after update event of the previous box.
**********************************************************
Private Sub cboRegion_AfterUpdate()
On Error GoTo err_cboRegion_AfterUpdate

cboCO.RowSource = "select distinct tbljob_info.co " & _
"FROM tbljob_info " & _
"WHERE tbljob_info.region = '" & cboRegion.Value & "' " & _
"ORDER BY tbljob_info.co;"
Me.cboRegion.Requery
Me.cboCO.Requery

exit_cboRegion_AfterUpdate:
Exit Sub

err_cboRegion_AfterUpdate:
MsgBox Err.description
Resume exit_cboRegion_AfterUpdate

End Sub
******************************************************
once a region is chosen from cboRegion, the user chooses a CO from cboCO
which sets the rowsource of cboRoute which sets the rowsource of cboEWO. If
a user justs types in a value in cboEWO, rather than using the filter combos
I have Dlookups to populate cboRegion, cboCO and cboRoute with the values
corresponding to the entry in cboEWO. The Dlookups work as they should, but
is there a way to make cboCO be filtered as if the rowsource had been set by
cboRegion? As it is now when I type in a value in cboEWO I see the value for
the other combos pop up but when I click on them they do not show the
filtered list, the show all records in the record source. I do not know if I
am explaining it well, so please let me know. Thank you.

 

[Douglas J. Steele]

There's no reason for the Me.cboRegion.Requery in your event. I suspect what
may be happening is that it's resetting the value of cboRegion.

 

[Guest]

Thanks for the reply. Yeah, I just forgot to take out the requeries before I
cut and pasted. I was trying to requery anywhere I thought I might be able
to load the combo list as it would if its rowsource had been set by the after
update event of the preceding combo. In the after update event of the final
combo I tried to set the rowsource of all the combos based on the entry in
cboEWO, but it did not work. Is it even possible to do what I am trying to
do?

 

[Douglas J. Steele]

If I'm understanding what you're trying to do correctly, it should be
possible.

One hint while debugging. Instead of setting the RowSource directly to the
string, assign the string to a variable, and then look at what's in that
variable:

Private Sub cboRegion_AfterUpdate()
On Error GoTo err_cboRegion_AfterUpdate

Dim strSQL As String

strSQL = "select distinct tbljob_info.co " & _
"FROM tbljob_info " & _
"WHERE tbljob_info.region = '" & cboRegion.Value & "' " & _
"ORDER BY tbljob_info.co;"
debug.print strSQL
cboCO.RowSource = strSQL
Me.cboCO.Requery
exit_cboRegion_AfterUpdate:
Exit Sub

err_cboRegion_AfterUpdate:
MsgBox Err.description
Resume exit_cboRegion_AfterUpdate

End Sub

That'll print the SQL to the Immediate Window (Ctrl-G). Check that the SQL
looks correct: copy it into a query and try running it.