473,562 Members | 2,650 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Difficulties with cascading combo boxes

6 New Member
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: "Specialtie s" and "Department s"
where "Specialtie s" includes a field (DepartmentsID) which is a lookup to the "Department s" 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
Expand|Select|Wrap|Line Numbers
  1. SELECT Departments.ID, Departments.DepartmentName
  2. FROM Departments;
The AfterUpdate event procedure for the Department combo box is:
Expand|Select|Wrap|Line Numbers
  1. Private Sub DepartmentID_AfterUpdate()
  2.    On Error Resume Next
  3.    Specialty.RowSource = "Select Specialties.SpecialtyName " & _
  4.             "FROM Specialties " & _
  5.             "WHERE Specialties.DepartmentID = '" & DepartmentID.Value & "' " & _
  6.             "ORDER BY SpecialtyName;"
  7.  
  8.  
  9. End Sub
  10.  
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
Nov 27 '08 #1
7 5820
Stewart Ross
2,545 Recognized Expert Moderator Specialist
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.

Expand|Select|Wrap|Line Numbers
  1. "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.
Nov 27 '08 #2
Toireasa
6 New Member
@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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub DepartmentID_AfterUpdate()
  2.    On Error Resume Next
  3.    Specialty.RowSource = "Select Specialties.SpecialtyName " & _
  4.             "FROM Specialties " & _
  5.             "WHERE Specialties.DepartmentID = " & DepartmentID & " " & _
  6.             "ORDER BY SpecialtyName;"
  7.  
  8.  
  9. End Sub
  10.  
Nov 28 '08 #3
ChipR
1,287 Recognized Expert Top Contributor
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.
Nov 28 '08 #4
Stewart Ross
2,545 Recognized Expert Moderator Specialist
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
Nov 28 '08 #5
Toireasa
6 New Member
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.
Dec 2 '08 #6
RBtwinky
1 New Member
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.
Dec 6 '09 #7
DBlearner
18 New Member
Yea, Access is acting like an helicopter mother worried that we may strain our brain too much if we play with codes.
Dec 8 '09 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

0
2060
by: cognoscento | last post by:
I'm currently putting together a database for my work (not an expert by any stretch, so muddling through as best as I can... you know the story...) and I could use some advice and hand-holding I've got a subform with a series of cascading combo boxes (thanks to the Access tutorials on fontstuff.com) that let the user assign categories to...
9
6747
by: Edwinah63 | last post by:
Hi everyone, Please let there be someone out there who can help. I have two BOUND combo boxes on a continuous form, the second being dependent on the first. I have no problem getting the second combo to change depending on what values the user selects in the first box, it's just that every time the user changes the first combobox, the...
6
2075
by: visionstate | last post by:
Hi there, I am building a database that requires cascading lists on a form. I currently have (I may be adding more later) 3 combo boxes on my form - Department, Surname and Forename. The user chooses the department they want and then the corresponding surnames from that department can be chosen from the Surname box and then the Forename...
4
64549
Rabbit
by: Rabbit | last post by:
Cascading Combo/List Boxes This tutorial is to guide you in the creation of Cascading combo/list boxes. That is when you have multiple combo/list boxes where the selection of an option in one determines the available options in the other. TERMINOLOGY Row Source: The table/query from which the Combo Box or List Box gets its values. Note:...
3
3688
by: buddyr | last post by:
Hello, Yesterday I recieved help with two cascading combo boxes on an access form. I went the link http://www.fontstuff.com/access/acctut10.htm And basically used their first example. Now I may need 3 cascading combo boxes and I cannot figure out how to do that. Can you help? Thank you
4
3472
klarae99
by: klarae99 | last post by:
Hello, I am working on an Access 2003 Database. The tables that pertain to this issue are tblOrg, tblState, tblCity, and tblZip. I have posted the table structure with only the pertinant fields below. tblOrg OrgID, AutoNumber, PK ZipID, Number, FK tblState StateID, AutoNumber, PK
1
2663
kcdoell
by: kcdoell | last post by:
Good Morning: I have a form where I am trying to create cascading combo boxes. I have done this before but I am getting the following error that is throwing me off: Procedure declaration does not match description of event or procedure having the same name. Basically have three tables: One for the Division location:
3
3973
kcdoell
by: kcdoell | last post by:
I have 5 cascading combo boxes on a form. Below is a sample of my vb in the first combo box: Private Sub CboDivision_AfterUpdate() 'When the Division is selected, the appropriate Segment list will 'display in the drop down list of CboSegment With Me! If IsNull(Me!cboDivision) Then
0
7655
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7869
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8101
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7935
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5477
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5193
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3623
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1191
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
903
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.