ComboBox To Control a Data Sheet

[m011]

Hi all,


I am trying to create a subform that has a combobox in it controlling a datasheet that is below it. When I click on the name of the candy producer, I would like the datasheet to show the different varieties of candy that producer makes. I have one table that lists all of the candy producers, with each one having a unique ID, as well as a few other fields. Then, I have another table for the candy varieties which is made up of a composite key - the producer ID and the candy variety ID. These varieties are stored in a separate table. My problem is that I cannot figure out how to set up the subform so that when you click on one of the 16 different candy producers, the datasheet below displays the candy varieties which that producer makes.

Hopefully I have given enough information for someone to be able to help.



Thanks

[June7]

Could possibly have the subform be a dynamic parameterized query that pulls records from the ProducerCandy table with the parameter being a reference to the combobox.

Why do you need this arrangement? What are you trying to do - data entry of some sort?

[m011]

That sounds like a good idea, but I have no clue how I would do something like that.

It's a school assignment, but yes data entry is the main purpose.

[m011]

I should also add that I am not using any programming, it's more or less supposed to be using the wizards contained within access.

[Missinglinq]

Sorry, but you're not going to do that with Wizards; it's going to take some VBA code! The concept is usually referred to as "cascading comboboxes," where a second Combobox is populated depending on the selection from the first Combobox In this case, instead of basing the Combobox's RowSource on the selection from the Combobox, you'd base the RecordSource of the Form on it. Here are a couple of links with examples:

http://www.fontstuff.com/access/acctut10.htm

http://bytes.com/forum/thread605958.html

If you Google the term "Cascading Comboboxes" along with "MS Access" you'll probably get a gazillion hits!

Linq ;0)>

[June7]

The wizards do use code called macros. I am confused about what you want. You said you want to control a subform but what Linq describes is what most people are actually looking for.

[m011]

Sorry, I probably didn't give enough information.

But I figured out what I was doing wrong from my classmates. When using the form wizard, I had to create the form with a subform as I was using fields from different tables. The wizard gives the user this option after selecting which fields they want. I was selecting the option that gave just one form.

Thanks to those who tried to help!