General discussion

Locked

Need an Access 97 VB Code Expert

By learneverything ·
In my report, I have an unbound field named "FooterText". When the
report is printed (even print previewed), I want it to
1. Print 3 collated copies
2.Change the Footer text on each:
first copy to "Sign and return with payment".
second copy to "Keep for your records".
third copy to "KDA copy".
3. Print the first 2 pages in color and the 3rd in monochrome. (I don't care if the print preview is color).

The code I have so far is below. I'm not getting any errors, but it isn't generating 3 copies for each record, so I don't know if the footertext will work or not.

Option Compare Database
Option Explicit
Dim Copies As Integer

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
DoCmd.PrintOut acPages, , , , 3, True

End Sub

Private Sub Report_Open(Cancel As Integer)
Copies = 1
DoCmd.Maximize
End Sub

Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
Select Case Copies

Case 1
[FooterText] = "SIGN AND RETURN WITH PAYMENT"
Case 2
[FooterText] = "KEEP FOR YOUR RECORDS"
Case 3
[FooterText] = "KDA COPY"
End Select
If Page = Pages Then Copies = Copies + 1
End Sub

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Need an Access 97 VB Code Expert

by Peyison In reply to Need an Access 97 VB Code ...

I think the problem you're going to have is that the PageFooter_Format event is only going to be called once - when the page is first rendered, not each time the page is printed. Also - you'll probably have problems calling DoCmd.PrintOut from a Print event.

Here's a way to do this that involves moving the code out of the report and into a form. Another advantage of this is the same code can be used for multiple reports, as long as each has the FooterText textbox - or you can build a switchfor those that don't.

To begin with, add the following Types to you form, or to a code module. If you add to a code module, they need to be Public instead of Private. These are used when setting the color of the output.

Private Type str_DEVMODE
RGB As String * 94
End Type

Private Type type_DEVMODE
strDeviceName As String * 16
intSpecVersion As Integer
intDriverVersion As Integer
intSize As Integer
intDriverExtra As Integer
lngFields As Long
intOrientation As Integer
intPaperSize As Integer
intPaperLength As Integer
intPaperWidth As Integer
intScale As Integer
intCopies As Integer
intDefaultSource As Integer
intPrintQuality As Integer
intColor As Integer
intDuplex As Integer
intResolution As Integer
intTTOption As Integer
intCollate As Integer
strFormName As String * 16
lngPad As Long
lngBits As Long
lngPW As Long
lngPH As Long
lngDFI As Long
lngDFr As Long
End Type

More to follow...

Collapse -

Need an Access 97 VB Code Expert

by Peyison In reply to Need an Access 97 VB Code ...

Next, add this Sub. Again, if added to a code module, it should be Public. This is the code to change the color of the output.

Private Sub SetPrintColor(asReportName As String, abColorOn As Boolean)
Dim DevString As str_DEVMODE
Dim DM As type_DEVMODE
Dim sDevModeExtra As String
Dim rpt As Report

'--this is the reverse of what is in the Access help file, but is what seems to work.
Const COLOR = 2
Const MONOCHROME = 1

DoCmd.OpenReport asReportName, acDesign

Set rpt= Reports(asReportName)
If Not IsNull(rpt.PrtDevMode) Then
sDevModeExtra = rpt.PrtDevMode
DevString.RGB = sDevModeExtra
LSet DM = DevString

If abColorOn Then
DM.intColor = COLOR
Else
DM.intColor = MONOCHROME
End If

LSet DevString = DM
Mid(sDevModeExtra, 1, 94) = DevString.RGB
rpt.PrtDevMode = sDevModeExtra
End If

End Sub

More to follow...

Collapse -

Need an Access 97 VB Code Expert

by Peyison In reply to Need an Access 97 VB Code ...

Here is the code that does the printing. It is for a button now, but can (and probably should) be a separate sub that is called from the button.

The only thing I'm not sure how to do, or if it can be done, is to put three copies of the reportin a Print Preview. I don't think the Print Preview is designed to be a view of what is actually send to the printer (e.g. three copies of the report), but more just a different mode of the report itself -i.e. just one copy.


Private Sub PrintIt_Click()
Dim sReportName As String
Dim iOutput As Integer

'--can get the report name from your form - e.g. from a listbox
sReportName = "rptReport"

DoCmd.OpenReport sReportName, acDesign
Reports(sReportName).Visible = False
DoEvents

'--the output type (1 or 2) can be set from option buttons on the form
iOutput = 2

Select Case iOutput
Case 1 'Preview
DoCmd.OpenReport sReportName, acPreview
Case 2 'Print
SetPrintColor sReportName, True DoCmd.OpenReport sReportName, acPreview
Reports(sReportName)("FooterText") = "SIGN AND RETURN WITH PAYMENT"
DoCmd.PrintOut acPrintAll, , , , 1, True

Reports(sReportName)("FooterText") = "KEEP FOR YOUR RECORDS"DoCmd.PrintOut acPrintAll, , , , 1, True

SetPrintColor sReportName, False
DoCmd.OpenReport sReportName, acPreview
Reports(sReportName)("FooterText") = "KDA COPY"
DoCmd.PrintOut acPrintAll, , , , 1, True

CloseReport sReportName
End Select

End Sub

Hope this helps.

Collapse -

Need an Access 97 VB Code Expert

by Peyison In reply to Need an Access 97 VB Code ...

One more Sub you'll need:

Sub CloseReport(asReportName As String)
DoCmd.SetWarnings False
DoCmd.Close A_REPORT, asReportName
DoCmd.SetWarnings True
End Sub

This will close the report without asking if you want to save the changesmade.

Also - you can modify the code that sets the print color to set the collate option - this will then allow you to just open the report in Normal mode to print it rather than using DoCmd.PrintOut.

To print it you'd use:
DoCmd.OpenReport sReportName, acNormal

Good luck.

Collapse -

Need an Access 97 VB Code Expert

by learneverything In reply to Need an Access 97 VB Code ...

Peyison, I thank you for your efforts. See the note I posted above.

The code I was using I got out of "Access Programming for Dummies". When I kept running into a wall, I e-mailed the author with a question. I never expected to hear anything,but he answered and sent me a sample database. The problem was that the code below needed to be in a seperate module, or attached to a form button instead of in the report itself.

Private Sub Command0_Click()
DoCmd.OpenReport "solicitation", acViewPreview
DoCmd.PrintOut acPrintAll, , , , 2, True
DoCmd.CLOSE acReport, "solicitation", acSaveNo

End Sub

I still can't believe he answered. I will buy every Access book Rob Krumm writes for the rest of our days. :-)

Collapse -

Need an Access 97 VB Code Expert

by learneverything In reply to Need an Access 97 VB Code ...

Good gravy, Peyison! This turned out to be a LOT more involved than I thought. I think this might work, but I need to ask you some questions as I go. I hope you get this post. If it's OK for me to ask you directly, e-mail me with an address I can reply to. THANKS.

Collapse -

Need an Access 97 VB Code Expert

by learneverything In reply to Need an Access 97 VB Code ...

This question was closed by the author

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums