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