Results 1 to 5 of 5
  1. #1
    WD Tice is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2015
    Posts
    3

    Limit Report Records to records selected on subforms


    I have a form that has two subforms in a access database. This form is based on three tables where subform 1 uses master/child link to mainformID and subform2 uses master/child link to subform1ID. My forms works great open mainform select the ID needed it displays records in subform1 related to mainformID then select the record in subform1 to display the records in subform2 related to subform1ID. My dilemma I created a command button to print a report; using the same master/child links for the the three reports as the forms. The main and two sub reports are based on three queries with each query using one of the mainform tables, I use vba code that selects the mainformID to open report print preview all is well but the report opens and prints all records using the mainformID; how to do I filter the records to use the recordID selected from subform1 and then filter records selected from subform2?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The report filter is driven by the main report criteria.

    If you want to further restrict the subreport records will need a parameter in the subreport RecordSource that references the subform ID field.
    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
    WD Tice is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2015
    Posts
    3
    Ok I understand that. How do I tell the command button using vba to use the record/records selected from each subform to use that as criteria for the underlying query? Give me some pointers I understand [forms].[mainformname].[subform]![field] but how do I use that to specify parameter for each subreport?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The button just opens the report with filter for the main report and has nothing to do with filter for subreports.

    The subreport RecordSource would require parameter that references the subform, something like:

    SELECT * FROM tablename WHERE ID = Forms!formname!subformcontainername.Form.Controlna me

    or

    SELECT * FROM tablename WHERE ID = Forms!formname.subformcontainername!fieldname

    Note that the reference syntax includes the name of the subform container, not the form in the container.

    I always name subform container different from the object it holds, like: ctrDetails
    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
    WD Tice is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2015
    Posts
    3
    Great that is what I needed. A little direction makes a big difference, Kind Regards.

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

Similar Threads

  1. Replies: 9
    Last Post: 08-19-2014, 12:41 PM
  2. Limit records on a page of a report
    By bgarey in forum Reports
    Replies: 4
    Last Post: 02-16-2012, 01:42 PM
  3. Limit number of records in report
    By aksnell in forum Reports
    Replies: 3
    Last Post: 12-05-2011, 02:31 PM
  4. Replies: 2
    Last Post: 09-18-2010, 07:52 AM
  5. Limit number of records in report by group
    By Dr Ennui in forum Reports
    Replies: 0
    Last Post: 06-22-2010, 12:36 AM

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