473,686 Members | 2,131 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Cascading to a third combo box.... on a form

kcdoell
230 New Member
I have three tables:

One for the Division location:

tblDivision
DivisionID = Autonumber
DivisionName = Text

One for the Working Region:
tblWrkRegion
WrkRegID = Autonumber
WrkRegionName = Text
DivisionID = Number (This is for my one to many relationship; a Division can have many different working regions)

One for the Credit Region:
tblCreditRegion
CreditRegID = Autonumber
CreditRegion = Text
WrkRegID = Number (This is for my one to many relationship; a working region can have many different credit regions)

I followed the cascading script method posted on this website and created a form, put 3 combo boxes on it:

cboDivision (bound on column 1, row source tblDivision, column count set to 2)
cboWrkReg (bound on column 1, row source blank, column count set to 1)
cboCreditReg (bound on column 1, row source blank, column count set to 1)


In the after update event of my cboDivision I placed the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CboDivision_AfterUpdate()
  2.  
  3. 'When the Division is selected, the appropriate Working Region list will
  4. 'display in the drop down list of CboWrkReg
  5.  
  6.     With Me![CboWrkReg]
  7.       If IsNull(Me!CboDivision) Then
  8.         .RowSource = ""
  9.       Else
  10.         .RowSource = "SELECT [WrkRegionName] " & _
  11.                      "FROM TblWrkRegion " & _
  12.                      "WHERE [DivisionID]=" & Me!CboDivision
  13.       End If
  14.      Call .Requery
  15. End With
  16.  
  17. End Sub
  18.  

This worked great. The Divisions would populate and when I clicked on cboWrkReg the working region list would be there. So….. I needed my last combo box to act and behave in the same way so In the after update event of my cboWrkReg I placed the following code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cboWrkReg_AfterUpdate()
  3. 'When the Working Region is selected, the appropriate Credit Region list will
  4. 'display in the drop down list of CboCredit Reg
  5.  
  6.     With Me![CboCreditReg]
  7.       If IsNull(Me!CboWrkReg) Then
  8.         .RowSource = ""
  9.       Else
  10.         .RowSource = "SELECT [CreditRegion] " & _
  11.                      "FROM TblCreditRegion " & _
  12.                      "WHERE [WrkRegID]=" & Me!CboWrkReg
  13.       End If
  14.      Call .Requery
  15. End With
  16. End Sub
  17.  
My problem is that when I click on the cboCreditReg I get a pop up dialogue box that is asking for me to input a number for the selection that was made in cboWrkReg. I have noticed that if I selected Atlanta in cboWrkReg; the row source is set to the following: SELECT [CreditRegion] FROM TblCreditRegion WHERE [WrkRegID]=Atlanta

If I key in the ID number for Atlanta the drop down box (cboCreditReg) will populate with the appropriate list. If I do nothing it will be blank or give me a syntax error message.

I have tried several things like changing bound columns with no luck. Does anybody have any idea where I am going wrong? I thought I solved this issue before I introduced a third cascading combo box.

Any help would be greatly appreciated.

Thanks,
Mar 11 '08 #1
7 2541
ADezii
8,834 Recognized Expert Expert
I have three tables:

One for the Division location:

tblDivision
DivisionID = Autonumber
DivisionName = Text

One for the Working Region:
tblWrkRegion
WrkRegID = Autonumber
WrkRegionName = Text
DivisionID = Number (This is for my one to many relationship; a Division can have many different working regions)

One for the Credit Region:
tblCreditRegion
CreditRegID = Autonumber
CreditRegion = Text
WrkRegID = Number (This is for my one to many relationship; a working region can have many different credit regions)

I followed the cascading script method posted on this website and created a form, put 3 combo boxes on it:

cboDivision (bound on column 1, row source tblDivision, column count set to 2)
cboWrkReg (bound on column 1, row source blank, column count set to 1)
cboCreditReg (bound on column 1, row source blank, column count set to 1)


