BleepingComputer.com: Drop down menu's - Microsoft Access

I am currently building a database for my school project in Access.

I wanted to have a series of drop down menu's but before you can move onto the next drop down menu you must first fill in the first drop down menu, and your choice there will effect the options that you have in the second one.

I will try and explain in terms of my actual project...It is for a dance school's shop.... for example there are items such as Leotards and Shoes.
The leotards come in sizes such as 1A 1B 2A 2 B etc, whilst the shoes come in 3, 4, 5, 6, 7....
If the item 'leotard' was selected in the first drop down menu, then for the second drop down menu (about sizes) I dont want to have the shoe sizes there, just the leotard ones.

I hope someone understands lol. How would I go about doing this?

I would appreciate any help, lizi x

(Moderator edit: added Topic Description. jgw)

This post has been edited by jgweed: 05 January 2009 - 10:25 AM

If you can code in Visual Basic try going to this website: Martin Green's Access Tips

There are many categories but the Access and SQL Parts 1-5 will help you through what you are trying to accomplish.

If you get stuck just reply and I will try to help you through it.



***Edit: There is a category called Cascading Lists that should be of interest also :-)

This post has been edited by glnbnz: 09 January 2009 - 05:26 PM

Thank you

I'll have a look at it sometime this week and get back to you if i need any help.

Thanks again, lizi

Ok...so I went with the cascading list option and did Example 1: Multiple Row Source Tables.

I have made a 'Category' Field on a 'Product info' table, where there is a combo box that retrieves its data from a table called 'category' and this allows the user to pick from 'leotards, shoes, or tights'

On the 'product info' table there is also a 'Size' Field...I have made 3 separate tables, Leotardsizes, shoesizes and Tightsizes and within these there is a field called 'Sizes' and I want it so when they choose 'leotards, shoes or tights' from the 'categories' list, they will get the relevant list of sizes in the 'size' field on the product info table/form'

Im tried out this code by substituting my table and field names i'm assuming it will be right? but could you please check it

Public Sub cascadingtest()
Select Case cboCategory.Value
Case "Leotards"
cboSizes.RowSource = "tblLeotardsizes"
Case "Shoes"
cboSizes.RowSource = "tblShoesizes"
Case "Tights"
cboSizes.RowSource = "tblTightsizes"
End Select
End Sub

The only thing is, is im not sure where to put this code, do i just enter it straight into VBA? or do i have to go through my tables to VBA...etc lol

Also, for the 'Size' part on the products field, in design mode....what do I put for 'Lookup'?

Thanks x

This post has been edited by lizi: 21 January 2009 - 02:01 PM

Okay the code you are referring to would go in the Form's VBA that has cboCategory. To do this open the form in design view, on the Database Toolbar there is a symbol for the Visual Basic code editor. Open the VBA code editor. In the box on the leftside of the editor, your form that has cboCategory should be highlighted. Now on the right part of the VBA code editor you will see 2 combo boxes. One that states (General) and one that states (Declarations).

Press the drop down in the combo box that states (General). In the list select cboCategory. After you have completed that press the drop down in the box that said (Declarations) (it will probably say BeforeUpdate now) scroll up and select AfterUpdate.

Your cursor should be in the middle and ready for code now and should look like this

Quote

Paste your code into there so that it looks like this:

Quote

I am not too sure about your "Lookup" question sorry.

I hope that this helps

This post has been edited by glnbnz: 27 January 2009 - 04:59 PM

Sorry pressed wrong button

This post has been edited by glnbnz: 27 January 2009 - 04:59 PM

Yay thank you for all your help

Got it working yay!