Results 1 to 5 of 5
  1. #1
    B.Mathews is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2017
    Posts
    2

    Report from a subform that displays all records

    Hello,



    I'm new to Access, VBA, and databases in general, additionally sorry if this has been asked/answered I have searched and . I'm having issues with creating a report from a subform that has all the records on it. I have a combobox that filters the table by Subject (five categories) and would like a text box that further filters based on a user search. I have a report that works but only finds the selected record from the subform. Hope that make sense and any help is appreciated.

    Table - Riparian_Assessment
    Form - RA_Search
    SubForm - RA_Subform
    Report - Riparian Report


    Code under the button: Open Report

    Private Sub Open_Report_Click()

    stCriteria = "[ID] = " & Me![ID]
    DoCmd.OpenReport "Riparian Report", acViewPreview, , "ID=" & Me!RA_Subform.Form.ID

    End Sub

    Thanks

    Bill

  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,825
    Don't understand.

    If you don't want just the 'selected record from the subform' then don't reference the subform ID as criteria.

    Why do you build variable stCriteria then don't use it?
    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
    B.Mathews is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2017
    Posts
    2
    Alright,
    The report either finds either the first record or the selected record. I would like it to print each record from the subform. My thinking was that I needed to use the subform ID as the criteria for accomplishing this.

    stCriteria can be removed as it was an earlier attempt and I was putting it in the last argument of the DoCmd function, I have started just coding it in directly.

    Hopefully that clears things up?

    Bill

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Reports and forms are there to display data - ALWAYS get the data right before creating it. You have a record source for the report, get this query working first. This would entail applying the correct criteria, selecting which fields from which form to use in the WHERE part of the statement. Your form/subform is filtered on Subject from the main form and "a text box that further filters based on a user search". These two fields would be the criteria entered into the query. Then you can open the report direct from the form without having to worry about adding criteria in the open statement. Either way will work fine, however. the point is to get the data displaying correctly first.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Query could reference the two form controls as dynamic parameters. This should work but I never use dynamic parameterized queries. I prefer VBA to set the WHERE CONDITION of OpenForm and OpenReport commands.
    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.

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

Similar Threads

  1. Replies: 3
    Last Post: 06-29-2017, 03:02 PM
  2. Replies: 3
    Last Post: 01-09-2017, 10:56 PM
  3. Replies: 4
    Last Post: 06-12-2014, 03:06 PM
  4. Creating a subform which displays values from a table
    By marcvanderpeet12 in forum Access
    Replies: 1
    Last Post: 03-03-2014, 01:04 PM
  5. Report displays no records
    By windwardmi in forum Reports
    Replies: 5
    Last Post: 10-05-2010, 12:10 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