SHARED COMPUTING SERVICES

Microsoft Access XP/2003, Level 300

Simplify Database Management
Updated 12/14/05

Use Macros to Simplify Tasks

Run a Macro
You can execute macros by:
• Select the macro in the Database window and click Run !.
• Click Run ! in the Macro window.
• Click a command button that has a macro assigned to it.
• Select the macro from the ToolsJMacroJRun Macro menu.

Create a Macro
1. In the Database window, click Macros under Objects.
2. Click New on the Database window toolbar.
3. In the Action column, click in the first cell and then click the arrow to display the action list and select the action you want.
4. Type a comment for the action. Comments are optional, but make your macro easier to understand and maintain.
5. In the lower part of the window, specify arguments for the action, if any are required.
6. To add more actions to the macro, move to another action row, and repeat steps 3 through 5. Microsoft Access carries out the
actions in the order you list them.
7. Save the macro.

Create an AutoKeys Macro Group(Shortcut Keys)

The following table shows the key combinations you can use to make key assignments in an AutoKeys macro group.
SendKeys syntax Key combination
^A or ^4 1. Create a new macro with the name AutoKeys.
[Ctrl]+Any letter or number key
{F1} Any function key 2. Click Macro Names OR go to ViewJMacro
^{F1} [Ctrl]+Any function key Names and use the above combination in the Macro
+{F1} [Shift]+Any function key Name field.
{INSERT} [Insert]
^{INSERT} [Ctrl]+[Insert]
+{INSERT} [Shift]+[Insert]
{DELETE} or {DEL} [Delete]
^{DELETE} or ^{DEL} [Ctrl]+ [Delete]
+{DELETE} or +{DEL} [Shift]+ [Delete]

Use the RunCommand Action to Access the Menu Commands

You can use the RunCommand action to run a built-in Microsoft Access command.

Action argument Description

Command The name of the command you want to run. The Command box shows the available built-in commands in
Microsoft Access, in alphabetical order. This is a required argument.
Microsoft Access XP/2003, Level 300
Simplify Database Management

Create Decision-Making Macros

You can have Microsoft Access carry out a series of actions if the condition is true by typing an ellipsis (...) in the Condition column of
the actions that immediately follow the condition.

1. In the Macro window, click Conditions on the toolbar.

2. In the Condition column, type a conditional expression in the row where you want to set the condition.
3. In the Action column, enter the action you want Access to perform when the condition is true.

„ If the macro containing the SetValue action runs

Reference Object Properties from the form or report with the property you want to
Set a form, report, or control property using a macro set, you can refer to the property by using just the
syntax propertyname. However, it's a good idea to
1. In a macro, add a SetValue action.
use the full syntax to refer to the property to avoid
2. Set the Item action argument of the SetValue action to an
conflicts with names of controls or Visual Basic
expression that refers to the property you want to set:
keywords. For example, Name is a Microsoft Access
• To set a form or report property, use the syntax
property; if you also have a control on your form
Forms!formname.propertyname or
called Name, you should use the full syntax to refer to
Reports!reportname.propertyname.
both the control and the property.
• To set a property of a control on a form or report, use the
syntax Forms!formname!controlname.propertyname or
Reports!reportname!controlname.propertyname.
3. Set the Expression action argument of the SetValue action to the value you want to set the property to. If the setting is a
string, be sure to enclose it in double (") quotation marks. For example, to set the Caption property of a form to Orders, you
would enter "Orders" in the Expression argument.
Set a section property using a macro
1. In a macro, add a SetValue action.
2. In the Item action argument, use the syntax Forms!formname.Section(constant).propertyname to refer to the property you
want to set. The constant argument refers to a particular section on the form or report, as described in the Section property.
3. Set the Expression action argument as described above.

Manage and Use Macros

Run Macros from Macros
Select RunMacro in the Item action list and set the Macro Name argument to the name of the macro you want to run.
TIP: To create a keyboard
Create a Macro Button shortcut for the button, enter an
1. In Design view, be sure the Control Wizards tool on the toolbox is on. ampersand [&] immediately before
the letter you want to underline.
2. On the toolbox, click the Command Button tool.
3. On the form, click where you want to place the command button. The wizard begins.
4. Click Miscellaneous in the Categories box, and then, in the Actions box, click the type of button to create (Run Macro).
5. Follow the directions in the wizard. In the last step, click Finish to display the command button in Design view.
Maximize a Form on Startup
Assign Macros to Events 1. Open the form in Design view.
1. Open the form in Design view. 2. In the Properties dialog box, select the Event tab.
2. Open the Properties for the form and on 3. For the On Open property, select [Event Procedure] from the list.
the Event tab, click on Build … for the 4. Click Build … and enter: DoCmd.Maximize
event and select Macro Builder. If you 5. Save your changes, then close the Visual Basic Editor to return to the
already created the macro, you can select form. The next time a user opens that form, Access will automatically
it from the drop-down list instead of maximize the form within the main Access window.
building one.

page 2 Shared Computing Services

 Microsoft Access XP/2003, Level 300
Simplify Database Management

Manage Your Database

Create a Switchboard
1. ToolsJDatabase UtilitiesJSwitchboard Manager
2. Click Edit J New
3. Enter the menu item; select the command and other information as required.
Use the Switchboard to Close Access
The Exit Application command does exit the application but, it leaves you in Access with no database open. Here’s the workaround.
1. In Design view, select ViewJCode to open the Visual Basic Editor.
2. Select EditJFind and type Case concmdExitApplication in the Find What text box, then click Find Next .
3. Comment out the line below by placing an apostrophe before the command so it looks like 'CloseCurrentDatabase.
4. Type the line DoCmd.Quit. (In Access 2003, type DoCmd.Quit acQuitPrompt)
5. Save and then close the Visual Basic Editor.
6. Save changes to the form and switch to Form view to test.

Set Startup Options

ToolsJStartup…

Set a Database Password Hide the “New Object” Shortcuts

1. In Open dialog box, select the database and click the 1. Go to the Tools menu and select Options.
drop-down list next to Open u . 2. On the View tab, clear the ;New object shortcuts
2. ToolsJSecurityJSet Database Password… check box and click OK .
3. Type a password.

Organize Objects in the Database Window

1. Click the Groups bar heading to display the current groups if none are visible.
2. Right-click on the Groups bar and choose New Group
3. Type an appropriate name for the group and click OK .
4. Drag the objects you want to create shortcuts to from the Database window to the Groups folder.

Shared Computing Services page 3

Microsoft Access XP/2003, Level 300
Simplify Database Management

Advanced Queries
Create a Crosstab Query Find Percent of a Total
1. Create new Crosstab query with the Wizard. 1. Create a Query calculating the Totals using the
2. Select the table or query to be used, then click Next> . appropriate table.
3. Select the field for row headings, then click Next> . 2. Create a Query with the original table and the new
4. Select the field for column headings, then click Next> . (If totals query.
either the row or column headings are a date, you’ll need to 3. Find the percent with the formula
select the interval.) Name1%: [field] / [Total of field]
5. Select the field for the data of the table and the function to and format as a percent style.
perform, then click Next> .
6. Name the query, then click Finish .

Group Totals by Months

1. Create a Select query.
2. Create an expression field for the date using the DateSerial(Year,Month,Day) function.
Change the Day argument to a 1.
i.e. MonthGroup: DateSerial(Year([DateField]),Month([DateField]),1)
3. Create a new Report using your new query.
4. Group on the report on the MonthGroup field.

Retrieve Data for the Current Month

1. Create a Select query.
2. Create an expression field for the Year using the Year([DateField]) function but, do not show in results.
i.e. CurrentYear: (Year([DateField])
3. Add criteria for the current year. i.e. Year(Now())
4. Create an expression field for the Month using the Month([DateField]) function.
i.e. CurrentMonth: (Month([DateField])
5. Add criteria for the current month. i.e. Month(Now())
6. Add any additional fields as needed.
7. Save and close the query.

Create an Alias for a Table

1. Create a new query in Design view.
2. Show the chosen table twice.
3. Right-click Table_1 and provide a new name for the Alias property.
4. Create a left outer join between the two fields that relate. (i.e. OriginalTable.Supervisor ID J AliasTable.Employee ID)
5. Add all appropriate fields from both tables. Fields with the same names will appear with the appropriate table name in front.
(i.e. TableName.FieldName)

Parse (split) Data in a Table „ This may not be able to

parse all of the records
1. Create a new query in Design view using the appropriate table.
accurately, due to data entry
2. Add all fields required except the StudentName field. inconsistencies.
3. To parse the data, use the following functions:
FirstName: Mid([StudentName],InStr([StudentName],",")+2)
LastName: Left([StudentName],InStr([StudentName],",")-1)
4. If the results of your query are what you need, change the query into an action or append query to save the data to the
appropriate table fields.

page 4 Shared Computing Services

 Microsoft Access XP/2003, Level 300
Simplify Database Management
Frequency Distributions
1. Create a table defining the groups and numerical ranges. You must include a Group ID (autonumber), a group name (text), a
minimum (number) and a maximum (number) fields.
2. Enter the data into the table to define the ranges. Remember that the number in the Maximum field MUST be larger than the
number provided in the Minimum field for a group.
3. Create a Select query using range table and the data table.
Field: Group Name ID Group ID Numerical Range
Table: Range Data Range Data
Total: Group By Count Group By Where
Sort: Ascending
Show: ; ; † †
Criteria: Between [Minimum] and [Maximum]

Not Equal Between Fields

Create a Select query using the two tables whose data you want to compare.
Field: Name Permanent Address Local Address If the criteria is in the Local Address
Table: Table1 Table2 Table1 field of Table1 table, then the code
Sort: refers to the Permanent Address from
Show: ; ; ; the Table2 table.
Criteria: <>[Table2].[Permanent Address]

Field: Name Permanent Address Local Address If the criteria is in the Permanent
Table: Table1 Table2 Table1 Address field of the Table2 table,
Sort: then the code refers to the Local
Show: ; ; ; Address from the Table1 table.
Criteria: <>[Table1].[Local Address]

Advanced Forms and Reports

Use the ActiveX Calendar Control on a Form
This control is actually a combination of a combo box with a calendar control.
1. Select InsertJActiveX Control when in Design view.
2. Select the Microsoft Date and Time Picker Control 6.0 and click OK .
3. Open the Properties window J Data tab, and select the field from the drop-down list for the Control Source property.
If you double-click the control while in Design view (OR right-click it and choose DT Picker ObjectJProperties), you can further
customize settings.

Use IIF Statement and Other Functions

Create an Unbound Text box
1. =IIF(«expression»,«truepart»,«falsepart») where expression is the comparison statement to which you are
trying to match. truepart is the result if the comparison matches the statement. falsepart is the result if the comparison does
not match the statement.
2. =Round(«number»,«precision») where number is the field or expression to round. precision is the number of
decimal places to round to.
3. =DateAdd(«interval»,«number»,«date») where interval is the date interval to measure(i.e. day, month, year).
number is the number of the interval to add. date is the field to which you are adding the interval.
4. =Format(«expression»,«format»)where expression is the field or expression to format. format is the format you
want the expression to be in.

Shared Computing Services page 5

Microsoft Access XP/2003, Level 300
Simplify Database Management

Substitute Text in Place of Null Values

Syntax: =Nz([Field],ValueIfNull)
Where Field is the field or variable to check and ValueIfNull is the text string, value or
function to perform if the Field is null.

Concatenate Fields on a Form or Report

1. Open the form or report in Design view.
2. Click the Text Box tool in the toolbox, and then click in the report header or
footer.
3. In the label for the unbound text box you just added, type a caption or delete the label.
4. Right-click the text box, and then click Properties.
5. Click the Data tab, and then type the following text in the Control Source property box:
[FieldName] & " " & [FieldName]
Remember to type field names in square brackets. Use the ampersand (&) to “connect” fields or literal text which may be
included in double quotes (“ ”).
Display Partial Text (Truncation)
Syntax: =Left([Field],Number of characters to truncate to)
1. =Left([FirstName],1)&". "&[LastName]

Insert and Format the Tab Control on a Form

1. Open a form in Design view
2. In the toolbox, click the Tab Control tool and then click the form where you want to place the control.
3. Use the following list for other tasks you might want to perform:
a. To change the caption displayed on a tab, open its property sheet, and then specify a new caption in the Caption
property.
b. To add, delete, or change the order of tabs, click the border of the tab control with the right mouse button and click
Insert Page, Delete Page, or Page Order.
c. To change the font name, font size, font style, and so on, of the captions on tabs, open its property sheet and then set the
appropriate properties. You can’t specify different settings for different tabs.
d. To add a graphic to the tab, open its property sheet and on the Format tab, click the Build button for the Picture
property. Choose a graphic and then click OK .
4. Size the tab control as appropriate. Click each tab to make sure all the controls fit well within each tab.

Suppress Headers and Footers at a Report’s Beginning or End

By default, page headers and footers appear on every page when you print a report. If your report includes a report header or footer,
which prints at the beginning and end of the report, the normal page header and footer may be inappropriate on the affected pages.
1. Open the report in Design view and display the form’s properties.
2. Control when page headers and footers print using the following settings:
• All Pages (the default)
• Not With Rpt Hdr
• Not With Rpt Ftr
• Not With Rpt Hdr/Ftr

page 6 Shared Computing Services

 Microsoft Access XP/2003, Level 300
Simplify Database Management

Stop Empty Reports

As long as the report is bound to a table or query, there’s a quick way to tell Access not to run a report if it contains no data.
1. Open the report in Design view.
2. In the Properties dialog box, click the Event tab.
3. Click the On No Data property, choose [Event Procedure] from the drop-down list, and click Build … to launch the Visual
Basic Editor.
4. Enter the following:
Private Sub Report_NoData(Cancel As Integer)
MsgBox ("Sorry--this report contains no data. Canceling...")
Cancel = -1
End Sub
5. Save your work. The next time the report is run without data, the message box will appear.

Create Report Based on a Viewed Record on an Open Form

1. Open the form with the criteria needed for the report.
2. Open a query in Design view.
3. Right-click the criteria area for the field listed in the Query Design Grid and select Build to open the Expression Builder.
4. In the lower-left box of the Expression Builder, double-click or click the folder containing the element you want.
Forms J Loaded Forms J (your form name here)
5. In the lower-middle folder, double-click an element to paste it into the expression box, or click a category of elements. Be sure
to select the element by which you want to filter the report.
6. When your expression is complete, click OK .
7. Save and close the query.
8. Open the form in Design view and create a macro button to preview or print the report that is based on the adjusted query.

Add the Database Name to a Report

1. Open your report in Design view.
2. Click the Text Box tool in the toolbox, and then click in the report header or footer.
3. In the label for the unbound text box you just added, type Database:
4. Right-click the text box, and then click Properties.
5. Click the Data tab, and then type the following text in the Control Source property box: =[CurrentDb].[Name]
6. On the View menu, click Print Preview to see the results. (Use Layout Preview to view just a sample of the data)

Add the Current User’s Name to a Report

1. Open your report in Design view.
2. Click the Text Box tool in the toolbox, and then click in the report header or footer.
3. In the label for the unbound text box you just added, type Current User: or Printed by:
4. Right-click the text box, and then click Properties.
5. Click the Data tab, and then type the following text in the Control Source property box: =CurrentUser()
6. On the View menu, click Print Preview to see the results. (Use Layout Preview to view just a sample of the data).

Shared Computing Services page 7