Reports in Access

1
Reports in Access

• Using expressions
• Using totals and control breaks
• Event procedures
• order of events
• useful event procedures

2
Using Expressions

• Calculations, e.g. line item totals
• example
• formatting the expression

Unbound text box control source is expression
Quantity Price
Use the Format property of the Text Box to change
the expression to Currency
3
Using Expressions

• Concatenating column headings or other text
  fields
• makes a report look tidier
• example

Delete messy looking labels and create an
unbound text box field. Join the fields using
concatenation.
CompanyName , LocationName ,
CountryName
4
Using Expressions

• Page numbers and dates
• automatically generated by report wizard BUT you
  may want to change them
• example

Page numbers -- create unbound text box and
select page number option from Insert menu
creates an expression Page Page
Dates -- create unbound text box and select date
and time options from Insert menu creates an
expression Format(Date(),Long Date)
5
Using Expressions

• Yes/No Values useful for categorizing information
• example

In a group header, add a check box. Modify the
control source to check for a value.
(SentDate - OrderDate) lt 2 Check box will
be filled in if the expression returns True.
6
Using Expressions

• IIf function
• useful for printing only the relevant information
  for a line item
• returns the true action if the condition is
  satisfied, otherwise it returns the false action
• example

IIf (IsNull(Age), , Age years old,)
7
Using Control Breaks and Totals

• Control breaks
• control breaks in Access are group headers and
  footers
• in Report Design view, set properties for group
  headers and footers
• can group on a specific value (e.g., category) or
  a range of values (e.g., numeric)

8
Using Control Breaks and Totals

• Totals
• VERY important to summarize information whenever
  there is a control break and at the end of a
  report
• place totals in group footers for control breaks
  and in the report footer for grand totals
• Count function used to count occurrences of a
  field
• example

Add unbound text box to a group footer. Set
control source to an expression Sum(Quantity
Price). Format as Currency.
9
Report Events

• Open
• when a report is opened, but before printing
  takes place.
• could be used to warn user that the report will
  take a long time to print, or even to password
  protect the report
• example ...

10
Report Events

• Open

Private Sub Report_Open (Cancel As Integer) Dim
strPwd As String strPwd InputBox (Please
enter the report password, Password) If
strPwd ltgt OpenTheReportHAL Then MsgBox Im
afraid I cant do that Dave! Cancel
True End If End Sub
11
Report Events

• Activate
• when a report that has been opened becomes the
  active window
• good place to display a custom menu or toolbar
• example

Private Sub Report_Activate() DoCmd.ShowToolbar
FullOrder, acToolbarYes End Sub
12
Report Events

• Deactivate
• when the report stops being the active window,
  but before another Access window becomes active
• use to remove the toolbar or menu
• Close
• when the report is closed and removed from the
  screen
• could use to log who used the report

13
Report Events

• Error
• when an error is generated within a report but
  NOT from a VB runtime error
• has 2 arguments
• DataErr is the error number, as set by Err
• Response determines whether an error message is
  displayed
• acDataErrContinue -- do not display the Access
  error message (but could display one of your own)
• acDataErrDisplay -- display the Access error
  message (default)

14
Report Events

• Error
• example

Private Sub Report_Error (DataErr As Integer,
Response As Integer) Response
acDataErrContinue LogError (Me, DataErr) End Sub
15
Report Events

• Format
• called every time Access formats the data in a
  report
• can be used to change report layout
• example ...

16
Report Events

• Format

Private Sub GroupFooter2_Format (Cancel As
Integer, FormatCount As Integer) If txtTotal gt
1000 Then lblAccount.Visible True Else
lblAccountVisible False End If End Sub
17
Report Events

• FormatCount property

If FormatCount 1 Then Select Case Age Case
Is lt 15 IntLessThan15 intLessThan15
1 Case Is gt 15 IntMoreThan15 intMoreThan15
1 Case Else Do nothing End Select End
If
18
Report Events

• Print
• after a section has been formatted but before it
  has been printed
• another way to count fields by their values
• store results in text boxes
• example ...

19
Report Events

• Print

If PrintCount 1 Then If Status Open
Then OpenIssues OpenIssues 1 End If If
Priority High Then HighPriority
HighPriority 1 End If End If
20
Report Events

• NoData
• the EASIEST event procedure every report needs
  this one
• example

Private Sub Report_NoData (Cancel As
Integer) MsgBox No data for this
report Cancel True End Sub