472,797 Members | 1,138 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,797 software developers and data experts.

Cascading Combo Box - Another angle on the subject.....

230 100+
I have 5 cascading combo boxes on a form. Below is a sample of my vb in the first combo box:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CboDivision_AfterUpdate()
  3. 'When the Division is selected, the appropriate Segment list will
  4. 'display in the drop down list of CboSegment
  6.   With Me![cboSegment]
  7.      If IsNull(Me!cboDivision) Then
  8.         .RowSource = ""
  9.       Else
  10.         .RowSource = "SELECT DISTINCT tblSegment.SegmentID, " & _
  11.          "tblSegment.SegmentName " & _
  12.          "FROM TblLocationsMM INNER JOIN tblSegment " & _
  13.         "ON TblLocationsMM.SegmentIDFK = tblSegment.SegmentID " & _
  14.          "WHERE [DivisionIDFK]=" & Me!cboDivision
  16.       End If
  17.      Call .Requery
  19.     'Have the first dropdown list of cboSegment visible
  20.             If Me![cboSegment].ListCount > 0 Then
  21.                 Me![cboSegment] = Me![cboSegment].Column(0, 0)
  23.     End If
  24.         End With
  26. End Sub 
I follow this method for the 4 other cascading combo boxes that are displayed on my form. Each selection adds an additional parameter to set the row source for the next combo box. I achieve this though my “Where” statement (See below):

Expand|Select|Wrap|Line Numbers
  1.  Private Sub cboSegment_AfterUpdate()
  3. 'First Make sure other combo boxes are null
  4. Me!cboWrkReg = Null
  5. Me!cboCreditReg = Null
  6. Me!cboBrokerType = Null
  8. 'When the Segment is selected, the appropriate Working Region list will
  9. 'display in the drop down list of CboWrkReg
  11. With Me![cboWrkReg]
  12.    If IsNull(Me!cboSegment) Then
  13.       .RowSource = ""
  15.     Else
  16.    .RowSource = "SELECT DISTINCT tblWrkRegion.WrkRegID, " & _
  17.          "tblWrkRegion.WrkRegionName " & _
  18.          "FROM TblLocationsMM INNER JOIN tblWrkRegion " & _
  19.          "ON TblLocationsMM.WrkRegIDFK = tblWrkRegion.WrkRegID " & _
  20.          "WHERE [DivisionIDFK]=" & Me!cboDivision & _
  21.          "And [SegmentIDFK]=" & Me!cboSegment
  23.     End If
  24.     Call .Requery
  26.     'Have the first dropdown list of cboWrkReg visible
  27.             If Me![cboWrkReg].ListCount > 0 Then
  28.                 Me![cboWrkReg] = Me![cboWrkReg].Column(0, 0)
  29.    End If
  30.       End With
  31. End Sub 
In the above example, the user would make a selection on my first combo box [cboDivision], now the second combo box [cboSegment] displays the first on the list based on the selection made in [cboDivision]. If the user does not actual click/select into [cboSegment] than my third combo box [cboWrkReg] is not going to have the correct drop down list.

If I do click/select [cboSegment] even though the first on the list is my choice the list choices in [cboWrkReg] are correct.

Now to my question, is there a simple code that I can put into my cboDivision_AfterUpdate that will not only display the first on the list but will trigger a click/select action so that the appropriate lists for subsequent combo boxes on the form will be correct?

I am thinking the solution I would apply to my other combo box AfterUpdate events but I am lost on how to solve...

Thanks for any ideas you may have.

Jun 17 '08 #1
3 3903
374 Expert 256MB
You can simply use the OnChange Event for each of your combo boxes, as you change them, then you can trigger the event that will update the next set of control(s) on your form.

Hope that helps,

Joe P.
Jun 17 '08 #2
230 100+
You can simply use the OnChange Event for each of your combo boxes, as you change them, then you can trigger the event that will update the next set of control(s) on your form.

Hope that helps,

Joe P.
So would I need the same coding in both the AfterUpdate and in the OnChange Event or eliminate the AfterUpdate coding and just simply move it to a OnChange event?

Thanks for the idea.

Jun 19 '08 #3
230 100+
In the end I used another technique by disabling the combo boxes (setfocus.enable = False), except for the first, until a selection had been made in the first.......................

I included a command button I called "Reset Selections" to give the user the ability to reset previous selections if they wanted to make other choices.

Jun 19 '08 #4

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

Similar topics

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 ...
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...
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...
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...
by: kcdoell | last post by:
I have three tables: One for the Division location: tblDivision DivisionID = Autonumber DivisionName = Text One for the Working Region: tblWrkRegion
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...
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...
by: 20028431 | last post by:
This is driving me mad! I have been trying for 3 days now to work out how to do this with no success. I have tables for Customer, Bookings, Entertainment, Act, Agent, and several others. I...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
How does React native implement an English player?
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

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.