Updating combobox values from another combobox

[Guest]

I have created a database that has several tables with different sets of
data. I am trying to create a form that allows me to check specific rows in
a given table. The tables all have data organized by day. For each day
there are several records. I created a combobox that has all of the table
names in it. What I would like to let the user do is select a table from the
first combobox, then have the second combobox fill in with all the distinct
days from that table to allow the user to select the day they are interested
in. When they have done that, the appropriate records would be displayed in
a set of fields.

The easiest way I could think of to do it was to have the row source for the
second combobox fill in based on a query with the selected value in the first
combobox. So the SQL would look something like:

Select distinct(Days) from forms.myForm.cboCombo1.value

When I type this into the row source of the prperties box for combobox2, I
get something like "Can't find database at C:\Documents and Settings\My
Document\forms.myForm.cboCombo1.value.mdb"

I realize I am doing something fundamentally wrong. Any ideas?

 

[Luke Dalessandro]

ispy,

You're on the right track, I have done this many many times with
different applications, generally to allow users to browse through
complex product catalogs that have hierarchical grouping (ie
NY->restaurants->fastfood->burgers). It's sort of a cascading selection
situation.

You can't type the combobox path directly into the designer to get it to
work as far as I know.

In the afterupdate event for the first combobox, you need to dynamically
generate the sql source for the second combobox and then requery it.

Private Sub cboCombo1_afterUpdate()
If Not IsNull(cboCombo1) Then
cboCombo2.RowSourceType = "Table/Query"
cboCombo2.RowSource = "SELECT DISTINCT Days From " & _
cboCombo1
cboCombo2.Requery
Else
cboCombo2.RowSourceType = "Value List"
cboCombo2.RowSource = "Select a table from Combobox1"
cboCombo2.Requery
End If
End Sub

Good Luck,
Luke

 

[Guest]

Luke,

Worked like a champ! Thanks for the help.

ispy

 

[Guest]

Follow-up question:

I created combobox2 which is populated with all of the dates in the
particular table. However, when I use combobox2 to feed a third combobox, I
seem to be getting the number of the selected value in the list rather then
the actual value. For example, combobox1 returns the following list to
combobox2:
03/01/2004
03/02/2004
03/03/2004

If I select "03/02/2004" from combobox2, then "1" gets returned rather than
"03/02/2005". Any thoughts?