Displaying dependent dropdown comboboxes based on database values

[Prevost]

Hi There. So I am completely new to Access and this is my first post. I have read some online information (About.com, Microsoft help, etc) and some books (that I also got online). I like these forums because it introduces me to a lot of features quickly.

I am in the midst of creating my own database for my work that contains information to create our products (along with pricing). It is more or less a product configurator (or at least that is my goal). I was previously using Excel to accomplish this but I believe the actual data for product pricing belongs in a database (since you can literally change our current Excel sheets and the price would change and no one would realize) and then I would like to extend this into creating your own product. This created product would then be put into the database.

So, is there a way to create a form or query with comboboxes whose drop down lists depend on the previous combobox value? Any help is greatly appreciated!

[ItsMe]

You mean like Cascading Dependent Comboboxes?

[alansidman]

This might be helpful

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

[Prevost]

Thanks. I think that is what I am after. I will look into that and then let you know how I made out. Thanks again!

[Prevost]

Will cascading dependent comboboxes results in a whole bunch of IF statements? For example, creating an IF statement for each unique value that could be selected from the previous combobox? Thanks.

[ItsMe]

It shouldn't. I briefly looked at the link Allan provided and don't quite understand why they are introducing a Case Select statement to assign an Index. Your tables and your combos should include a PK value.

The RowSource of a dependent combo will have the parent combo's value property within it (Parameterized). The combo's "Bound Column" should be a Primary Key value. If the Rowsource of the Combo is written correctly, you only need to use IIF to evaluate for Null. Either the parent combo is Null or it has a Value. A value that is a number (PK).

I like Alan's example here, in this link. It is simple and to the point. If your tables are Normalized, you will be off and running in 12 minutes.
http://www.datapigtechnologies.com/f...combobox2.html

There are many ways to approach this topic. After you have a basic understanding of the Combo's various properties and methods, you can use VBA to ensure your combos behave correctly for the User. Here are VBA examples in this sample DB.
https://www.accessforums.net/sample-...tml#post200624

Another approach is to create a Named Saved Query Object in Access that uses criteria as a Dynamic Parameterized query. You do this by naming the Combos on your form within the Named Query Object. This is a lot like Alan's video only that you are using a Query Object vs. a Dynamic Parameterized SQL statement within a RowSource of a parent Combo.

[Prevost]

Thanks. I am going to look take a look at those links you posted. All of my data is contained in one table where the primary key is ID created by AutoNumber. My question is do I have to split that table up into multiple tables, or can I create the cascading drop down menus using this one table alone. I imported the data from an excel spreadsheet and the reason why I kept it in one spreadsheet is because they are all unique records. But since there are 1000 of them, I wanted to something that allows the user to select options from the drop down menu (instead of looking it up everytime).

I will keep on reading and trying things!

Thanks.

[ItsMe]

You can add Criteria to a query. This way, the query will only retrieve data that meets the criteria. The criteria can be the value of another combo. This will cause the query to be dependent on the combo. You can also add criteria that is not dynamic, e.g. >500.


You can add multiple criteria to your query too. This can get tricky because you need to decide on the AND operator or the OR operator. Do you want your criteria to be the value of the combo AND >500??? Maybe you want the criteria to be the value of the combo OR >500, instead.

[Prevost]

Do you have any suggestions off hand of the best resources for learning Access? Besides the obviousness of these forums, the internet and Access books; maybe the ones that stick out from the rest?

Thanks.

[ItsMe]

It depends on what your objectives are. You can use knowledge from various resources, not directly related to Access, to help you understand and use Access.
https://www.accessforums.net/access/...tml#post222629

I would probably start with online tutorials regarding RDBMS and then some books on Access, Microsoft Press. You need to understand RDBMS (at least the basics) before anything else.

[alansidman]

My two favorite sites to get people started are:

http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

and

http://www.accessmvp.com/strive4peace/

[anhtuanpt]

an useful writing