UNIVERSITY OF BRISTOL COMPUTING SERVICE

This document may be used wholly or in part by the academic community, providing suitable acknowledgement is made. It may not be used for any commercial or profit-making purpose without permission.

Practical Advanced Reports and Access Basic

Summary of Report wizards

Creating a report from a form

Designing a report from scratch

Common Application Solutions

Splitting a persons address

Splitting a persons name

Database Documentor

Customising Microsoft Access

Options and Preferences

Backup

Compressing the database

Introduction

There are many things that one can do to help applications and here are a few listed below
Look at the design of Report1:

note the use of a line count in the detail section by using countrol source `=1'. By setting running source to over group, 1 is added to the control each time a new line is displayed. The count starts again at 1 when a new patient is displayed
note the count calculation in the footer. Other aggregate functions are: max, min, sum, avg, stdev, var
look at the name expression to avoid too many blank spaces between the forename and family name
check the sorting icon to see how the data is grouped

Summary of Report wizards

Autoreport

The Columnar AutoReport lists the table's fields from top to bottom without truncation

The Tabular Autoreport lists each record over one line, labels once per page

Report Wizard

Label wizard

text fields must have properties cangrow=yes and canshrink=yes
the width of the label must not be larger than the size of the page.
- detail cangrow and canshrink must also set to yes
- keep together should be per column rather than per page
- try reducing the height of the label and move it up to get better margins
It can be easier to start again if a label falls over

Creating a report from a form

Often the same layout is appropriate for a form and report. there are two methods to create a report like a form. Method 1:

open the Patients form which already has the fields laid out and sized appropriately
Make a note of the record source
select all the controls and copy them with Ctrl-C
Create a new report using Design view with the same record source as the form
paste the controls onto it.

Method 2:

Choose a form from list shown in the database window and rightclick
Select save as report

There are a few changes to make:

If the controls have the Sunken special effect, change it to Flat while they are still all selected; sunken controls don't print well.
If any controls have a colored background, change it to white or transparent.
remove the buttons, they have no effect
If the controls use a screen font (like MS Sans Serif), change it to a print font (like text to Arial and labels to Times Roman).

Field expressions

Certain types of data work better on reports as concatenated fields. You do not need separate controls for name components, or city, postcode, so you can use a calculated field directly in a control, or as an expression in a query column, to get names and addresses to print without gaps. The iif function (arguments: condition, output if true, output if false) can be used to prevent spaces or orphan punctuation if there is missing information in any of the component fields.

The strconv function can be used to change fields that have been entered in capitals to mixed case

=IIf([FirstName] is not null,[FirstName] & " ",null) & [LastName]
=IIf([City] is not null,[City] & ", ","",null) & IIf([county],[county] & " ",null) & [PostalCode]
=[WorkPhone] & IIf([WorkExtension] is not null," Ext " & [WorkExtension],null)
=IIf([e_mail_address] Is Not Null,"E-Mail: " & [E_MAIL_address],Null)
=strconv(town,3)

Varying number of lines

If there are any controls that might contain varying amounts of information (say Address or Notes)

set CanGrow property to Yes, so they can grow in height as needed.
Set the Detail section's CanGrow and CanShrink properties both to Yes, so the section can grow or shrink as needed. (note that all the controls are the same height in design view; the CanGrow setting allows some of them to expand as needed).

Designing a report from scratch

Design view does not use the wizard.

create a new report with Design view using all_info query
open the Sorting and Grouping dialog
select the fields to use for grouping and sorting. Choose DRug name for the first group (with group header and footer), to group by DRug name, and Given Name for the second group, for sorting only (no header or footer).

turn on the report header and footer by selecting Report Header/Footer from the View menu.
change the Drug name header section name from GroupHeader0 to Drugheader
add expressions (listed below) to print the date on the left side of the footer and Page x of y on the right side:
add controls to display appropriate text in the various headers and footers

Following is a list of the most useful header settings (annoyingly, they are not all in the same properties sheet) to construct informative report headers:

Report Properties Sheet

Property Name	Settings Choices	Explanation
Page Header	All Pages (default) Not with Rpt Hdr Not with Rpt Ftr Not with Rpt Hdr/Ftr	Choice of when to print the page header (generally, it is best to select Not with Rpt Hdr).
Page Footer	All Pages (default) Not with Rpt Hdr Not with Rpt Ftr Not with Rpt Hdr/Ftr	Choice of when to print the page footer (All Pages is usually fine).
Grp Keep Together	Per Page (default) Per Column	For multi-column reports, specifies whether to keep together groups by row or by column.

Report Header/Footer, Group Header/Footer Properties Sheets

Property Name	Settings Choices	Explanation
Force New Page	None (default) Before Section After Section Before & After Section	Where to start a new page; generally, after a section is best, otherwise you may get blank pages.
New Row or Col	None (default) Before Section After Section Before & After Section	For multi-column reports, specifies where the new row or column will start.
Keep Together	Yes (default) No	If Yes, keeps the entire section on one page; if No, will break within the section. You have more control in the Sorting/Grouping dialog, so use that instead if you need more control.
Repeat Section	Yes No (default)	Determines whether to repeat the section on the top of every page (only on group header).

Sorting and Grouping Fields

Property Name	Settings Choices	Explanation
Keep Together	No (default) Whole Group With First Detail	Whole Group keeps the entire group together (only practical if the groups aren't very long). Generally, With First Detail is the best choice; it keeps the header together with the first record in the detail section

To make the Drugname header print on each page of the report (not just when a new drug starts), only one change is needed:

set the Repeat Section property in the Drug name header properties sheet to Yes. The second page (and all other pages) now repeat the group header
In the report properties sheet : shrink the Page Header to nothing (since I am using a repeating group header, I don't need it)
in the Drug_name footer properties sheet select Force New Page After Section
in the Detail section set Keep Together to Yes.

The selections made in the Sorting/Grouping dialog and/or properties sheets control how and where the new pages will start; but they do not determine what will print in the report's page header or group header. For that, you need to place text boxes in the appropriate header and/or footer, and some special header properties must be run from code on a report section's Format or Print event.

To print the Drug name on the top of every page, all you need to do is to set the Repeat Section property to Yes, as described above. If you want something a little more sophisticated, such as printing the header with the text "continued" after the Drug name on pages where treatments for the same drug are printing, this takes a little more effort.

You can use the RunningSum property to accomplish this task.

Create text box DrugContinued with control source.
Set the new text box's Visible property to No, and position it right on top of the Drug name field
Place a text box called txtRunningSum in the Detail section with the expression
as its control source.
Make it invisible, and set its RunningSum property to Over Group. This text box will contain 1 on the first page of each Drug group, and higher numbers on all other pages of the group.
Click the Build button next to On Print in the Event page of the Page header section's properties sheet, and type in the lines between the Private Sub and End Sub lines:
This will make the "continued" text box visible if the detail section has data from the same drug as the last page; if it starts a new drug, the regular Drug name text box is visible.
Look at the "continued" group header.

Multi-column reports

open the Page Setup dialog from the report's File menu
click the Columns tab. Once you have the page open, fill in the number of columns, column spacing, and column width (row spacing and height are generally OK at the default values), and select the desired column layout from the option group at the bottom.
Back in the report itself, you have to manually move and resize the controls to all fit into a single column width (report and page headers and footers, however, extend across the entire page width).

HTML template files

When you output a datasheet, form, or report, or use the Publish to the Web Wizard, and you specify an HTML template file, Microsoft Access merges the HTML template file with the output files by replacing the tokens with the following.

HTML template token Replacement

 The object name (placed in the title bar of the Web browser)

 The object output

 An anchor tag to the first page

 An anchor tag to the previous page

 An anchor tag to the next page

 An anchor tag to the last document page

 The current page number

HTML template token	Replacement
<!--AccessTemplate_Title-->	The object name (placed in the title bar of the Web browser)
<!--AcessTemplate_Body-->	The object output
<!--AccessTemplate_FirstPage-->	An anchor tag to the first page
<!--AccessTemplate_PreviousPage-->	An anchor tag to the previous page
<!--AccessTemplate_NextPage-->	An anchor tag to the next page
<!--AccessTemplate_LastPage-->	An anchor tag to the last document page
<!--AccessTemplate_PageNumber-->	The current page number

\Program Files\Microsoft Office\Templates\Access\gray.htm

<HTML>
<TITLE>Switchboard</TITLE>
<BODY background = gray.jpg>
<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0><FONT FACE="Arial" COLOR= #000000><CAPTION><B>Switchboard</B></CAPTION>
<THEAD>
<TR>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLO R=#000000>Object</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLO R=#000000>ObjectType</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLO R=#000000>LastModified</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLO R=#000000>Description</FONT></TH>
</TR>
</THEAD>
<TBODY>
<TR VALIGN=TOP>
<TD BORDERCOLOR=#c0c0c0 ><U><FONT SIZE=2 FACE="Arial" COLOR=#0000 ff><A HREF="Report1_1.html">Report1</A></FONT></U>< ;/TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Rep ort</FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>5/1 3/99 2:04:39 PM</FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>< ;BR></FONT></TD>
</TR>
</TBODY>
<TFOOT></TFOOT>
</TABLE>
</BODY>
<BR><BR>
<IMG SRC = "msaccess.jpg">
</HTML>

<HTML>

<TITLE></TITLE>

<BODY BACKGROUND = "gray.jpg">


</BODY>
<BR><BR>


<A HREF = "">First</A>
<A HREF = "">Previous</A>
<A HREF = "">Next</A>
<A HREF = "">Last</A>

<P ALIGN = CENTER>Page .</P>

<IMG SRC = "company_logo.jpg">

</HTML>

HTML tags that specify files, such as the <IMG SRC> tag, assume the files reside in the same folder as the output files created by Microsoft Access. You can specify the folder destination in the Publish to the Web Wizard or by using the Save As/Export command on the File menu.
If you specify an HTML template file when you output an object to dynamic HTML format, it is merged with the .htx or .asp file during the output operation.

Access Basic code

Look at the code for the button on the `front' form by clicking on the button, looking at the `on click' event property and pressing `...'.
`docmd close' performs the command to close the form,
msgbox controls the message (text containing spaces must be enclosed by double quotes)

