MS Access Which Event To Use With DoCmd.OpenReport Method

I am using the DoCmd.OpenReport Method in Access to automatically Print a report of a Receipt.

This is what I have chosen for the On Activate but it is obviously wrong....
'If the customer pays in full lets make the paid in full stamp appear.
If Me!txtCashTendered.value >= Me!txtTotalDue.value Then
 Me!imagePaidInFull.Visible = True
  Me!ImageBalanceDue.Visible = False
   Me!txtBalanceDue.Visible = False
Else
 Me!imagePaidInFull.Visible = False
  Me!ImageBalanceDue.Visible = True
   Me!txtBalanceDue.Visible = True
End If
Exit Sub

Open in new window


Basically if the customer pays in full a "Paid in Full Stamp Appears" Else a "Balance Due Stamp Appears"

But (What I think is the problem) The report is not getting activated by being sent to the printer automatically....

So which event is the best for this option? I have tried several... On Current, On Activate, On Load, On Open, On Page

Thanks for the help.
LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?
 
Dale FyeCommented:
Try using the report sections Format event for your code, not the Activate.  If Format doesn't work, try Paint.

Keep in mind, that you have Report and Preview views of a report.  With the Report view (default), there are no pages, and some of the events do not fire (don't remember which ones).
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Dustin,

Assuming problem is that report is not showing latest data?

you didn't show the DoCmd.OpenReport statement, but I suspect it is after this.  Reports show data that is SAVED.
if data may have changed, save the record. Test first to see if it needs to be saved (is 'dirty') before OpenReport
   if me.dirty=true then Me.Dirty = False 'save record if there are unsaved changes

Open in new window

happy new year, Dustin

have an awesome day,
crystal
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thanks Crystal but I have another form called frmCashPayment and it has a button. On click I have:
       Forms![frmNewOrdersGuestCheckout].Dirty = False
        DoCmd.OpenReport "RptqryGuestCheckoutReceipt"
         DoCmd.Close acForm, Me.Name
          DoCmd.Close acForm, "frmMakePayment"
           DoCmd.Close acForm, "frmNewOrdersGuestCheckout"

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Dustin,

after:
Forms![frmNewOrdersGuestCheckout].Dirty = False
perhaps you need it to happen NOW (since code is not running from behind that form)
try adding
   DoEvents

Open in new window

... trying to understand the problem: you changed data that is not showing up on report? yes/no
if no, then what is the problem?

have an awesome day,
crystal
0
 
Dustin StanleyEntrepreneurAuthor Commented:
ok as for the
'If the customer pays in full lets make the paid in full stamp appear.
If Me!txtCashTendered.value >= Me!txtTotalDue.value Then
 Me!imagePaidInFull.Visible = True
  Me!ImageBalanceDue.Visible = False
   Me!txtBalanceDue.Visible = False
Else
 Me!imagePaidInFull.Visible = False
  Me!ImageBalanceDue.Visible = True
   Me!txtBalanceDue.Visible = True
End If
Exit Sub

Open in new window


Which event in the report should I choose? On Current, On Activate, On Load, On Open, On Page, etc....

It works perfectly if I was to just open the report and view it then manually choose to print it. But not with the automatic printing using:
DoCmd.OpenReport "RptqryGuestCheckoutReceipt"

Open in new window


I have not changed data. I am just trying to make a 1 of 2 images on the report to show if This Then image1 visible True. and the 2nd image visibility would then be false. I hope this makes sense.

Untitled.png
0
 
PatHartmanCommented:
Which event in the report should I choose?
NONE.  Give the user the option of opening the report in print preview or print directly.  Use an OptionGroup.printOptions.JPG
There's more to the code than this but this should give you an idea of what to do.  You probably only need cases 2 and 3.  This is obviously over the top for what you need.  This form is driven by a table that lists all the available reports and arguments that are used to control them.
    Select Case frm.fraOutputTo
        Case 1  'open form
            If rs!DisplayType = "Report" Then   'some selections can only be displayed as reports
                DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
            Else
                stDocName = frm.txtFormToOpen
                frm.Visible = False
                DoCmd.OpenForm stDocName, , , , , acWindowNormal, frm.Name
            End If
        Case 2  'preview report
            If rs!DisplayType = "Form" Then 'some selections can only be displayed as forms
                stDocName = frm.txtFormToOpen
                frm.Visible = False
                DoCmd.OpenForm stDocName, , , , , acWindowNormal, frm.Name
            Else
                DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
            End If
        Case 3  'print report
            If rs!DisplayType = "Form" Then 'some selections can only be displayed as forms
                stDocName = frm.txtFormToOpen
                frm.Visible = False
                DoCmd.OpenForm stDocName, , , , , acWindowNormal, frm.Name
            Else
                DoCmd.OpenReport stDocName, acViewNormal, , stLinkCriteria
            End If
        Case 4  'export to Excel
            If IsNull(rs!ExportQuery) Then      '
                MsgBox "Export is not available for this report.", vbOKOnly + vbInformation
            Else
'                ThisDBPath = Left(CodeDb.Name, InStrRev(CodeDb.Name, "\"))
'                ExportFileName = ThisDBPath & Forms!frmLogin!cboAuditParmsID.Column(4)
                DoCmd.OpenForm "frmGetDirectory2", acNormal, , , , acDialog
                ExportFileName = Forms!frmLogin!txtPath
                If ExportFileName & "" = "" Then
                    MsgBox "Path name was not provided.  Export cancelled.", vbOKOnly + vbCritical
                    rs.Close
                    GoTo lstReports_DblClick_Exit
                End If
                ExportFileName = ExportFileName & "-" & rs!ExportFileName & "-" & Format(Date, "yymmdd") & ".XLS"
                If Dir(ExportFileName) <> "" Then Kill ExportFileName
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rs!ExportQuery, ExportFileName, True
                MsgBox "File Exported to ---> " & ExportFileName, vbOKOnly + vbInformation
            End If
        Case 5  ' pdf
                DoCmd.OpenForm "frmGetDirectory2", acNormal, , , , acDialog
                ExportFileName = Forms!frmLogin!txtPath
                If ExportFileName & "" = "" Then
                    MsgBox "Path name was not provided.  Export cancelled.", vbOKOnly + vbCritical
                    rs.Close
                    GoTo lstReports_DblClick_Exit
                End If
                ExportFileName = ExportFileName & "-" & rs!ExportFileName & "-" & Format(Date, "yymmdd") & ".pdf"
                If Dir(ExportFileName) <> "" Then Kill ExportFileName
                'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rs!ExportQuery, ExportFileName, True
                DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, ExportFileName, False
                MsgBox "File Exported to ---> " & ExportFileName, vbOKOnly + vbInformation
        Case Else   ' no output type selected
            MsgBox "Please select an output type.", vbOKOnly + vbInformation
            Exit Sub
    End Select

Open in new window

0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thank you Pat for th input. I have to leave work now but hopefully I get some time tomorrow to figure this out. But your comment makes me more confused. This report will always be a direct print.

The only problem I am having is when the report is directly printed it shows both images and the text box "imagePaidInFull","ImageBalanceDue", and "txtBalanceDue". (All 3 visible is true)

But if I view the report and then manually print it. It works perfectly. That is when my code below is in the reports on Activate Event......

'If the customer pays in full lets make the paid in full stamp appear.
If Me!txtCashTendered.value >= Me!txtTotalDue.value Then
 Me!imagePaidInFull.Visible = True
  Me!ImageBalanceDue.Visible = False
   Me!txtBalanceDue.Visible = False
Else
 Me!imagePaidInFull.Visible = False
  Me!ImageBalanceDue.Visible = True
   Me!txtBalanceDue.Visible = True
End If
Exit Sub

Open in new window

0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Dustin,

as Dale also mentioned, be sure that the default view of the report is Print Preview since you have code that Report View won 't execute.

> "But if I view the report and then manually print it. It works perfectly. "
you are probably using Print Preview and the code is executing.  It should execute when printing too.  

The code should be in the section Format event (property sheet calls this On Format)

Activate is the wrong event

have an awesome day,
crystal
0
 
Dustin StanleyEntrepreneurAuthor Commented:
I love you all! I read before about the "On Format" and I was 90% positive this is where it was supposed to go but in the design view of the reports property I could not find this  for nothing. I tried for hours.

But then as you said it is in the "Section" of the report. So as my images were in the Report Footer section...I clicked on that section and WHAM! there it was "Format" in the Report Footers Property Sheet. All is perfect now.

THANK YOU ALL!
0
 
Dale FyeCommented:
glad I could help.
0
 
PatHartmanCommented:
Sorry, I was confused.  I thought you were trying to print the report from within a report event and my suggestion was to make that decision prior to opening the report.  I see now that you were trying to make something visible or not visible which isn't actually the same as "print".  At least you have a solution.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
It's all good Pat! Thanks for trying to help!
0
 
PatHartmanCommented:
You're welcome.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Dustin ~ happy to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.