Results 1 to 11 of 11
  1. #1
    bwwhite is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    10

    Populate listbox based on another listbox selection without VBA

    Can I populate listbox B based on the selection of listbox A without using any VBA--using only queries/SQL? My reason for wanting to do this is that I want to have this database usable on SharePoint and I don't think SharePoint will let me run code. At least I know I can't run any Excel macros via Excel Web Access on SharePoint.

    Listbox A will display unique values of Field1 from Table1. Based on the selection in Listbox A, Listbox B will display corresponding records from Table1, Field2. Then, based on a selection from Listbox B, Textboxes C, D and E will display a single item from 3 different fields from Table2. Tables 1 & 2 are related via a one (Table2) to many (Table1) relationship.



    Again--can this be done without VBA code, using only queries?

    My Access is quite rusty these days, so thank you in advance for any help!

    Bruce

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can use the first method and do the requery with a macro.

    http://www.baldyweb.com/CascadingCombo.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    bwwhite is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    10
    Thanks, pbaldy! Sounds like what I want can't be done without some VBA coding. I'll have to give that a try and see if SharePoint allows it.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you miss this?

    Quote Originally Posted by pbaldy View Post
    You can use the first method and do the requery with a macro.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    bwwhite is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    10
    OK, I have switched my list boxes to combo boxes like in the example. I have the code like in the example. How do I get the first combobox to list only unique values? Also, whenever I make a selection from the first combo box, I get an input box as though I have created a parameterized query. Any ideas? Thanks!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The technique works with list or combo boxes. To get distinct values, either add DISTINCT to the SQL (or use the unique values property), or use a totals query with a GROUP BY clause.

    The parameter prompt is Access telling you it can't find something, so check your spelling, etc.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    bwwhite is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    10
    Thanks! I forgot [Forms]! preceding my forms name. That did it. I have a few more to link, so I may be back for questions. Thanks again!

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help, and welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    bwwhite is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    10
    More questions.

    1) I'm trying to add a 3rd combobox, but can't get it to work. I have 2 tables--Background & Expertise. Each have a "Name" field. In the Background table, the Name field is unique--only one record per name. In the Expertise field, there can be multiple entries per name (some people have more than one area of expertise. My first combobox lists all the unique areas of expertise from the Expertise table. Based on the selection made in that combobox, the 2nd combobox displays the names of all the people with the chosen expertise. My ultimate goal is to then allow the selection of one of the names and display other areas of expertise they may have in a 3rd combobox. In addition, I'd like to somehow display (textboxes, labels, ???) various background information (from the Background table) for the name chosen in the 2nd combobox.

    2) Can this all work with listboxes instead of comboboxes?

    Thanks again!

    Bruce

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    1) Not being able to see what you've tried, it's hard to say what may be wrong. This is one way to do the display, if the fields are in the row source:

    http://www.baldyweb.com/Autofill.htm

    2) As mentioned earlier, yes. If a listbox is multiselect however, VBA is required to get the selected items.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    bwwhite is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    10
    I followed your example for 2 cascading combo boxes, but can't figure out how to extend it to 3 boxes. I'll try to lay out the example in greater detail.

    Background table has fields for name, education and department. One record per name.
    Experts table has fields for name and expertise. There can be multiple records per name, as one person can have multiple areas of expertise.

    cbo1 selects distinct records from the expertise field of the Experts table. Selecting an expertise causes cbo2 to display the names from the Experts table associated with the selected expertise.

    What I want is that when a name is selected from cbo2 is for cbo3 (or text box or list box) to diplay other areas of expertise connected to that name from the Experts table. In addition, I'd like background information selected from the Background table based on the name selected. I'd like the department and the education of the individual selected from cbo2 to be displayed in separate text boxes.

    As I said, I have only been able to implement your cascading combo technique on the first 2 combo boxes. I have not figured out how to extend it to 3 boxes, nor how to modify it to use listboxes rather than combo boxes.

    Thanks!

    Bruce

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

Similar Threads

  1. Replies: 1
    Last Post: 09-10-2012, 11:21 PM
  2. Replies: 1
    Last Post: 07-26-2012, 11:45 AM
  3. Unable to populate textbox based on listbox
    By marcustofeles in forum Forms
    Replies: 21
    Last Post: 10-24-2011, 07:18 AM
  4. Replies: 1
    Last Post: 11-05-2010, 05:49 PM
  5. Replies: 2
    Last Post: 08-03-2010, 10:16 AM

Tags for this Thread

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