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.

Creating code for an event

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
  [Event Procedure].
• 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
argument:

  DoCmd.OpenForm "frmDetail", acNormal,,strWhere

For a more detailed description of the arguments in the OpenForm
method, and a number of examples, see
this page.

Troubleshooting: Cannot design
reports

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
  Properties.
• 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.

How to create an MDE file

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
  your users.
• Put the MDB version aside: this is your development copy.
  You will be unable to make design changes without this copy.

Booting users out of the database

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
event:

 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
  quits.

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
needs.

 
Home | About |
Links |

Contact | Excel |
Access