In the after update event of my cboDivision I placed the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CboDivision_AfterUpdate()
  2.  
  3. 'When the Division is selected, the appropriate Working Region list will
  4. 'display in the drop down list of CboWrkReg
  5.  
  6.     With Me![CboWrkReg]
  7.       If IsNull(Me!CboDivision) Then
  8.         .RowSource = ""
  9.       Else
  10.         .RowSource = "SELECT [WrkRegionName] " & _
  11.                      "FROM TblWrkRegion " & _
  12.                      "WHERE [DivisionID]=" & Me!CboDivision
  13.       End If
  14.      Call .Requery
  15. End With
  16.  
  17. End Sub
  18.  

This worked great. The Divisions would populate and when I clicked on cboWrkReg the working region list would be there. So….. I needed my last combo box to act and behave in the same way so In the after update event of my cboWrkReg I placed the following code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cboWrkReg_AfterUpdate()
  3. 'When the Working Region is selected, the appropriate Credit Region list will
  4. 'display in the drop down list of CboCredit Reg
  5.  
  6.     With Me![CboCreditReg]
  7.       If IsNull(Me!CboWrkReg) Then
  8.         .RowSource = ""
  9.       Else
  10.         .RowSource = "SELECT [CreditRegion] " & _
  11.                      "FROM TblCreditRegion " & _
  12.                      "WHERE [WrkRegID]=" & Me!CboWrkReg
  13.       End If
  14.      Call .Requery
  15. End With
  16. End Sub
  17.  
My problem is that when I click on the cboCreditReg I get a pop up dialogue box that is asking for me to input a number for the selection that was made in cboWrkReg. I have noticed that if I selected Atlanta in cboWrkReg; the row source is set to the following: SELECT [CreditRegion] FROM TblCreditRegion WHERE [WrkRegID]=Atlanta

If I key in the ID number for Atlanta the drop down box (cboCreditReg) will populate with the appropriate list. If I do nothing it will be blank or give me a syntax error message.

I have tried several things like changing bound columns with no luck. Does anybody have any idea where I am going wrong? I thought I solved this issue before I introduced a third cascading combo box.

Any help would be greatly appreciated.

Thanks,
I think I see the problem in that Me![cboWrkReg] returns the Work region Name and Not the Work Region ID, try:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtSendKeys_GotFocus()
  2. Private Sub cboWrkReg_AfterUpdate()
  3.   With Me![CboCreditReg]
  4.     If IsNull(Me!CboWrkReg) Then
  5.       .RowSource = ""
  6.     Else
  7.       .RowSource = "SELECT [CreditRegion] FROM TblCreditRegion WHERE [WrkRegID]=" & _
  8.                     DLookup("[WrkRegID]", "tblWrkRegion", "[WrkRegionName] = '" & Me![cboWrkReg] & "'")
  9.     End If
  10.     Call .Requery
  11.   End With
  12. End Sub
  13. End Sub
Mar 11 '08 #2
kcdoell
230 New Member
Unbelievable, that was it. I really want to thank you. This problem was driving me crazy and I could not get myself out of this hole until you came around.

Thanks a million!

Keith.
Mar 11 '08 #3
ADezii
8,834 Recognized Expert Expert
Unbelievable, that was it. I really want to thank you. This problem was driving me crazy and I could not get myself out of this hole until you came around.

Thanks a million!

Keith.
You are quite welcome, Keith.
Mar 11 '08 #4
kcdoell
230 New Member
You are quite welcome, Keith.
ADezii:

I am not too sure you will get this message but one of my users has asked me to do something else with the cascading combo box that I thought you may have some insight...

Basically, everything works great but the list in the third box only displays when the user clicks into the combo box. In some cases, the third combo box may only have one choice to choose from. Is there a way that I can get the first on the list to display without the user having to click the third combo box to see visually the dropdown list/choices to pick?

Hope all is well,

Keith.
Apr 24 '08 #5
ADezii
8,834 Recognized Expert Expert
ADezii:

I am not too sure you will get this message but one of my users has asked me to do something else with the cascading combo box that I thought you may have some insight...

