dependent combos

  • Thread starter Thread starter fapa via AccessMonster.com
  • Start date Start date
F

fapa via AccessMonster.com

can a combo box auto populate another combo box which auto populates another?


i have a form whereby a user chooses a class they want to attend from a combo
box. after choosing, they have to choose the dates the class is available at
(the combo box only displays the dates associated with that class). and from
this choice they pick the time (which is dependent on class and date)

i'm not sure if this is possible, can someone guide me to the right path?
 
This is a common thing to do. You will see it referred to as "Cascading
Combos".
The technigue is to manipulate the rowsource of the combos. So, When the
user selects a class in combo1, The After Update event of combo1 should
change the rowsource of combo2 to include only those occurances of that
class, then requery combo2. Then, combo2's After Update does the same to
filter combo3's rowsource. They way that is typically done is to base your
combos on queries that you can filter based on what was selected in the
previous combo. Another method is rather than using stored queries, is to
create SQL strings that do the filtering for the combo, and change the
rowsource to that SQL.
 
Your suggestion makes sense, however when im trying to code it, its not
working. i think im typing in the wrong stuff (as am v.new to access). I've
checked out some of the coding from other threads and have tried to make them
work however, the Date - displays the dates of all classes instead of only
showing the dates that class is running. how can i stop this as i dont know
how to filter date either (am pretty useless!) can you help?
 
What are you using to display the classes? You should be using a query an
you can filter the query with the name of the class. So For starters, in the
criteria row for the class name column in your query builder enter this:
[Enter Class Name]

Then when the query runs, it will pop up a box and ask for the class name.
Enter the class you want to see, hit enter, and your query will come up with
only the dates for that class.
 
Hi Klatuu

I create a query as u suggested with the fields - ClassName, ClassDate,
ClassTime. However, when i create a combo box on a form, it keeps coming back
with a Mismatch Criteria. I've typed in the following code for ClassName to
display the date's the chosen class is on:

Private Sub CmbYoga_AfterUpdate()
Dim sSQL As String

'This function sets the RowSource of cboInvoice, based on the
'value selected in cboCustomer.
sSQL = "SELECT Class_id, ClassName, ClassDate, ClassTime " _
& " FROM ClassSchedule WHERE ClassName = " & Me.CmbYoga _
& " ORDER BY ClassName"

Me.ClassDate.RowSource = sSQL

Me.ClassDate.Requery 'Requery the combo
End Sub

Im not sure why im getting an error! All three fields are from the same table.


The reason im doing cascading combo's is becasue i want staff to look up a
particular yogo class (without typing it in) and then pick a date and time
the class is on so it can be booked.

Can you help?
 
It is likely ClassName is a text field. The syntax you are using in the
WHERE clause if for a numeric field. Add single quotes around Me.cmbYoga
like this:

sSQL = "SELECT Class_id, ClassName, ClassDate, ClassTime " _
& " FROM ClassSchedule WHERE ClassName = '" & Me.CmbYoga _
& "' ORDER BY ClassName"
 
Back
Top