One can combine forms with Microsoft Access Basic code. Forms have procedures associated with events and saved with the form definition (as in all the following examples and the click subroutine above). To create a procedure to respond to the OnCurrent event of a form, for example, click the Code Builder button next to the OnCurrent property in the property sheet and write the desired code. The procedure is saved with the form itself. Only global procedures that is shared between forms is stored in modules. This model makes it easier to keep track of an application's code.

The following exercise shows different ways of creating a button to close a form and how to put an informative message on when the button is pressed. It also shows how using the button wizard produces default Access code and how it can be modified.

create a new form called front
create a button using the toolbox.
Choose category form operation with action close form.
Keep the exit picture.
Call the button commandexit to be more meaningful

Look at the properties of the on click event by pressing the 3 dots. This is Access Basic and can be modified. Note the DoCmd statement indicating a macro action to be performed.

 
Private Sub commandexit_Click() 
On Error GoTo Err_commandexit_Click 
    DoCmd.Close 
Exit_commandexit_Click: 
    Exit Sub 
Err_commandexit_Click: 
    MsgBox Err.Description 
    Resume Exit_commandexit_Click 
End Sub

run the form and see the result

Now add an extra line before the MsgBox Err.Description statement. (This is a global error message so does not distinguish between different types of errors- so quick and easy to give your own message for putting on previous record, and next record buttons for example, when the only error is likely to be that you are already at the first record so cant go back any further.)

