Results 1 to 14 of 14
  1. #1
    Gee is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Posts
    31

    Cascading List Boxes

    I do not understand what I am doing wrong with this code.


    It is supposed to work like this:
    Click on one or more from the Manu list, which determines what is on the Segu list and Modu list.

    Code:
    Private Sub ListManu_AfterUpdate()
    Me.ListSegu.RowSource = "SELECT Seg" & "FROM BAMVtbl" & "WHERE Manufacturer=" & Nz(Me.ListManu)
    Me.ListModu.RowSource = "SELECT Model" & "FROM BAMVtbl" & "WHERE Seg=" & Me.ListManu
    End Sub
    Thank you in advance for any help you can give me.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,524
    Need to requery the dependent comboboxes.

    Me.ListSegu.Requery
    Me.ListModu.Requery
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Gee is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Posts
    31
    Just stick those tow requeries on to the end?
    I'm lost on this one.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,524
    Yes. Could even put the SQL statements directly in the RowSource property of controls, like:
    SELECT Model FROM BAMVtbl WHERE Seg=
    [ListManu]

    You actually have more concatenation than necessary:
    Me.ListModu.RowSource = "SELECT Model FROM BAMVtbl WHERE Seg=" & Me.ListManu
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Gee is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Posts
    31
    I did make some mods, I only have the ListManu now, It contains the Manufacturer and Seg.
    I still can't figure out how to get it to work.
    What I need is:f
    If they click on a selection in ListManu, then ListModu should only show those Models whos record's Manufacturer and Seg fields match the data in ListManu.

    I tried puting that code into the RowSource, and it gave me a box to input the Model before the form would open and that's not what I need.
    I know I'm getting close...any other suggestions?

  6. #6
    Gee is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Posts
    31
    Code:
    Private Sub ListManu_AfterUpdate()
    Me.ListModu.RowSource = "SELECT Model FROM BAMVtbl WHERE Seg & Manufacturer=" & Me.ListManu
    Me.ListModu.Requery
    End Sub

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,524
    That sql syntax is wrong.

    Show the sql for ListManu RowSource. Is there a unique ID field?

    Review http://datapigtechnologies.com/flash...combobox2.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Gee is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Posts
    31
    SELECT DISTINCT [dbo_BAMVtbl].[Seg], [dbo_BAMVtbl].[Manufacturer] FROM dbo_BAMVtbl ORDER BY [Seg], [Manufacturer];

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,524
    So there is no unqiue ID field in the ListManu combobox RowSource. This complicates.

    Are Seg and Manufacturer text or number fields? If text use apostrophe delimiters.

    Try:
    Me.ListModu.RowSource = "SELECT Model FROM BAMVtbl WHERE Seg='" & Me.ListManu.Column(0) & "' AND Manufacturer='" & Me.ListManu.Column(1) & "'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Gee is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Posts
    31
    No, that didn't work.
    The problem isnt' really in the row sources....it's in the AfterUpdate.
    The user clicks on the row in ListManu that contains the Seg and Manufacturer they want, and ListModu is supposed to populate (narrow down the list) with the records that match...Seg and Manufacturer to Model.
    Before I do anything both List Boxes are populated correctly, but when I click on a selection in ListManu, ListModu doesn't narrow down.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,524
    It should work.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Gee is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Posts
    31
    I wish I could but I can't. It's proprietary data ... even the names of the fields have been changed to protect my employment.
    I put that into the ListModu Rowsource and it said the record source was mispelled etc.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,524
    Don't care about data, structure is the issue. If you can't make copy and scrub or delete data or extract relevent objects to another file, can't help because I don't know your database. If spelling is wrong then need to figure out correct spelling. You have example of the technique, need to apply to your situation.

    I will be unable to work with database files for two weeks starting tomorrow.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Gee is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Posts
    31
    Thank you for trying.

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

Similar Threads

  1. Cascading Combo / List Boxes
    By plowe in forum Programming
    Replies: 5
    Last Post: 09-07-2012, 10:55 AM
  2. Sum of Cascading Combo Boxes
    By alonewolf23 in forum Forms
    Replies: 2
    Last Post: 11-20-2011, 02:10 PM
  3. Cascading combo boxes
    By combine21 in forum Forms
    Replies: 3
    Last Post: 12-02-2010, 12:57 PM
  4. Cascading Combo Boxes
    By desireemm1 in forum Programming
    Replies: 1
    Last Post: 10-05-2009, 06:00 AM
  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
  •  
Other Forums: Microsoft Office Forums - Senior Forums