Basically, everything works great but the list in the third box only displays when the user clicks into the combo box. In some cases, the third combo box may only have one choice to choose from. Is there a way that I can get the first on the list to display without the user having to click the third combo box to see visually the dropdown list/choices to pick?

Hope all is well,

Keith.
Assuming the name of the 3rd Combo Box is cboThree (ingenious name, heh), place the following code in the AfterUpdate() Event of cboTwo, after the Row Source for cboThree has been created. If cboThree contains at least 1 entry, then the 1st entry in cboThree (1st Column, 1st Row) will be visible in the Text protion of the Combo Box. Is this what you are requesting?
Expand|Select|Wrap|Line Numbers
  1. If Me![cboThree].ListCount > 0 Then
  2.   Me![cboThree] = Me![cboThree].Column(0, 0)
  3. End If
Apr 24 '08 #6
kcdoell
230 New Member
Is this what you are requesting?
Expand|Select|Wrap|Line Numbers
  1. If Me![cboThree].ListCount > 0 Then
  2.   Me![cboThree] = Me![cboThree].Column(0, 0)
  3. End If
ADezii:

That was exactly what I was looking for! You were spot on!! Here was my end solution:

Expand|Select|Wrap|Line Numbers
  1.   With Me![cboCreditReg]
  2.    If IsNull(Me!cboWrkReg) Then
  3.       .RowSource = ""
  4.  
  5.     Else
  6.    .RowSource = "SELECT DISTINCT tblCreditRegion.CreditRegID, " & _
  7.          "tblCreditRegion.CreditRegionName " & _
  8.          "FROM TblLocationsMM INNER JOIN tblCreditRegion " & _
  9.          "ON TblLocationsMM.CreditRegIDFK = tblCreditRegion.CreditRegID " & _
  10.          "WHERE [WrkRegIDFK]=" & Me!cboWrkReg
  11.  
  12.     End If
  13.  
  14.     Call .Requery
  15.  
  16.     'Have the first dropdown list of cboCreditReg visible
  17.             If Me![cboCreditReg].ListCount > 0 Then
  18.                 Me![cboCreditReg] = Me![cboCreditReg].Column(0, 0)
  19.     End If
  20.       End With
  21.  
Thanks a million!

Keith.
Apr 24 '08 #7
ADezii
8,834 Recognized Expert Expert
ADezii:

That was exactly what I was looking for! You were spot on!! Here was my end solution:

Expand|Select|Wrap|Line Numbers
  1.   With Me![cboCreditReg]
  2.    If IsNull(Me!cboWrkReg) Then
  3.       .RowSource = ""
  4.  
  5.     Else
  6.    .RowSource = "SELECT DISTINCT tblCreditRegion.CreditRegID, " & _
  7.          "tblCreditRegion.CreditRegionName " & _
  8.          "FROM TblLocationsMM INNER JOIN tblCreditRegion " & _
  9.          "ON TblLocationsMM.CreditRegIDFK = tblCreditRegion.CreditRegID " & _
  10.          "WHERE [WrkRegIDFK]=" & Me!cboWrkReg
  11.  
  12.     End If
  13.  
  14.     Call .Requery
  15.  
  16.     'Have the first dropdown list of cboCreditReg visible
  17.             If Me![cboCreditReg].ListCount > 0 Then
  18.                 Me![cboCreditReg] = Me![cboCreditReg].Column(0, 0)
  19.     End If
  20.       End With
  21.  
Thanks a million!

Keith.
You are quite welcome, Keith.
Apr 25 '08 #8

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

Similar topics

9
6756
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 second combobox FOR EVERY RECORD goes blank.
6
2084
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 depending on which Surname they chose. I then have a command button which produces the results of the...
4
64603
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: There are other types of row sources that can be used but for simplicity we will stick with Tables...
3
3697
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
3487
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
2670
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
3980
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
7
5833
by: Toireasa | last post by:
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...
0
8585
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 usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8517
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9057
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8934
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8770
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7603
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4309
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4534
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2208
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.