Results 1 to 15 of 15

SAMPLE Cascading Combo Boxes

  1. #1
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863

    SAMPLE Cascading Combo Boxes

    CascadeCombo.zip




    This DB has an example of three cascading comboboxes that are dependent on one another. There are examples of how to change the combo box's Row Source using VBA. Using a basic SQL statement in afterupdate events, the Row Source is updated. Other properties are updated as needed via VBA when the user interacts with the form.

    Because there are three combo boxes, there are several diferent combinations the user has to choose before requesting a final output result.

    If you are looking for some examples of dependent cascading combo boxes, take a look. Please feel free to comment or ask questions.

  2. #2
    JoeyB is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2013
    Location
    The Netherlands / Australia
    Posts
    54
    Responding to your reply on my post:

    This looks something I would be looking for. After a quick look through the desgin view(of the queries) and SQL of the dropdown boxes, I cant figure out how you for example make them appear out of nowhere, and the information in the dropdown boxes is filtered down to the previous information in the previous dropdownbox.

    I hope you can help me with this, would mean a breakthrough!

    Cheers!

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    You can start by commenting out all of the lines with .Visible in it. The reason I hide them is they are dependent. They can not use the SQL for the rowsource until after the other comboboxes have a value.

  4. #4
    JoeyB is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2013
    Location
    The Netherlands / Australia
    Posts
    54
    Quote Originally Posted by ItsMe View Post
    You can start by commenting out all of the lines with .Visible in it. The reason I hide them is they are dependent. They can not use the SQL for the rowsource until after the other comboboxes have a value.
    I found out that they also wont appear when in after update [Even Procedure] is not mentioned.

    But how does Access know in for example BOL combobox know what data to display? I reckon you need some kind of relation ship between them? Is this relationship established during Combobox wizard, or in the queries?

    Many thanks!

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    Are you looking in the VBA module? This is where both cmbPO and cmbCont get their RowSource. The SQL strings are in the afterupdate events of the comboboxes. The SQL selects fields from Access query objects, the queries with names. This is how the comboboxes are able to get the calculated count values along with the other fields from the tables. All of the relationships are managed by the saved query objects within Access.

    The trick is the criteria within the WHERE clause in the VBA module, the After Update event handler. It uses the value of the other combobox.

    The first combobox was built using the wizard and all of its properties are described in its properties window. Go to the property window for the BOL combo, cmbCont, and look at its Row Source. Go ahead and delete the Row Source from the property window. It does not need it because it gets it from the VBA module.

  6. #6
    JoeyB is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2013
    Location
    The Netherlands / Australia
    Posts
    54
    Thank you. Been reading through the "code" and pretty much makes all sense to me. I have done this before, but it has been some time ago.

  7. #7
    JoeyB is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2013
    Location
    The Netherlands / Australia
    Posts
    54
    After some studying of the VBA, I think I have found a "mistake".

    I reckon the following peace of text is redundant.

    If Not IsNull(Me.cmbCont) Then

    lngBOL = Me.cmbCont.Column(1)
    lngUnits = Me.cmbCont.Column(2)

    strMsg = strMsg + " " & vbCrLf
    strMsg = strMsg + "Bill of Lading # " & lngBOL & " has " & vbCrLf
    strMsg = strMsg & lngUnits & " units remaining in inventory. "

    Because in the last one of message has the same constraint, and that is what Access uses eventually.

    Hope you understand what I am talking about, still a bit novice with this

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    The if then else statements are strategically placed to capture any event or combination of events the user may throw their way. It is possible I may have created one too many If Then statements. If I recall, there were many probabilities that required code that looked similar but is not, simply because of its placement within the VBA.

    Not sure though. I do recall being rather tired when I was debugging that sample and those If Thens were some of the last code I wrote.

  9. #9
    JoeyB is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2013
    Location
    The Netherlands / Australia
    Posts
    54
    I reckon the last argument should have both Me.cmbCont and Me.cmbPO in it. Is there a way of doing this?

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    You are right. There is an error in the code. Try replacing the If Then Else that is just previous to the code you posted here. This code will exit the sub and skip the code you posted. See if this works...


    Code:
    If Not IsNull(Me.cmbPO) Then
    strPO = Me.cmbPO.Column(1)
    lngUnits = Me.cmbPO.Column(2)
    strMsg = strMsg + " " & vbCrLf
    strMsg = strMsg + "PO # " & strPO & " has " & vbCrLf
    strMsg = strMsg & lngUnits & " units remaining in inventory.      "
    
        If Not IsNull(Me.cmbCont) Then
        
        lngBOL = Me.cmbCont.Column(1)
        lngUnits = Me.cmbCont.Column(2)
        
        strMsg = strMsg + " " & vbCrLf
        strMsg = strMsg + "Bill of Lading # " & lngBOL & " has " & vbCrLf
        strMsg = strMsg & lngUnits & " units remaining in inventory.      "
        
        End If
    MsgBox strMsg, vbInformation, "Here is your answer."
    Exit Sub
    End If

  11. #11
    JoeyB is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2013
    Location
    The Netherlands / Australia
    Posts
    54
    Thank you, unfortunately my time for today is up, will have an extensive look at it tomorrow

  12. #12
    JoeyB is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2013
    Location
    The Netherlands / Australia
    Posts
    54
    Cant make it work, could you amend it and post it again?

    Thank you

  13. #13
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    I think this one is doing what it is supposed to. I added the value of strPO in this revision.

    You can visualize which If Then is firing by placing additional text within this line
    strMsg = strMsg + " " & vbCrLf
    Maybe something like
    strMsg = strMsg + "MSG Option 1" & vbCrLf
    Attached Files Attached Files

  14. #14
    JoeyB is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2013
    Location
    The Netherlands / Australia
    Posts
    54
    Click image for larger version. 

Name:	Capture.PNG 
Views:	191 
Size:	25.5 KB 
ID:	14604
    This is what I get when I select all of the comboboxes. Clearly you can see there are 5 sentences, but in the VBA there are only 4. Is there something going wrong or is it working as you expected?

    Same goes for when you dont select an PO.

    Thanks

  15. #15
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    You can change the message around if you like. There is the possibility that a PO can have more units than a BOL. A PO can have more than one BOL. The message you are seeing is telling you that there is only one unit in the PO and only one unit in the BOL. This is because there is only one BOL left in inventory for that PO. This single BOL for that specific PO only has one unit left.

    If there is more than one BOL then there will be a higher unit count for the PO than the BOL. If you add some text in the Msg strings, maybe Msg One, Msg Two, Msg Three, you will see how and when the messages get displayed.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. cascading Combo boxes
    By Chatholo in forum Forms
    Replies: 2
    Last Post: 08-09-2013, 01:39 AM
  2. Cascading combo boxes
    By Jackie in forum Access
    Replies: 5
    Last Post: 07-26-2013, 09:07 AM
  3. Sum of Cascading Combo Boxes
    By alonewolf23 in forum Forms
    Replies: 2
    Last Post: 11-20-2011, 02:10 PM
  4. Cascading combo boxes
    By combine21 in forum Forms
    Replies: 3
    Last Post: 12-02-2010, 12:57 PM
  5. Cascading Combo Boxes
    By gjw1012 in forum Access
    Replies: 1
    Last Post: 07-25-2009, 04:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums