Forms - Using 2 combo boxes in conjunction with each other

I have a form that gets all of its data from a query. The form is to be used for querying records only not inputting new records. The data in this database is for looking at courier delivery routes on certain dates.

I want to have two combo boxes. The first one will have a list of available dates. I have created this box. I then want a second box that lists all of the routes that were done on that day. This means that the second combo box is driven by the date selected in the first. I then have a number of text boxes on the form that should be populated from the date and route number that have been selected in the combo boxes.

Any help would be much appreciated.

You want to create a cascading combo box. I'm a visual learner and this link made it simpler than trying to explain.

http://datapigtechnologies.com/flashfiles/combobox2.html

If you want a working example, you can go >>HERE<<
There are 2 samples linked to that thread.

Thanks for the link but im having trouble still. The query builder for the row source will not accept the criteria statement.

I am entering [forms]![frmName].[cmbDate]

I then get an error saying the database engine does not recognise [forms]![frmName].[cmbDate] as a valid field name or expression.

I am using access 2007 if this makes a difference.

In your expression you refer to your form name as [frmname] and your combo bx as [cmbDate]. Did you use your forms actual name? I assume you changed the combo boxe's name to cmbDate.

Yes, i changed the combo box name to [cmbDate] and the form is actually called [Dartford_Week_Summary].

I have just tried it using the expression builder and it came out slightly different,

[Forms![Dartford_Week_Summary]![cmbDate]

It still didnt work though

The expression you just sent did not have the end bracket at the end of the [forms] expression. Cut and paste your expressions to avoid typos. Are you still getting the error message or is it just not working.

I don't think the . vs ! really matters as long as "!" is between the [forms] and [form name]. I typically use the "!" before forms and "." elsewhere.Someone else can better explain the exact syntax differences.

When you were in Query builder, for the column you are referencing to the parent did you add the Where statement. Also, be sure you are using the Key in the field.

Lastly, did you add the afterupdate procedure to the parent combo box. That should get the combo boxes working.

make sure to requery any sub-comboboxes (so to speak) on the afterupdate event of the combobox. Say you have 2 combo-boxes, cbo1 and cbo2 and they both reside in form1.... on the afterupdate event of cbo1, you would build a vba function to requery cbo2. Forms!Form1.cbo2.Requery

Now, for the query that cbo2 uses, you will want to put this in the criteria of the field that cbo1 controls. Forms!form1.cbo1

Keep in mind that the table that cbo2 is built off of, has to have the data that cbo1 has.

For instance cbo1 is based on table1, and cbo2 is based on table2....

Table1 (Data - not fields)
North
South

Table2 has to have north and south in the records for the cascading comboboxes to work.

Table2 (This is data, not the fields)
North Boston
North Chicago
North Seattle
South Atlanta
South Dallas
South Reno

If cbo1 was chosen to be north, then cbo2 would equal Boston, Chicago, Seattle
If cbo1 was chosen to be south, then cbo2 would equal Atlanta, Dallas, Reno

Both comboboxes can be based on the same table too.

Thanks Guys. Ive now got it working.

Cheers