Results 1 to 6 of 6
  1. #1
    tylerh is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    1

    Use Macro to Set Row Source

    Hi All,



    I am new to Access and I am hoping that someone can help me with an issue. I have a Table with two fields named "Type" and "Category". Both fields are to be dropdown lists and I want to have it so that the value that is selected in the "Type" field will determine the values that show up in the "Category" field dropdown list.

    I know that I need to create a AfterUpdate macro that sets the Row Source for the "Category" field based on the value selected in the "Type" field. But I cannot figure out how to actually write the macro in Access 2016 so it does just that and I can't find any examples that illustrate how to do this. So I am hoping one of you folks might be kind enough to guide me in the right direction!

    A bit more info: there are 4 possible values that can be selected in the "Type" field, and each one of those have the data for the "Category" field list in separate tables.

    Let me know if any more info is needed.

    Thanks for all help!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    This is cascading or dependent combobox (or listbox). This is a common topic. Search form or web. There is a SetProperty method in macros but I only use VBA.
    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
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    You do not want to dynamically set a combo box's row source with a macro. That would be the wrong approach.

    The combo box 2's record set (row source) will be a query. That query in turn will have criteria from the preceding combo box 1.

  4. #4
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63
    first you have to tell accesss what types belong to what category, so make a table named types with a two fields 1) Types 2) Category
    IE
    TYPE Category
    record 1 bat baseball
    record 2 ball baseball
    record 3 pad football
    ect.
    from the info above your saying that the bat and ball are both members of the baseball category and the pad is a member of the football category

    Now create a form lets call it form 1 create a drop down combo box that will show you your list of [types]. save the form and close it.

    Now create a query with all of the fields from your table and make the criteria point back to the combo box form you created IE form1!types. and save it as Query 1 and close it.
    in other words your asking access to look at the combo box named types on form 1 and show you only the records that match that. the result is about to become you list for category

    so with that being said
    Now go back and open form 1 up again now create a drop down combo box from the Query you made called Query 1. have the field show the Category field.
    That should do it. well kind of.

    At this point you will have to re open the form every time you change the type field so it will re query for your list, and thats not good.

    So in the properties of the type field, add a requery macro in the on lost focus so that it will re query Query 1.
    this way if you change the type field it will look up the value and rerun the query for your new list.

  5. #5
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Paul has an example of code for cascading combo's here http://www.baldyweb.com/CascadingCombo.htm

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Type is a reserved word and should not be used in this manner
    http://allenbrowne.com/AppIssueBadWord.html#T
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 3
    Last Post: 04-27-2016, 01:25 PM
  2. Replies: 1
    Last Post: 01-13-2015, 01:33 PM
  3. Replies: 1
    Last Post: 11-28-2013, 10:03 PM
  4. Replies: 5
    Last Post: 09-18-2013, 09:15 PM
  5. Replies: 2
    Last Post: 05-05-2012, 02:34 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