Difficulties with cascading combo boxes

[Toireasa]

Hi,

Newbie Access developer here, and my first post on this forum, so I might not get everything right - thanks in advance for your help and your patience!

I'm using Access 2007, in XP. I'm currently trying to set up a whole pile of cascading combo boxes of different levels of complexity, so I started with the easiest set - and can't even get that to work (even using the tutorial on this site at http://bytes.com/insights/access/605...mbo-list-boxes) , so I'm turning to you guys for assistance. My table structure and code for this is as follows.

Tables: "Specialties" and "Departments"
where "Specialties" includes a field (DepartmentsID) which is a lookup to the "Departments" table.
Aim: two combo boxes in a form, where the Department selection filters the possible selections for Specialty.

In the form, the RecordSource is the Specialties table, but the bound Department combo box has its ControlSource as DepartmentID and its RowSource as

SELECT Departments.ID, Departments.DepartmentName
FROM Departments;

The AfterUpdate event procedure for the Department combo box is:

Private Sub DepartmentID_AfterUpdate()
   On Error Resume Next
   Specialty.RowSource = "Select Specialties.SpecialtyName " & _
            "FROM Specialties " & _
            "WHERE Specialties.DepartmentID = '" & DepartmentID.Value & "' " & _
            "ORDER BY SpecialtyName;"
 
 
End Sub
 

When I go back to form view, I get a Department combo box with data, but no data in the Specialty combo. I also get the number of records equal to the number of specialties that are currently in the database.

What am I missing?

Thanks again for your help
Toi

[Stewart Ross]

Hi. Is your DepartmentID field numeric or text? You are currently treating it as text in your where clause for the specialty row source by enclosing the departmentID value in single quotes. If it is a number you refer to it without single quotes.

"WHERE Specialties.DepartmentID = " & DepartmentID

The use of the explicit .value method to access the value of a control is unnecessary, as .value is the default for all control references.

-Stewart

ps Unless you have a default rowsource set for your specialty combo it will not be filled at all until a department is selected from the department combo. The After Update event of the department combo is only fired when a selection is made from the list.

[Toireasa]

@Stewart Ross Inverness
Thanks for your help Stewart - DepartmentID is a Number lookup, so I've made both these changes. No difference, unfortunately.

ps Unless you have a default rowsource set for your specialty combo it will not be filled at all until a department is selected from the department combo. The After Update event of the department combo is only fired when a selection is made from the list.

Exactly - what I'm expecting is that when I select an option from Department in a new record, the Specialty field is populated with a filtered list. That's not happening at the moment, and I can't work out why! My code is now this:

Private Sub DepartmentID_AfterUpdate()
   On Error Resume Next
   Specialty.RowSource = "Select Specialties.SpecialtyName " & _
            "FROM Specialties " & _
            "WHERE Specialties.DepartmentID = " & DepartmentID & " " & _
            "ORDER BY SpecialtyName;"
 
 
End Sub
 

[ChipR]

The problem is that your form is bound to the Specialties table. If I'm understanding what you want correctly, you would remove the record source from your form completely and just set the combo boxes.

[Stewart Ross]

If your form is bound to the specialties table this should not in itself stop the rowsource property of your combo box from creating a list - might well invalidate why you are doing it in the first place, but it would not prevent a list from showing in the combo box.

In my opinion you need to start eliminating potential sources of error systematically now. For a start, comment out the Where clause in your rowsource SQL then run the revised code to see whether the combo is filled or not. If it is, the where clause is causing the problem.

Commenting out the Where clause is a temporary measure for test purposes - any list produced would be of all records, not just the ones matching your chosen department ID. If the combo still shows no items in the list after removing the Where clause there is something more fundamental going on - you could try copying the SQL for the rowsource into a normal Access query and running that to see what happens.

In any event, you will have to track this one down systematically. You could use the breakpoint and single-step facilities of the VB editor to interrupt your code and walk it through one line at a time, checking the value of local variables as you go.

Cascading combos are not difficult to implement - but you do need to be sure of the SQL and the effects of that SQL at each stage, as any error (whether of logic or syntax) will result in non-functioning combos or run-time errors, as you are finding out the hard way.

-Stewart

[Toireasa]

Thanks for the help Stewart and Chip - I guess my main problem here is that I'm not 'sure of the SQL' as this is my first attempt at SQL programming! I'll try what you suggest and may be back here for more help if I work out what the problem is but can't solve it.

[RBtwinky]

I was struggling with this for two days before I realized that I needed to click the options bar at the top of the Access window (under the ribbon). If you don't want to have to click that every time, open the Trust Center and add the database's directory as a trusted location.

I couldn't figure out why my code matched everything I could find on the internet, but it wouldn't even run. Turned out Access was protecting me from myself.

[DBlearner]

Yea, Access is acting like an helicopter mother worried that we may strain our brain too much if we play with codes.