Hello All
I am a little proud of myself and this method was of great benefit to me and
my company so I thought I would share it with everyone.
I created a system to allow my reports to be "blank" so that I could print a
version of my report with all the boxes and lines and so on. This allows a
blank version of the report to be filled out by hand and then someone can
enter it in to the application later. This allows our service techs to have
something to record data out in the field when their laptops are not able to
connect to our system.
There are 4 parts to this system, code on the report, a code module, and
“blank†records in the data and code on the buttons that call both the filled
version and blank version of the report.
The code for the filled button:
On Error GoTo Err_cmdPrintPartOrder_Click
Dim stLinkCriteria As String
Dim stDocName As String
stDocName = "rptPartOrders"
stLinkCriteria = "[PartOrderID]=" & Me.PartOrderId
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Exit_cmdPrintPartOrder_Click:
Exit Sub
Err_cmdPrintPartOrder_Click:
MsgBox Err.Description
Resume Exit_cmdPrintPartOrder_Click
End Sub
This code is pretty basic, nothing special, it just loads the report on the
appropriate record.
The code on the button that opens the blank version:
Private Sub cmdPrintBlankPartOrder_Click()
Dim blankRecord As String
blankRecord = "PartOrderId = 290"
DoCmd.OpenReport "rptPartOrders", acViewPreview, , blankRecord, , "Blank"
End Sub
This code does 2 things. It opens a specific record, and then adds the open
args of “Blankâ€. The record it opens is a record that I created to be a
“dummy†record for just this purpose, but it can be any record I want. For my
purposes it was necessary to have a specific record so that I can make sure
the subreports fill up a certain amount of lines.
The code for the report open and close:
Private Sub Report_Close()
On Error GoTo Err_Report_Close
If Me.OpenArgs = "Blank" Then
Call BlankDataControlsOnReports(Me, False)
Forms!mymainform.Visible = True
Forms!mymenuform.Visible = True
Forms!myblankformmenu.Visible = True
Else
Forms!mymainform.Visible = True
Forms!mypartorderform.Visible = True
End If
Exit_Report_Close:
Exit Sub
Err_Report_Close:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Report_Close
End Sub
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
DoCmd.Maximize
If Me.OpenArgs = "Blank" Then
Call BlankDataControlsOnReports(Me, True)
Forms!mymainform.Visible = False
Forms!mymenuform.Visible = False
Forms!myblankformmenu.Visible = False
Else
Forms!mymainform.Visible = False
Forms!mypartorderform.Visible = False
End If
Exit_Report_Open:
Exit Sub
Err_Report_Open:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Report_Open
End Sub
This code opens and closes the report and checks the opening arguments for
the key “Blankâ€. If it finds no args it just opens the record. Otherwise it
opens and calls a function to blank fields by changing the fore color to
white. I found it necessary to put the following code on the subreports so
that they were also turned blank:
Private Sub Report_Close()
On Error GoTo Err_Report_Close
If Me.Parent.OpenArgs = "Blank" Then
Call BlankDataControlsOnReports(Me, False)
End If
Exit_Report_Close:
Exit Sub
Err_Report_Close:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Report_Close
End Sub
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
DoCmd.Maximize
If Me.Parent.OpenArgs = "Blank" Then
Call BlankDataControlsOnReports(Me, True)
End If
Exit_Report_Open:
Exit Sub
Err_Report_Open:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Report_Open
End Sub
The main part of this system was the actual function. This code does all the
work:
On Error GoTo Err_Handler
'Purpose: change the fore color of the bound controls and on the
reports and any of its subreports.
'Arguments rpt = the form to be changed
' controlColor is used to tell the routine what color to
change the fields to
'Usage: Call BlankDataControlsOnReports(Me, "Blank")
Dim ctl As Control 'Each control on the report
Dim lngI As Long 'Loop controller
Dim ctlForeColor As Long ' Color variable
Dim subReportName As String
If controlColor = True Then
ctlForeColor = 16777215
ElseIf controlColor = False Then
ctlForeColor = 0
End If
For Each ctl In rpt.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup,
acOptionButton, acToggleButton
' Debug.Print ctl.Name & " " & ctl.ControlType & " acTextBox"
'blank/unblank these controls if bound to fields.
If HasProperty(ctl, "ControlSource") Then
If Len(ctl.ControlSource) > 0 Then 'And Not
ctl.ControlSource Like "=*"
If ctl.ForeColor <> ctlForeColor Then
ctl.ForeColor = ctlForeColor
End If
End If
End If
Case acCheckBox
' Debug.Print ctl.Name & " " & ctl.ControlType & " acCheckBox"
ctl.Visible = Not controlColor
Case acLabel, acLine, acRectangle, acCommandButton, acTabCtl,
acPage, acPageBreak, acImage, acObjectFrame
'Do nothing
Case Else
'Includes acBoundObjectFrame, acCustomControl
Debug.Print ctl.Name & " " & ctl.ControlType & " not handled
" & Now()
End Select
Next
On Error Resume Next
Exit_Handler:
Set ctl = Nothing
Exit Function
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Function
Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant
On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
This code might look familiar, I adapted it from code that would recurse and
lock fields on forms. I don’t remember where I got that code, but I can’t
take all the credit on this code.
I tried to get it to recurse through subreports, I was unsuccessful. That is
why it is necessary to put the blanking function call on the subreports.
If you find this useful go ahead and use it. thanks for those who helped me
with this idea, here in this thread, and those I bugged elsewhere.
If you do find it useful, post any changes or code efficiency revisions you
make, I might just implement your code.
