Cascading Drop Downs Based on Previous Selection

[KirstyAmanda]

Hi there,

So I've been having trouble with my database design-(which I think I've now sorted!) and I'm looking to create the form for entering the data;
Database3.zipAccess Claim Tracker.zip
I'm wanting to replicate the drop down selection I have in this excel sheet (attached) as this is the data I will be inputting.



So my form will have 3 areas for drop downs: Area, Department and Equipment, so depending on what is selected in Area, only certain values will appear in the drop down to be selected from for Department, and then same again for Equipment, based on what was selected in Department.

I've tried using the Combo Wizard to take information for 3 tables I created with all the headings and subheadings for these 3 categories, but I get an error messaging saying "can't find valid data in tables" so I'll have to enter all the data again through the wizard; Area drop down has already been completed, as it only contains 7 entries to choose from.

I would be grateful if anyone could let me know or point me in the right direction for getting the drop downs all working in line with each other.

Many, many! Thanks in advance,

[CJ_London]

you need to modify or requery the rowsource to each combo, either in the previous combo after update event or current combo on enter event


e.g.
cboArea rowsource = "SELECT ID, areaName FROM tblArea"

cboDept rowsource = "SELECT ID, deptName FROM tblDepartments WHERE areaFK=[cboArea]

cboArea afterupdate event - cboDept.Requery

[KirstyAmanda]

Thank you!

Is there a way to do it without altering/making code? I'm not confident in my abilities with that yet, literally only done the Access beginners course, but of course my boss thinks that means we're proficient in everything..

[CJ_London]

you should be able to use a macro instead, but I can't advise because I don't use them (too restrictive, no debugging facility, unable to document what the code does and consequently little online support).

You won't get very far without using code, so recommend you grab a coffee and get stuck in.

[KirstyAmanda]

Okay thanks for the tip!

Could you point me in the direction of where I even get the code up to play about with it for this?

[Missinglinq]

This is, as Ajax suggested, really going to have to be done with code. Here are a couple of links with good examples:

Cascading List for Access Forms

Cascading Combo/List Boxes

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

Linq ;0)>

[CJ_London]

1. open the form in design view
2. select the combo you require (e.g. cboArea)
3. select properties on the ribbon (design tab) or right click on the control and select from there
4. in properties, select the events tab
5. Then select the after update event and click on the three dot carat to the right
6. Select Code builder
7. the VBA window will open with the sub created
8. enter 'cboDept.Requery' (no quotes, change cboDept to the name of the department combo) between the private sub and end sub lines

job done

Whilst in the VBA editor, ensure you have Option Explicit written just below the Option Compare Database line - this will pick up errors more quickly. You can set this up automatically for all new modules in the VBA editor by clicking Tools>Options from the menu and ticking the 'Require Variable Declaration' option.

Some tips to make learning easier

1. do not have spaces or non alphanumeric characters in field and table names - for fields use the caption property if you want spaces
2. do not use lookups in tables - see this link for reasons why http://access.mvps.org/access/lookupfields.htm
3. although they have limited use avoid using multivalue fields, they can be problematic to maintain
4. similarly avoid calculated fields - they also have limited uses
5. avoid using reserved words for table and field names - see this link https://support.office.com/en-us/art...7-da237c63eabe. You will find many such as Date, Description, Value, Desc etc. Give them meaningful names such as InvDate, OrderDate