Set recordsource of a subform using code

Posted on 2011-05-03
Last Modified: 2012-06-27
I have a bunch of queries. Which one will be executed depends on the date range the user enters in a couple of textboxes on a form.  How would I modify the code below to set a SUBFORM's recordsource? The code below was used on another one of my projects where I set the FORM's recordsource.  This time, I need to set the SUBFORM's recordsource.  The subform's name is SUBFORM1. Thank you.

QueryNameForReport = QueryName

If (IsNull(ANYDATA)) Then GoTo NoDataMessage
   DoCmd.OpenForm "Form1"
   Set frm = Forms("Form1")
   With frm
  .RecordSource = QueryName   <------ (QueryName is a variable that is set to the name of the
                                                                 appropriate query based upon which date range a
                                                                 user selects)
Question by:dbfromnewjersey
    LVL 75

    Accepted Solution


    If (IsNull(ANYDATA)) Then GoTo NoDataMessage
       DoCmd.OpenForm "Form1"
       Set frm = Forms("Form1")
       With frm
          .SUBFORM1.Form.RecordSource = QueryName

    LVL 47

    Assisted Solution

    by:Dale Fye (Access MVP)

    After opening form1, you could just use:

    Forms("Form1").subFormControlName.Form.RecordSource = "QueryName"

    where "subFormControlName" is the name of the control, when viewed on Form1, not the "ControlSource" of the subform.

    Author Comment

    Thank you.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now