Tips for using Access
This is a collection of tips that don’t warrant a whole page to
themselves, but could make your life easier.
General hints and tips
print, preview or design reports
- Calculate Working Days Using International Date Formats
- Using Dates in SQL Expressions
Access is an event-driven language. Events happen when a form is
loaded, a button is clicked, a selection is made from a combo box,
and so on. We can create code that responds to such events so that
certain controls are displayed or hidden, records are filtered,
another form or report is launched, and much more. This tip shows
you how to get into the VBA environment, so that you can
create (or paste) code.
Let’s assume that you have a button called cmdOpenForm and you
want to open frmDetail when the button is clicked. Do the following:
- Right-click cmdOpenForm and select its Properties, then
select the Events tab
- Double-click the blank line next to On Click. You will see
- Now click the Builder (…) button at the end of the line,
to go to the code window.
- You will see…
Private Sub cmdOpenForm_Click() End Sub
Edit this so it reads
Private Sub cmdOpenForm_Click() DoCmd.OpenForm "frmDetail", acNormal End Sub
Save the form, go to Form view, and try it out.
Explaining the code
The line that launches the form uses the DoCmd object, which
enables you to replicate almost all of the commands in the Macro
builder. The syntax for DoCmd.OpenForm is
DoCmd.OpenForm "form name", View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs
With the exception of the form name, all other arguments are
optional. However, if you want to define a WhereCondition and not a
FilterName, you need include a comma to show that you skipped an
DoCmd.OpenForm "frmDetail", acNormal,,strWhere
For a more detailed description of the arguments in the OpenForm
method, and a number of examples, see
Sometimes, you will find that you can’t create new reports or
print existing ones. There are a number of possible reasons for
this, and most have to do with how Access communicates with your
default printer. Because the printer driver is used in both Design
and Preview mode, faulty communication will prevent you from
printing, designing or previewing reports. Try this:
- If you don’t have a printer installed, do so. Access
requires a valid printer driver to be installed before you can
design or preview reports.
If you have previously been able to preview and design reports in
your database, and now you can’t try this:
- In the Printers dialog (Start > Printers and Faxes in
Windows XP; Start > Control Panel > Hardware and Sound > Printer
in Windows Vista), select the default printer and go to its
- The paper tray is most likely set to Auto. Change it
instead, to one of the numbered paper trays. In many cases this
will solve the problem.
MDE files are compiled versions of Access databases that prevent
users from changing the design of forms, reports and code modules.
If you create a multi-user Access database, providing users with an
MDE version will prevent them from making unwanted changes to the
application. To create an MDE, do the following:
- Split the database into a back-end (tables and
relationships) and front-end (everything else). There is a
wizard that does this for you; run it by going to Tools >
Database Utilities > Split Database and follow the prompts.
- On the front end, go to Tools > Database Utilities > Convert
Database and select the highest available version. If you have
Access 2003, for example, and you have a database in the 2000
format, you will be unable to create an MDE until you convert
it to the 2003 format (the command will be greyed out). There is
no need to upgrade the back end database; your updated front end
database will still be able to use it without problems. In fact,
upgrading the back end may prevent users of earlier versions
from using the data.
- Now, go into any code module (Alt+F11) and select Debug >
Compile. Fix any errors until the code compiles.
- Now, go back to Tools > Database Utilities > Create MDE
File. Follow the prompts in the wizard. When finished, you will
have an MDE version of the front end that you can distribute to
- Put the MDB version aside: this is your development copy.
You will be unable to make design changes without this copy.
If a database needs maintenance (for example, making design
changes to the back end, backing up or performing a compact and
repair), you need to ensure that you are the only user in the
system. With more than two or three users it can be difficult to
contact everybody and ask them to please get out for a few minutes.
If somebody goes out to lunch or leaves for the day while still
logged in to the database, you can’t do much about it.
That’s the reason for this technique. With a hidden form that
checks for the presence of a specific text file, you can give users a
bit of warning and then force them to quit the database. This can
simplify your job without causing your users undue frustration, if
you use it sparingly.
Note: This tip has been updated so that the code works in all
versions of Access. The previous code for the monitor form used the
Application.Filesearch method which no longer works in Office 2007;
now it uses the Windows Dir function instead.
|The logic is shown in the adjacent diagram
The startup form
Assuming that your database has a startup form (in this case,
frmMenu), you need to put this line of code into the form’s Open
DoCmd.OpenForm "frmMonitor", , , , , acHidden
This will launch frmMonitor in the background. Note: If you are
unsure how to create the code for an event, check the
tip at the top of this page.
The monitor form
The monitor form is very simple. It has some code in the Timer
event, which is triggered when the Timer Interval is reached.
Because the Timer operates in milliseconds, a value of 60000 in the
Timer Interval will trigger the Timer event once a minute. Don’t use
a comma when entering the Timer Interval value.
Private Sub Form_Timer() 'searches for DataBaseOn.txt every minute. 'if not found, quits Access Dim fn fn = Dir(CurrentProject.Path & "DatabaseOn.txt") If fn = "" Then 'display a form with a message DoCmd.OpenForm "frmClock", , , , , acDialog 'get out Application.Quit End If End Sub
This code uses Dir to look in the same folder
as the current database for a file called DataBaseOn.txt. If it
finds the file, nothing happens. If not, frmClock is launched; this
counts down from 15 seconds. Once it gets to 0, the form closes and
so does the database. Some points to note:
- If you only have one copy of the front end database, place
the text file in the same folder. If you have given each user a
copy of the database you need to place the text file in a
network directory that all users will have permission to access.
The directory that holds the back end database is a good option.
- To trigger the shutdown, change the name of the text file to
DataBaseOff.txt. Next time frmMonitor runs its check, it will
launch the countdown timer.
- By opening frmClock as a Dialog form, we force frmMonitor to
wait until frmClock closes. Once that happens, the application
The clock form
frmClock has a label called lblClock, with the caption “This
database will be closing in 15 seconds”. The following code changes
the caption on the label, and closes the form when you get to 0. By
setting the Timer Interval to 1000, the caption updates every
second. This is the full code for the form:
Option Compare Database Option Explicit Dim iCounter As Integer Private Sub Form_Load() iCounter = 15 End Sub Private Sub Form_Timer() If iCounter > 0 Then iCounter = iCounter - 1 If iCounter = 1 Then Me.lblClock.Caption = "This database will be closing in " _ & iCounter & " second" Else Me.lblClock.Caption = "This database will be closing in " _ & iCounter & " seconds" End If If iCounter = 0 Then DoCmd.Close acForm, Me.Name, acSaveYes End Sub
That’s it. Two forms, some generic code, and you can remove a
major source of frustration. Adjust the intervals to suit your