Err.Description = "this is my own error message"

This changes the default error message to your own message.

The following shows how to use a macro on a button to perform the same effect:

create a macro frontclose
create another button category miscellaneous to run macro frontclose

The following shows how to add informative text to a default button

create another button to close the form
now edit the button code to add
MsgBox "you pressed the closed door button to see some useful examples"
before the docmd.close statement
compare the results of pressing the three buttons

The following code opens a form in Form view and moves to a new record. Note that the number of arguments to the DoCmd call reflects the arguments required on the macro call.

Sub ShowNewRecord() DoCmd.OpenForm "Patient", acNormal DoCmd.GoToRecord , , acNewRec End Sub

Common Application Design Solutions

macros

When should I use Visual Basic?

Make your database easier to maintain. Because macros are separate objects from the forms and reports that use them, a database containing many macros that respond to events on forms and reports can be difficult to maintain. In contrast, Visual Basic event procedures are built into the form's or report's definition. If you move a form or report from one database to another, the event procedures built into the form or report move with it.
Create your own functions. Microsoft Access includes many built-in functions, such as the ucase function, which converts text to upper case. You can use these functions to perform calculations without having to create complicated expressions. Using Visual Basic, you can also create your own functions either to perform calculations that exceed the capability of an expression or to replace complex expressions. In addition, you can use the functions you create in expressions to apply a common operation to more than one object.
Mask error messages. When something unexpected happens while a user is working with your database, and Microsoft Access displays an error message, the message can be quite mysterious to the user, especially if the user isn't familiar with Microsoft Access. Using Visual Basic, you can detect the error when it occurs and either display your own message or take some action.
Create or manipulate objects. In most cases, you'll find that it's easiest to create and modify an object in that object's Design view. In some situations, however, you may want to manipulate the definition of an object in code. Using Visual Basic, you can manipulate all the objects in a database, as well as the database itself.
Perform system-level actions. You can carry out the RunApp action in a macro to run another Windows-based or MS-DOS�based application from your application, but you can not use a macro to do much else outside Microsoft Access. Using Visual Basic, you can check to see if a file exists on the system, use Automation or dynamic data exchange (DDE) to communicate with other Windows-based applications such as Microsoft Excel, and call functions in Windows dynamic-link libraries (DLLs).
Manipulate records one at a time. You can use Visual Basic to step through a set of records one record at a time and perform an operation on each record (see the split name example). In contrast, macros work with entire sets of records at once.
Pass arguments to your Visual Basic procedures. You can set arguments for macro actions in the lower part of the Macro window when you create the macro, but you can not change them when the macro is running. With Visual Basic, however, you can pass arguments to your code at the time it is run or you can use variables for arguments � somethingyou can not do in macros. This gives you a great deal of flexibility in how your Visual Basic procedures run.

In the Database window, click the Macros tab.
Click the name of the macro you want to convert.
On the File menu, click Save As/Export.
In the Save As dialog box, click Save As Visual Basic Module.

Technique 1- Creating a Dialog Box

Modal

Borderstyle

Popup

ScrollBars

The following example shows a form that summarizes the treatments given to each patient between two given dates

create an aggregate query patient treatments technique 1 form based on patient and treatment showing the number of treatments given to each patient. Add a column on date start course with where selected for Total asking for date start course to be between two periods to be given on the form using parameter values:
create a form based on the above query. Save as Technique 1_(Treatment form used by dialog box form)
create a dialogue box form with no record source with two unbound columns- begin date and end date to match the above query. Change the labels of the unbound columns to begin date and end date
set Modal to yes (to close the form before the user can perform any other action)
set Borderstyle to Dialog (gives the form a thick (double) border and can include only a title bar, a Close button, and a Control menu. The form can not be maximized, minimized, resized or changed to design mode)
set Popup to (opens and remains on top of all other Microsoft Access windows)
set Scroll bars to neither
set record selectors to no
set navigation buttons to no
create a button on the dialog form to run the query by opening the form based on the query. The button wizard will create Access basic code that you can then modify. The category of button is Form, the action is open form, choose to show all the records (since the dialogue box is unbound). Put some helpful text on the button rather than a picture (range of accepatbale dates for example). Look at the On Click event properties of the button
edit the code by adding me.visible=False before the DoCmd openform statement
open the form in datasheet mode by changing the second argument to acFormDS.
Note that comments can be added by prefixing them with single quote on a new line.
create a button to cancel the query

Technique 2- Finding a Record Based on a Combo Box Selection

create a form based on the treatment table
create an unbound combo box named selectpatient in the form header
create an event procedures for after update event of the combo box
create an On Current event procedure for the form

Technique 3- Adding a New Record When It is Not in the List

create a form based on drugs with event on form unload
create a button to close the drug form
create the main form called `not in list' based on treatment table
create a combo box field drugid
create a not in list event procedure on the drugid field

Technique 4- Setting a Control's Record Source During Runtime

create an unbound form
create a list box based on the query

SELECT DISTINCTROW Patients.[patientID], [given Name] & " " & [family Name] AS Name FROM patients WHERE town = "bristol" ORDER BY [given Name] & " " & [family Name];
create an radio button with an event on after update

Sub optionUS_AfterUpdate () Dim SQLSource As String SQLSource = "SELECT DISTINCTROW patients.[patientID], [given Name] & ' ' & [family Name] AS Name FROM patients WHERE town " ' Set criteria to match option group choice. If optionpatient = 1 Then SQLSource = SQLSource & "= 'bristol' " Else SQLSource = SQLSource & "<> 'bristol' " End If SQLSource = SQLSource & "ORDER BY [given Name] & ' ' & [family Name];" 'Set the RowSource property of the list box and requery the list. [patientid].RowSource = SQLSource [patientid].Requery End Sub

Using Access Basic to split an address into separate fields

This example shows how to split an address field up when the user has entered it in one field with each address line separated by carriage return (chr(13) )

The functions used are:

mid

instr

Function get_address(fulladdress, line)
Dim rest As String
Dim address_line(1 To 10) As String
Dim i As Integer

For i = 1 To 10
address_line(i) = ""
Next
rest = fulladdress
i = 1

If InStr(rest, Chr(13)) = 0 Then
address_line(1) = fulladdress
Else: Do While InStr(rest, Chr(13)) > 0
address_line(i) = Left(rest, InStr(rest, Chr(13)) - 1)
'carriage return and line feed so pick up start of new address line as 'being the second character after the position of chr(13) rest = Mid(rest, InStr(rest, Chr(13)) + 2)
i = i + 1
Loop
End If
address_line(i) = rest
If i > 3 Then
MsgBox ("got more than 3 lines")
End If
get_address = address_line(line)
End Function

Create a query with the bad_design table to use the function:

address1:address_line1: get_address([address],1)
address_line2: get_address([address],2)

Using Access basic to split up a person's name into separate fields

fullname

initials, surname

title

Function get_initials(fullname)
get_initials = ""
rest = fullname
part_initials = ""
If InStr(rest, " ") > 2 Then
rest = Mid(rest, InStr(rest, " ") + 1)
End If
Do While InStr(rest, " ") > 0
part_initials = part_initials & Left(rest, 1)
rest = Mid(rest, 3)
Loop
get_initials = part_initials
End Function

Function get_surname(fullname)
get_surname = Mid(fullname, InStr(fullname, " ") + 1)
reduce = Mid(fullname, InStr(fullname, " ") + 1)
Do While InStr(reduce, " ") > 0
reduce = Mid(reduce, InStr(reduce, " ") + 1)
Loop
get_surname = reduce
End Function

Function get_title(fullname)
Dim rest
get_title = ""
If InStr(fullname, " ") > 2 Then
get_title = Left(fullname, InStr(fullname, " "))
End If
End Function

initials:get_initials([name])

surname: get_surname([name])

title: get_title([name])

Init	title	surname	fullname
AB		Smith	A B Smith
XYZ	Prof	Bloggs	Prof X Y Z Bloggs
ABC		Grigg	A B C Grigg
J		Smith	J Smith

Working through Prof X Y Bloggs:
Stage 1:
get_initials is null; rest is Prof X Y Bloggs; part_initials is null; instr(rest," ") is 5 (position of first space) Stage 2:
since instr is greater than 5, rest set to X Y Bloggs using mid
Stage 3:
instr(rest) is now 2 (position of first space in rest)
enter the do loop, set part_initials to X, rest to Y Bloggs
loop
instr(rest) is now 2, set part_initials to XY, rest to Bloggs
loop
instr(rest) is now 0 so end loop, set get_initials to the value of part_initials ie XY

Database Documentor

Select database documentor from File/Add-ins of the database window
select the object type that you want to examine (Tables, queries, forms, reports, macros, modules and all
highlight the objects to document from the list of objects for that object type
select the characteristics to see by clicking the options button
warning: each object is documented on a different page and large amounts of output can be generated so use the options to select what you really want to see

Customising Microsoft Access

Options and Preferences

From the view menu, select the options menu.
For a complete list and description of options press F1. All the changes are saved in the system.mda file. This is a system database which stores general settings, msacc20.ini settings, usernames and last databases accessed. To be really cautious keep a backup copy of system.mda, and the ini file if anything drastic is to be changed.

Backup

Compressing the database

Select Compact from the File menu
select the database and choose OK
specify the name, drive and directory for the compacted database