Limit values in dropdown box in form field2 based on value entered in form field1

[OBTech]

Hi all,

New to the forum and have a problem in an Access db I'm working on.
Basically, it is a booking system for various dates (field2) at various venues (field1)- only certain dates are available at certain venues.
I have designed a 'booking' form in access that populates the 'Bookings' table with the data entered in this form.
A simple query on table 'Venues' (which is already populated) provides the basis for the combobox for field1.
A 'Tour_dates' query on table 'Tour-Dates' (which is already populated) provides the basis for the combobox for field1.
I want field2's combobox only to show the dates that are available for the venue entered in field1 on the form.
I actually have it working, but....if the user were to go back and change their choice of venue in field1, the dates for their original venue choice continue to appear in field2's combobox.
How can I get field2's combobox to show the dates for the user's revised choice of venue, i.e. re-run the Tour_dates query for the revised choice of venue - without closing the form and starting again?

Thanks in advance

[zmbd]

1st - Welcome!

Just to sum things up, this sounds very similar to the cascading comboboxes discussed in the following:
home > topics > microsoft access / vba > insights > cascading combo/list boxes

Read thru the aforementioned article. If there is something specific there that you don't "get," or, if I'm not on the right track here, please post it back to this thread and we'll go from there!

(there's also a link to Filtering on a form in the article that you might want to toss in your "toolbox" for later use :) )

[OBTech]

Thank you zmdb,both for the welcome and the help!

I have recreated the cascading comboboxes as suggested and it works. However, if I am to use the chosen data from these 'unbound' comboboxes to populate another table, how could I do that? To continue to use the example, let's say we want a 'Bookings' system for a communal meeting room in the building that the companies occupy. The 'Bookings' table has three columns - Booking_ID (autonumber), company_Id and employee_Eid.
How can I populate the 'Bookings' table with a form using the cascading comboboxes bound to this table?

Thanks again.

[NeoPa]

Hi OBTech.
First let me point you to another article with a similar topic. You probably won't need it as it overlaps with the one ZMBD posted already but they aren't exactly the same so it may help further your understanding.

As for your new question, that is something that I'll give a tip on, but that's all in here. If you need to develop that further then you'll need to ask the question in a separate thread. Each thread should only deal with one topic here.

The tip is simply to take the values from any of the ComboBox filters that have been selected and add them into the .DefaultValue properties of the bound controls on the form where you want to set this data. Although this isn't explained very fully in the linked article, it is covered.

[OBTech]

Thank you for reply NeoPa.

And this other article you refer to is .....where?

With all due respect, this is not a 'new' question but rather is related to my OP in which I say I have designed a 'booking' form in access that populates the 'Bookings' table with the data entered in this form.
The previous mod ZMBD has suggested cascading combos, and this has brought me part of the way.
As invited by him please post it back to this thread and we'll go from there!, this is exactly what I have done.

I'll wait for his assistance, but in the meantime, the link to your article would be appreciated.

Thanks

[NeoPa]

OBTech:
And this other article you refer to is .....where?

Good question. It was supposed to be included within my last post. Here it is now (Cascaded Form Filtering).

OBTech:
With all due respect, this is not a 'new' question but rather is related to my OP in which I say ...

That's as may be, and I'm more than happy that you hold that opinion. Nevertheless, I see no reason to change my mind on the status of the extra question or how appropriate it is for this thread.

To ensure you get the current interest of all members already subscribed to this thread, you're welcome to post a link in here to your new thread if you choose to pursue that avenue. I'm sure everyone involved will be happy to follow the link and do what they can to help with your new thread.