| | Feedback | Report template | | ||
These pieces of code can be added to report events. Within the properties of the report, a section or a field, choose Events and then click the [...] button beside the required event and select the Code Builder.
You may wish to choose whether or not to print a header / footer dependant on a certain value within your data. For example you may wish not to print a footer containing totals if there is only one record in the group. To achieve this you can include a running total field (see below) on your report which counts the number of records with a particular value (to fall inside a group).
Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
' note that this is linked to the Format event of the Footer0
If Me!MyCount > 1 Then
GroupFooter0.Visible = True
Else
GroupFooter0.Visible= False
End If
End Sub
This method can also be used if the query on which the report is based (the recordsource) contains a prompt and parameter asking whether the user wishes to view all records or just a summary.
Alternatively you may wish to format the appearance of a section (either detail of a header / footer) in a way which is dependant on the values it contains. The following routine will change the background and font colours and switch bold on or off dependent upon one of the field values.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [Amount] > 0 Then
Detail.BackColor = 8454143
SumOfAmount.ForeColor = 255
SumOfAmount.FontBold = True
Else
Detail.BackColor = 16777215
SumOfAmount.ForeColor = 0
SumOfAmount.FontBold = False
End If
End Sub
These number codes can be used to select particular colours: red=255 blue=16711680 black=0 pale green=13434828
To include a running total of a particular variable in the page footer follow these steps:
| In the report detail section, add a TextField (e.g. [text14]) and set the contents as: =Sum([Amount]) |
| Amend the Running Sum properties of the [text14] field to Yes |
| Set the Visibility property of [text14] to No. |
| In the page footer, add a new text box with contents: =[text14] |
When displaying numbers on your reports, the named formats (e.g. Standard) provided by Microsoft may not offer what you require and so you can add your own formats. The field format property codes are similar to those used by Excel and can contain elements which are conditional upon whether the number is positive, negative or zero. Each format picture is separated by a semi-colon. Type the following example into the Format property of a field (or a group of fields).
| Format codes | Examples of numbers | Formatted numbers |
| #,##0.0; (#,##0.0); " " | 1312.67 | -.6 | 0 | 1,312.7 | (0.6) | |
| #0; -#0; 0 | 1312.67 | -.6 | 0 | 1312 | -1 | 0 |
It is helpful to build a standard report containing regular headings, lines, dates and page numbers - i.e. a template.
| file: arepcode.htm | © MeadInKent.co.uk 2014 | Last updated Apr14 |