Results 1 to 10 of 10

reports to forms to queries

  1. #1
    aaa1 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    4

    reports to forms to queries

    I am a newbie to access.
    I want the report to print out the results after taking requests from the form and then to a query and then to the report. Is that possible?
    Or a from to a query and then to a report directly?

    Please let me know on this..
    Thanks.

  2. #2
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    30,811
    Don't really understand your description.

    Reports can be filtered by criteria entered on a form. There is more than one way to accomplish this. One is by input parameters in the query the report is based on. The input parameters would be references to controls on the form.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    aaa1 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    4
    Basically I need to output results on the report based on criteria on the form.

    In this process my control first goes to the report.Report redirects to the form after making itself hidden. The form then takes the criteria input. This criteria is transferred to a Query and the report, which is initially based on the query.

    I am actually stuck at the Openform method which is part of the Onload method of report.What have to be the Openargs, as i am not sending any parameters to the form from the report. I am getting an error at the Openform, mainly due to no Openargs present.

    Is this way correct.Please advice me on the code to be put in the report Onload method.

    Thanks

  4. #4
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    30,811
    I still don't understand what you are trying to do. Why open report first and what is accomplished by then directing focus to the form, form takes criteria input from where? I have never done anything like this. I have never opened a form from a report. I consider a report the culmination of a process, not an intermediate step. If I can't understand what you are doing, I can't advise on code to use.
    Last edited by June7; 08-10-2011 at 01:28 PM.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    aaa1 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    4
    Sorry for not helping you understand.
    I have a form that takes in a required data from fields. Queries are triggered based on this and the result from the queries have to be displayed on the forms.

    I heard that its impossible to display the results out on the report in this manner.

    Please let me know if you understood the scenario.

    Thanks.

  6. #6
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    30,811
    Not sure I understand. What do you mean by 'trigger' a query?

    Reports are bound to tables or queries to produce desired output. No reason to 'trigger' a query. Open the report that has query as its RecordSource. Queries can refer to controls on form as input parameters.

    Report RecordSource can be a saved query object or use the query designer right in the report's RecordSource property to construct the SQL statement.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  7. #7
    aaa1 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    4
    So after clicking on the submit button in the form how do we output the results onto the report?

    Can you please advise me with some code on this action?

  8. #8
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    30,811
    Code (macro or VBA) is one way to send filter criteria to the report when it opens. Uses arguments of DoCmd.OpenReport

    Another way is to have the query refer to the controls on the form as input parameters, as long as the form remains open.

    In the query design view, in the criteria row enter reference to the form control, like: Forms!formname!controlname.

    Now use this query as the RecordSource for report.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  9. #9
    randman1 is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2009
    Posts
    25
    Your report cannot not open a form to collect criteria then feed that criteria back to the report. You simply use the form to collect the criteria, click Submit, then open the report by passing the criteria in the DoCmd.OpenReport method.

    One example might be a report to show employee information. The report's RecordSource will be a query like:
    Code:
    SELECT EmpFirstName, EmpLastName, Dept, HireDate FROM tblEmployees
    If you were to open this report from the DB window, it would display all records in the tblEmployee table. Now let's say that you have a form with three textboxes: txtDept, txtStartDate and txtEndDate. The user fills in the the textboxes with the appropriate information and clicks the Submit button. The code behind the Submit button would look like:
    Code:
    Private Sub cmdSubmit_Click
       Dim strWhere as String
       strWhere = "Dept = " & Me.txtDept & " And HireDate Between #" &  Me.txtStartDate & "# And #" & Me.txtEndDate & "#"
       DoCmd.OpenReport "rptEmployees", acViewReport, , strWhere
    End Sub
    This will now open the same report as before but rather than displaying all records in the tblEmployees table, it only displays the ones from the specified department and having a hire date between the two dates specified on the form.

  10. #10
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    30,811
    Good examples randman.

    Here is another recent thread on this same issue http://www.accessforums.net/queries/...ort-16039.html
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

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

Similar Threads

  1. 2010 and 2007 Access Queries, Forms & Reports
    By rpaldridge in forum Import/Export Data
    Replies: 3
    Last Post: 02-11-2011, 05:37 AM
  2. Splitting DB; Keeping the Forms, Queries, Reports in BE
    By evander in forum Database Design
    Replies: 2
    Last Post: 07-07-2010, 03:59 PM
  3. Queries, Forms, Reports..
    By groundhog in forum Access
    Replies: 3
    Last Post: 07-07-2010, 11:30 AM
  4. Using Public variables between forms/queries/reports
    By dcrake in forum Sample Databases
    Replies: 1
    Last Post: 02-11-2010, 09:10 PM
  5. Replies: 4
    Last Post: 04-01-2009, 07:49 PM

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
  •  
Tech Forums: Microsoft Office Forums