You are here:

Using MS Access/Return to Form from Report

Advertisement


Question
QUESTION: Hi Scott,
I'm a beginner using Access 2003. I'm executing a report for viewing that opens a form with text boxes with various query criteria that executes a query that gives me a report.  This works fine. What I'd like is after the report is displayed to be able to go back to the form to edit the criteria the re-query and display another updated report.  Right now the only way this will happen is if I close the report and start it up again. What I'd like is continuously going back and forth between the form and report without having to close the report to update the form fields.  Is this possible?
Here's the report and form code I have so far:

Report:
Option Compare Database

Private Sub Report_Close()
  DoCmd.Close acForm, "MyForm"
End Sub

Private Sub Report_Open(Cancel As Integer)

  ' Set public variable to true to indicate that the report
  ' is in the Open event
  bInReportOpenEvent = True
  
  ' Travel Expense Dialog
  DoCmd.OpenForm "MyForm", , , , , acDialog

  ' Cancel Report if User Clicked the Cancel Button
  If IsLoaded("MyForm") = False Then Cancel = True

  ' Set public variable to false to indicate that the
  ' Open event is completed
  bInReportOpenEvent = False
End Sub

Form:

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
 If Not bInReportOpenEvent Then
'  If we're not called from the report
    MsgBox "For use from the Travel Query Only", vbOKOnly
    Cancel = True
 End If
Form_Open_Exit:
  Exit Sub
End Sub

Private Sub Run_Query_Click()
 Me.Visible = False
End Sub

Private Sub Cancel_Click()
 DoCmd.Close
End Sub

ANSWER: No its not possible. Though it might be possible with Access 2007 or later.

Access 2007 introduced an new view of Reports called Report View. Report View is interactive unlike Print Preview. So you might be able to place a button on a report that will requery the report.

With Access 2003, the report is rendered when opened and only then. So you need to close the report before you can see the results of changed criteria. You could, however, go back to the form, change the criteria, then, from a button on the form, close and reopen the report in one action.



Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Scott,  thanks for your quick response. Before moving on to 2010, I'd like to try your suggestion to add a command button on the form to close and reopen the report. I'm not sure where to go with the on_click from the new (reset) button on form or how to work that into my present form and report code. Any insight from you on this would be greatly appreciated.

Thanks and regards

Answer
Very Simple

DoCmd.Close acReport, "reportname"

DoCmd.OpenReport "Reportname" ...

So the user would adjust the criteria on the form, hit the button and the report closes and reopens. How long it takes to reopen depends on the query behind it.

If you need to specify any parameters to the OpenReport method, you can do so.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.