  |
Keeping a Sub Report Together on a Single Page. |
| When designing a report which contains one or more sub-reports, there may be instances where the sub report grows large enough where it will span more than one page; with the headers and a portion of the detail on one page, and additional detail on another page. While it is possible to repeat the sub report's page headers on the new page, spanning two pages may not be desirable for certain types of sub-reports, where the data needs to be together.
Normally, if you place your sub-report in the main report's detail section, there is no way to enforce a "keep together" property for the sub report so that if the data on the sub report grows large enough, it will force a new report page to be printed containing the entire sub report, rather than splitting it in two. Fortunately there is a work around available if you want to force a new page if the sub report spans more than one page. To implement the work around do the following: 1. In the sorting and grouping properties of the report, enter a "pseudo" group, whose name is a number, at the point in the report you want the sub report to appear, preceding the name of the "pseudo" group with an equals sign ( = ), such as: "= 1" 2. Set either the Group Header, or Group Footer property to "yes". 3. Place your sub-report in the Group Header or Group Footer, rather than in the detail section of the report. 4. In the properties for the header or footer (not the sub report,) set the "Keep together" property to "Yes". | |
|
Restarting Page Numbers at "1" for Each Group On a Report |
| Many reports have their records sorted by groupings. Often in reports such as invoices you may want the report to restart each group at page number 1, rather than the standard page number set by Access. This can be done quite simply using a little basic in the report's code module. Here is how to do it:
1.) In the declarations page to the code module for the report, dimension a variable for use as a flag to recognise the start of a group as in: Dim MyGroup as Integer 'Note you can use Boolean rather than integer in Access 7 or 82.) In the Group Header's "OnPrint" event add the following code to recognise that we've started a new group: MyGroup = -1 'Or true3.) In the Group Footer's "OnPrint" event, add the following code to note that we've reached the end of the Group: MyGroup = 0 'Or false4.) Create an unbound text control control in the page footer; something like: "PGNUM". 5.) In the PageFooter On Print Event, add the following code: Static GroupPGNum as Integer
'Note: Use static because you don't want value of GroupPGNum to
'change with each call to the code; if we just use "Dim"
'it zeros out GroupPGNum each time the code is called.
If MyGroup = 0 Then 'We ended the last group, in the footer MyGroup was set to 0.
GroupPGNum = 1 'first page number for the group
Else 'We are still in the same Group since MyGroup = -1 so increment the page number.
GroupPGNum= GroupPGNum+1 'increment the page number
End If
Me!PGNUM = GroupPGNum 'Assign the page number to the control.
| |
|
How to Avoid Abandoned Group Headers at the Bottom of a Report Column or Page |
| When you create a report which uses a Group Heading, for either columns or just for the general group, you have to protect against the column heading appearing at the bottom of a page or column and the detail appearing at the top of the next page or column, without the heading.
Here's is how to make sure the heading stays with the detail:
1.) First make sure that you have the KeepTogether property of both the detail and group heading section set to Yes. 2.) In the Group Header's "OnPrint" event add the following code: Dim R As Report
Set R = Me
If R.Top > (8.9 * 1440) Then
R.PrintSection = False
R.MoveLayout = True
R.NextRecord = False
End If
What this code does is to use the Top property of the report (i.e. how far printing is from the
physical top of the page,) and test how far down on the page printing will occur when the code is run. In this example we're testing whether we're farther down the page than 8.9 inches. You determine what number to use by looking at the height of your detail section + the page footer height + bottom margin and subtract from the total page length. (Or just fiddle around till you find what works!).
(Note: Access' internal measurement system is in twips, where there are 1440 twips per inch, so we actually need to measure in twips. If you are using centimeters for your system unit of measure there are 567 twips per centimeter, and you'll have to adjust the example above.)
If the Group Header would print at a position greater than 8.9 inches, this code tells the report to:
PrintSection = False 'Don't print the group header at this point
MoveLayout = True 'Move to the next section in the layout, which would be the top of the next page or column.
NextRecord = False 'Don't advance to the next record, we want to print the header when we get to the next print location.
| |
|
How Print only the Odd Or Even Pages of A Report |
| Often you may want to print certain types of Access Reports such as address books, or other reports on both sides of the report stock. If you don't have a duplex printer this might seem like a daunting task but its not. Here's how:
1.) On what ever form you will be using to initiate printing of your report, add an option group with two options called Odd and Even. Set the option values for each as: Even should have a value of one and Odd should have a value of 0. 2.) For each section of your report in the "OnPrint" event add the following code: If Me.page mod 2 = MyForm!optOddEven then
Cancel = -1
End if
This code uses the mod function built into Access which can be useful in many situations. How it works is if you have even pages selected to be printed on your form, when the report hits the code and its an odd page, (say page 3,) 3 mod 2 = 1. Since Even = 1 on your form, then printing of this section for that page is canceled.
| |
|
| How to Send Parameters to Reports only Using Code (Not using Query By Form) |
| Access developers sooner or later run into a situation where they need to generate reports which are filtered or use parameters, but where all the parameters need to be set via code, rather than having them pulled from a form using query by form.
Most developer's know how to set parameters for opening a recordset, using the parameters property of the DAO QueryDef object. There are two rather simple ways to do set parameters when working with a Report object rather than with the standard record set:
| |
|
How Do I Get My Report to Open In Preview In Front of My Form Rather Than Behind It? |
If you are opening a report in preview from a form which is set to be a dialog form or has its pop up property set to true, your report will open behind your form rather than in front of it. You can change this behavior by temporarily hiding your form when the report is opened, and then redisplaying it when the report is closed Here's code to do this:
Sub OpenRptFromDialogForm ()
DoCmd.OpenReport "MyReport, acPreview
Me.Visible = False
DoEvents
While SysCmd(acSysCmdGetObjectState, acReport, "MyReport") = acObjState_Open
DoEvents
Wend
Me.Visible = True
DoEvents
End Sub
| |
|
How Can I Tell If My Report Was Opened In Preview or Printed? |
| There are occasions where in your application, you need to know if a report was printed or previewed in order to allow for some action
to be taken after printing and to not take a similar action (e.g. an update query,) if the report was only previewed.
To determine this, you can call the following function from your report's OnClose event. It will return true if the report was previewed, and false if it was sent to the printer. If a call to this function returns false, then have your report call a second sub procedure which sets a module level variable in whatever module opens the report to act as a flag on whether to take action or not. The one trick to using this code, is that you should remove the print icons from both the tool bar and menu for your reports (Do this by creating custom tool bars and menus) because if the user previews the report and then prints from the preview, the function will still return a value of true for the report only being previewed. Declares:
Private Declare Function GetWindowText _
Lib "user32" Alias "GetWindowTextA" _
(ByVal hWnd&, ByVal lpstrTitleBuff$, _
ByVal intCharstoCopy%) as Long
Private Declare Function GetClassName _
Lib "user32" Alias "GetClassNameA" _
(ByVal hWnd As Long, ByVal lpClassName As String, _
ByVal nMaxCount As Long) As Long
Private Declare Function GetWindow _
Lib "user32" (ByVal hWnd As Long, _
ByVal wFlag As Long) As Long
Private Declare Function FindWindowEx _
Lib "user32" Alias "FindWindowExA" _
(ByVal hwndParent As Long, ByVal hWndChid As Long, _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function IsWindowVisible Lib _
"user32" (ByVal hWndTarget As Long) As Long
Public Function ChkPreview(RptName As String) As Boolean
'-------------------------------------------------------
'Purpose: To see if an Access report is open in preview
'or not (i.e. its opened to print)
'Accepts: Report Name up to 100 characters
'Return: True if in preview, false if not
'Copyright © 1997-98 ACG Soft, Ann Arbor, MI USA
'All rights reserved.
'---------------------------------------------------------
On Error GoTo ErrHandler
Dim Winhwnd&, hWndMIDI&, hWndTarget&
Dim ClsNameBuff$
Dim ClsBuffsz%
Dim TitleBuff As String * 100
Dim titlestr$
Dim dwReturn&
Const TARGET_WINDOW_MIDI = "MDIClient"
Const TARGET_WINDOW_DB = "OReport"
Const GW_HWNDNEXT = 2
Const GW_CHILD = 5
ClsBuffsz = 30
ClsNameBuff = Space(ClsBuffsz)
ChkPreview = False
Winhwnd = Application.hWndAccessApp
hWndMIDI = FindWindowEx(Winhwnd, 0&, TARGET_WINDOW_MIDI, vbNullString)
If hWndMIDI = 0 Then
'If its not found exit gracefully
Exit Function
End If
'Found the MIDI Client Class, Now find the OReport Class window
'by looking through children for a report with the like name.
hWndTarget = GetWindow(hWndMIDI, GW_CHILD)
While hWndTarget > 0
dwReturn = GetClassName(hWndTarget, ClsNameBuff, ClsBuffsz)
If InStr(ClsNameBuff, TARGET_WINDOW_DB) > 0 Then
dwReturn = GetWindowText(hWndTarget, TitleBuff, 100)
titlestr = Trim(TitleBuff)
If InStr(titlestr, RptName) > 0 Then
dwReturn = IsWindowVisible(hWndTarget)
If dwReturn = 1 Then ChkPreview = True
Exit Function
End If
End If
hWndTarget = GetWindow(hWndTarget, GW_HWNDNEXT)
Wend
Exit_MWC:
Exit Function
ErrHandler:
Resume Exit_MWC
End Function
| |
|
| How to Sequentially Number the Pages of Multiple Reports |
|
If you need to sequentially number the pages in a series of separate reports this can be done using two simple functions created in a general module and called from each reports. Here's how:
| |
|
|
How to Assure Proper Page Orientation and/or Paper Size for A Report Run On Different Machines |
|
When a unique paper size or orientation for a report is used, when the report is moved to different machines with different printers it can "loose" its design settings when previewed and printed. To assure proper orientation or paper size, you must open the report in design mode and set these properties of the report before preview or printing using the prtDevMode property of the report in all versions prior to Access 2002. (In Access 2002, you can open the report in design and set the specific paper size and orientation properties without using the prtDevMode property. Listed below is a function called SetRptProps that you can call to assure the proper orientation and paper size. Note that the code will work all versions of Access after Access 97 (including Access 2002). The function to call is listed after a series of declarations and following the code is a series of constants for various paper sizes. As an alternative, you can also use our "On the Fly Printing" code/class modules to provide users the ability to select the output printer at run time and automatically retain your desired paper and orientation settings. (Note the code below will NOT work in MDE/ADE applications.) 1. Create a general module paste in the code shown below: 2. Call the function SetRptProps before calling any DoCmd.OpenReport method in your application, specifying the paper size and orientation. (Copy the constants you need into your calling form's module.) 3. The specific standard values for different orientations, paper sizes that you would need can be downloaded by clicking here Tray And and Paper Size Constants. 4. Note that for tray numbers, many printers use custom values for various tray assignments and you may need to set your report to a specific tray and retrieve the value of dmDefaultSource to determine the printer drivers correct value for the tray. 'Declarations
'Copyright © 1995-2003 ACG Soft
'The following variable is used to turn on or off window locking in the
'functions. Set this to True for any debugging activites.
Const DEBUGC As Boolean = False
Private Type DEV_MODE_STRING_WIDE
RGB As String * 6144
End Type
Private Type DEV_MODE_STRING_NARROW
RGB As String * 94
End Type
Private Type DEV_MODE_NARROW
dmDeviceName As String * 16
dmSpecVersion As Integer
dmDriverVersion As Integer
dmSize As Integer
dmDriverExtra As Integer
dmFields As Long
dmOrientation As Integer
dmPaperSize As Integer
dmPaperLength As Integer
dmPaperWidth As Integer
dmScale As Integer
dmCopies As Integer
dmDefaultSource As Integer
dmPrintQuality As Integer
dmColor As Integer
dmDuplex As Integer
dmYResolution As Integer
dmTTOption As Integer
dmCollate As Integer
dmFormName As String * 16
dmLogPixels As Long
dmBitsPerPel As Long
dmPelsWidth As Long
dmPelsHeight As Long
dmDisplayFlags As Long
dmDisplayFrequency As Long
End Type
Private Declare Function api_FindWindow32 Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As Any) As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hwndParent As Long, ByVal hWndChid As Long, _
ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function api_LockWindow32 Lib "user32" Alias "LockWindowUpdate" _
(ByVal hwndLock As Long) As Long
Private Declare Function api_DestroyWindow32 Lib "user32" Alias "DestroyWindow" _
(ByVal Hwnd As Long) As Long
Private Sub atLockWindow(TrueOrFalse As Boolean)
On Error Resume Next
Dim Status&, hWndTarget&
If TrueOrFalse = True And DEBUGC = False Then
hWndTarget = FindWindowEx(Application.hWndAccessApp, 0&, "MDIClient", vbNullString)
If hWndTarget <> 0 Then Status = api_LockWindow32(hWndTarget)
Else
Status = api_LockWindow32(0)
End If
End Sub
Public Function SetRptProps(strRptName As String, Optional intOrientation As Byte, _
Optional intPaper As Integer, _
Optional intWidth As Integer, _
Optional intLength As Integer, _
Optional intTray As Integer) As Integer
'-------------------------------------
'Purpose: Sets a report's paper size, orientation, tray as desired.
'Tray and paper size values available: Tray And and Paper Size Constants
'Written by: ACG Soft, Ann Arbor, MI USA
'Accepts: strRptName: Name of a Report to Set
'For custom paper sizes the width and length are set in millimeters.
'If using inches as a base measure, then multiply the value used by 254.
'--------------------------------------
On Error GoTo ErrHander
Dim lngReturn As Long
Dim lngOrigFields as Long, lngDMLen As Long, boolFormUse As Boolean
Dim strRptDevMode As String
Dim udtDMStructOnlyStr As DEV_MODE_STRING_NARROW
Dim udtDMNarrow As DEV_MODE_NARROW
Dim udtDMstr As DEV_MODE_STRING_WIDE
Dim objFrm As Form
Const DM_PAPERSIZE = &H2&
Const DM_PAPERLENGTH = &H4&
Const DM_PAPERWIDTH = &H8&
Const DM_DEFAULTSOURCE As Long = &H200
boolFormUse = False
Set objFrm = Screen.ActiveForm
boolFormUse = True
'Open report get its base settings
NoForm:
If DEBUGC = False Then DoCmd.Echo False
DoCmd.OpenReport strRptName, acDesign
Reports(strRptName).Painting = False
DoCmd.ShowToolbar "Report Design", acToolbarNo
DoCmd.ShowToolbar "ToolBox", acToolbarNo
DoCmd.ShowToolbar "Formatting (Form/Report)", acToolbarNo
DoCmd.SetWarnings False
'kill the dialogs if open
lngReturn = api_DestroyWindow32(api_FindWindow32("OEcl", 0&))
lngReturn = api_DestroyWindow32(api_FindWindow32("OSnG", 0&))
lngReturn = api_DestroyWindow32(api_FindWindow32("OArgDlg", 0&))
'Refocus on the form if in use
If boolFormUse = True Then
DoCmd.SelectObject acForm, objFrm.Name
End If
'Lock it up to hide what's going on
Application.Echo True
Call atLockWindow(True)
'Add the settings desired
If Not IsNull(Reports(strRptName).PrtDevMode) Then
'set the byte array into the struct
udtDMstr.RGB = Reports(strRptName).PrtDevMode
LSet udtDMNarrow = udtDMstr
lngDMLen = udtDMNarrow.dmSize + udtDMNarrow.dmDriverExtra
lngOrigFields = udtDMNarrow.dmFields
If (intOrientation > 0) And (intOrientation = 1 Or intOrientation = 2) Then
udtDMNarrow.dmOrientation = intOrientation
End If
If intPaper > 0 Then
udtDMNarrow.dmPaperSize = intPaper
udtDMNarrow.dmFields = udtDMNarrow.dmFields Or DM_PAPERSIZE
If intWidth > 0 And intPaper = 256 Then
udtDMNarrow.dmPaperWidth = intWidth
udtDMNarrow.dmFields = udtDMNarrow.dmFields Or DM_PAPERWIDTH
End If
If intLength > 0 And intPaper = 256 Then
udtDMNarrow.dmPaperLength = intLength
udtDMNarrow.dmFields = udtDMNarrow.dmFields Or DM_PAPERLENGTH
End If
End If
If intTray >= 1 Then
udtDMNarrow.dmDefaultSource = intTray
udtDMNarrow.dmFields = udtDMNarrow.dmFields Or DM_DEFAULTSOURCE
End If
LSet udtDMStructOnlyStr = udtDMNarrow
Mid(udtDMstr.RGB, 1, 54) = udtDMStructOnlyStr.RGB
strRptDevMode = left(udtDMstr.RGB, lngDMLen)
Reports(strRptName).PrtDevMode = strRptDevMode
End If
CloseRpt:
On Error Resume Next
If DEBUGC = False Then Application.Echo False
Reports(strRptName).Painting = True
DoCmd.Close acReport, strRptName, acSaveYes
DoCmd.ShowToolbar "Report Design", acToolbarWhereApprop
DoCmd.ShowToolbar "ToolBox", acToolbarWhereApprop
DoCmd.ShowToolbar "Formatting (Form/Report)", acToolbarWhereApprop
DoCmd.SetWarnings True
Application.Echo True
Call atLockWindow(False)
SetRptProps = True
Exit_SPP:
Exit Function
ErrHandler:
Select Case Err.Number
Case 2103, 2451
SetRptProps = False
Application.Echo True
MsgBox "Error: The report name: """ & strRptName & """, is invalid.", vbCritical, "Report SetUp"
Call atLockWindow(False)
Resume Exit_SPP
Case 2475 'No form is active
Resume NoForm
Case 2601
SetRptProps = False
MsgBox "Error: No permissions for report """ & strRptName & """.", vbCritical, "Report Set Up"
Application.Echo True
Resume Exit_SPP
Case 2094 'Toolbar not found
Resume Next
Case Else
MsgBox "Error: " & Err & " " & Error$, , "Report Set Up"
SetRptProps = False
Err.Clear
Resume CloseRpt
End Select
End Function
| |
|
Scheduling and Automating Printing of Reports |
| You can use Windows Scripting Host (WSH)
and a vbScript file, combined with the Windows Task Scheduler to schedule
printing of reports. To do this, you create a vbscript (or Jscript) file
and run it using the script engine cscript.exe from the scheduler. A
sample script is shown below.
To run the script from the Task scheduler, select only cscript.exe when initially setting up the task. Then after the task has been set up, modify the task and add the path to the script file following the executable name, placing the vbscript file path in quotes if you are using a long file name as in: cscript.exe "c:\my dbs\run my report.vbs" '-------------------Begin Script Code--------
'Windows Scripting Host Script, for Win 95/98/NT/2000
'Compatible with WSH Version 1 and 2
'Written By ACG Soft, Ann Arbor, MI USA
'Run from the command line using the cscript.exe runtime engine
'Call cscript.exe from Windows Scheduler
'-----------------------------------------------------
On error resume next
call RunReport("c:\my dbs\my db with report.mdb", "Report1")
Function RunReport(strDbPathAndName, strRptName)
On Error Resume Next
Dim objDb 'no object qualifier in vbs
Set objDb = CreateObject("Access.Application")
objDb.Visible = True 'False if you desire
objDb.OpenCurrentDatabase (strDbPathAndName)
objDb.Docmd.OpenReport strRptName, 0 'print it
DoEvents
'Run a function if necessary
'objDb.Run "MyUpdateFunctionName" 'Don't use a sub, won't work
objDb.CloseCurrentDatabase
objDb.Quit
End Function
| |
|
How to Set Margins Via Code for a Report |
You can set the margins for a report
via code prior to previewing or printing a report. The code below should be placed in a general db module and
be called first then call the DoCmd.OpenReport Method. Note that this
code works in Access 95 and above. Even though in Access 2002 (Xp) you can
access the margin properties directly in design mode rather than using the
PrtMip property, this code will also work on that
version of Access for compatibility.'-------------------Module Declarations----------------
Type PRT_MIP_STRING
RGB As String * 28
End Type
Type PRT_MIP
'Typed as longs due to Ansi to Unicode conversion
xLeftMargin As Long
yTopMargin As Long
xRightMargin As Long
yBottomMargin As Long
fDataOnly As Long
xItemSizeWidth As Long
yItemSizeHeight As Long
fDefaultSize As Long
xItemsAcross As Long
yColumnSpacing As Long
xRowSpacing As Long
rItemLayout As Long
rFastPrinting As Long
rDataSheetHeadings As Long
End Type
'------------------------Function-----------------------------
Public Function SetReportMarginDefault(strReportName As String, left!, top!, right!, bottom!)
Dim PrtMipString As PRT_MIP_STRING
Dim PM As PRT_MIP
Dim objRpt As Report
Dim tempPrtMip As String
DoCmd.Echo False
DoCmd.OpenReport strReportName, acDesign
Reports(strReportName).Painting = False
Set objRpt = Reports(strReportName)
PrtMipString.RGB = objRpt.prtmip
LSet PM = PrtMipString
'Use 1440 for US (inches), 567 (rest of the world) (centimeters)
PM.xLeftMargin = left * 1440
PM.yTopMargin = top * 1440
PM.xRightMargin = right * 1440
PM.yBottomMargin = bottom * 1440
LSet PrtMipString = PM
objRpt.prtmip = PrtMipString.RGB
'Make sure report has the focus
DoCmd.SelectObject acReport, strReportName
'Save the Report
DoCmd.DoMenuItem 7, acFile, 4, , acMenuVer70
CloseRpt:
DoCmd.Close acReport, strReportName
DoCmd.Echo True
'You're done.
End Function
| |
|
How to create Calendar Style Reports |
An application may need to create calendar style reports for scheduling or job tasks. There are two ways to accomplish this:
| |
|
| Setting Stored Procedure Input Parameters at Run Time for Reports in ADP and ADE DBs in Access 2000 and 2002 |
Access developers are used to setting report parameters at run time by changing record sources, using SQL strings and other mechanisms. In the world of SQL Server based ADP and ADE databases, stored procedures ("sprocs") are often desired or required as the record source for a report since views can't accept parameters, nor can they use GROUP BY or other commands required for cross-tab queries. When a report is linked to a sproc, any required input parameters are specified in the Input Parameters property of the report. Dynamically changing the Input Parameters property via code will either error or have no impact on the output unless the property is changed when the report is in design mode. If you don't want to link the report's input parameters to a specific form how can input parameters be set on the fly a run time? In Access 2002, if you want to use the same report without a form and set the report's input parameters by code at run time, it is easy to do by supplying the parameters and changing the record source via code like this in the On Open event:
Dim strRecordSource As String
strRecordSource = "Exec [Sales By Year] '01/01/1996','07/31/1997'"
Me.RecordSource = strRecordSource
In Access 2000, this method doesn't work. A couple of possibilities exist to supply the input parameters at run time:
d.) Errata NOTE: A bug exists in both Access 2000's original release (prior to SR 2) that may cause the method described here not to work properly. This bug will arise if you first display the stored procedures window (in Access 2000) prior to running the report. Apparently when Access itself refreshes the db window it any subsequent call in VBA call to RefreshDatabaseWindow fails, even if you select an object in the db window. If you simply open the ADP container with the reports window displayed, the report configured as shown above will work properly. | |
|
Code To Reset All Reports To Use The Default Printer |
Before distributing your database to users, it
is recommended that all reports be set to use the User's default printer
rather than a specific printer. Manually opening and saving every report
can be time consuming. Here's code that works in all 32 bit versions of
Access to reset all reports to use the default printer.'-------------------Module Declarations----------------
Private Type DEV_NAMES_STRING
RGB As String * 80
End Type
Private Type DEV_NAMES_STRING_NARROW
RGB As String * 4
End Type
Private Type DEV_NAMES_NARROW
wDriverOffset As Integer
wDeviceOffset As Integer
wOutputoffset As Integer
wDefault As Integer
End Type
'------------------------Function-----------------------------
Public Function SetDefPrinter()
On Error Resume Next
Dim obj As Object, App As Object, Proj As Object
Dim strDNString As String
Dim udtDNamesStr As DEV_NAMES_STRING
Dim udtDNStrNarrow As DEV_NAMES_STRING_NARROW
Dim udtDN As DEV_NAMES_NARROW
Dim intAccessVer As Integer, i As Integer
intAccessVer = Val(SysCmd(acSysCmdAccessVer))
DoCmd.Echo False
Select Case intAccessVer
Case Is < 9 'Access 95/97
Set App = CurrentDb()
Set Proj = App.Containers("Reports")
For i = 0 To Proj.Documents.Count - 1
DoCmd.OpenReport ReportName:=Proj.Documents(i).Name, View:=acViewDesign
strDNString = Reports(Proj.Documents(i).Name).PrtDevNames
udtDNamesStr.RGB = strDNString
LSet udtDN = udtDNamesStr
udtDN.wDefault = 1
LSet udtDNStrNarrow = udtDN
Mid(strDNString, 1, 4) = udtDNStrNarrow.RGB
Reports(Proj.Documents(i).Name).PrtDevNames = strDNString
DoCmd.Close acReport, Proj.Documents(i).Name, acSaveYes
Next i
Case Is = 9 'Access 2000
Set App = Application
Set Proj = App.CurrentProject
For Each obj In Proj.AllReports
DoCmd.OpenReport ReportName:=obj.Name, View:=acViewDesign
strDNString = Reports(obj.Name).PrtDevNames
udtDNamesStr.RGB = strDNString
LSet udtDN = udtDNamesStr
udtDN.wDefault = 1
LSet udtDNStrNarrow = udtDN
Mid(strDNString, 1, 4) = udtDNStrNarrow.RGB
Reports(obj.Name).PrtDevNames = strDNString
DoCmd.Close acReport, obj.Name, acSaveYes
Next obj
Case Is > 9 'Access 2002 and greater
Set App = Application
Set Proj = App.CurrentProject
For Each obj In Proj.AllReports
DoCmd.OpenReport ReportName:=obj.Name, View:=acViewDesign
If Not Reports(obj.Name).UseDefaultPrinter Then
Reports(obj.Name).UseDefaultPrinter = True
DoCmd.Save ObjectType:=acReport, ObjectName:=obj.Name
End If
DoCmd.Close acReport, obj.Name, acSaveYes
Next obj
End Select
DoCmd.Echo True
End Function
This tip was prompted and contributed to by Bruce Dockeray,
UK
| |
|
| Solving Common Problems with Graphs in Access Reports |
Some of the most common questions related to Access reports involve problems with Microsoft Graph or formatting Graph presentations. Here's a few common issues and how to solve them with Visual Basic.
1) Problem: When the report is previewed or printed, Graph
doesn't display the proper data from its record source, instead it displays
either the data from a prior record or the sample data in the Graph data sheet:
On Error Resume Next
Dim objGraph As Object
Set objGraph = Me!TheNameOfYourGraph.Object
objGraph.Refresh
DoEvents
Set objGraph = Nothing
2) The data in the datasheet and sample data shown in Graph in design view
is not the data from the record source specified, rather it is some default sample data;
so its hard to properly design the graph.
Solution:
On Error Resume Next
Dim objGraph As Object
Set objGraph = Me!TheNameOfYourGraph.Object
objGraph.Refresh
'This will update the data sheet
objGraph.Application.Update
DoEvents
Set objGraph = Nothing
4) Design Goal: You need to have the color of a bar
change according to the value in Graph. Dim objGraphAs Object | |
Back to the Tips Index