Miscellaneous Microsoft Access Tips

(Updated 2010/09/27)

Progress Bar

What Microsoft forgot on command buttons on forms when you've added/changed data.

When you add a command button to a form to do another action weird things can happen. If you

The above all have the same cause.  The record is not saved to the table before the user clicked on the command button.  You can verify this by viewing the pencil indicator on the record selector.  (A small box or bar to the left of a record that you can click to select the entire record in Datasheet view and Form view.   This may be turned off using the forms Record Selectors  properties)

1) When closing a form and  you have some field or table level validation rules, such as a required field or a relational integrity problem Access will happily close the form without giving you any warning.  Another example is where the default value of a foreign key has been set to 0 and the user hasn't entered any data in that field's combo box.

In the code behind the CloseForm button or in the OnClose event insert a

if me.dirty = true then _
     me.dirty = false

before the close form statement. This will then trigger an error message warning the user there is a problem and halt the closing of the form.

2) In the code behind the buttons which open other forms or reports insert the above lines to the top of the code before the line which opens the form.

Note to Access 97 users.  You could use the

DoCmd.RunCommand acCmdSaveRecord

statement however, in Access 2000 and newer, if the record does not require saving you will get an error message.

What Microsoft forgot in wizard generated code on reports

One feature in the forms wizard I use all the time is the feature to "Open the form and find specific data to display."  Why Microsoft forget this when it comes to reports I have no idea.  Seems to me it would've been a cut and paste feature.  Quite painless.

If you are trying to create a RTF, PDF, Excel or other file for emailing or other such purposes then this approach won't work.  Instead see my Emailing reports as attachments page.

Fortunately you can do one of two things.

1) Create a command button calling a form and fill in the data at the "Which fields contain matching data the button can use to look up information?" form  Follow this wizard path through to it's conclusion.

Then create a command button calling the report, preferably in preview mode.  Especially when testing.

Right click on the first command button, click on "Build Event" and find the lines which look like the following bolded lines.

Dim stLinkCriteria As String

stDocName = "<You should see your report name here"

stLinkCriteria = "[fID]=" & Me![fID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Now locate the code which is behind the report preview button.   It will contain something like the following:

Dim stDocName As String

stDocName = "FAQ report"
DoCmd.OpenReport stDocName, acPreview

Add the above bolded lines to this code so that it now looks like the following:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FAQ report"
stLinkCriteria = "[fID]=" & Me![fID]
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Note that there are two comma's after the acPreview and before the stLinkCriteria.

The fID field, or primary key, must be defined in the query upon which the report is based.

2) Create a Report Preview command button yourself and add the above lines customizing as appropriate.

Allowing the user to efficiently use percentage fields

To minimize data entry work by the users I put the following code behind the AfterUpdate event of every percentage field.  I assume that any number entered which is larger than +/- 1 is actually the left hand side of the percentage.  I.e. user entered 12.5 which is then displayed as 12.5% which is stored internally as .125.

On Error GoTo tagError
    If Abs(Me!jodaDiscountAdderPercent) > 1 Then _
    Me!jodaDiscountAdderPercent = Me!jodaDiscountAdderPercent / 100
Exit Sub

tagError:
    MsgBox Err.Description
Exit Sub

Note that if you are using the results of this percentage elsewhere in logic where you are referencing the field in a recordset do ensure that you save the record first before continuing.  I wasted twenty minutes in some complex VBA code trying to figure out why 30% and 20% were adding up to 2030%.  <smile>

Also note that this assumes the user will never have percentages greater than 100%.  After all if the user puts in 1.25 meaning 125% then it will get reduced to 1.25% which is not at all what they meant.

Sorting a report on demand

This topic is, in my not so humble opinion, poorly documented and with a lousy example in the online help.  So in the reports OnOpen event see the following example

Select Case Forms![Global Options]!jiPriorityHandlingType
Case 1 ' Work Pkg prty
    lblTitle.Caption = "Spool Control and Status Log - unshipped only, by Work Pkg priority"
    Me.GroupLevel(2).ControlSource = "AcornCtrlNo"
    Me.GroupLevel(3).ControlSource = "SpoolNo"
    Me.GroupLevel(4).ControlSource = "SpoolNo"
Case 3 ' Wrk Pgk/Acorn Priority
    lblTitle.Caption = "Spool Control and Status Log - unshipped only, by Work Pkg/Acorn priority"
    Me.GroupLevel(2).ControlSource = "AcornPrioritySequence"
    Me.GroupLevel(3).ControlSource = "AcornCtrlNo"
    Me.GroupLevel(4).ControlSource = "SpoolNo"
Case Else ' 2 actually is a somewhat different format of report.
    MsgBox "This report doesn't support the selected Job Priority type. "
End Select

Note that this in this I've left GroupLevel(0) and GroupLevel(1) alone as they the Work Package Priorty and Work Package and are common to both reports.

This method also requires that the group levels already exist on the report.  You can't add them unless you're in design view which can be impractical and impossible on an MDE.

Resume while debugging

Place the following bolded line after your error trapping.

tagError:
    msgbox err.description
    exit sub
    resume
end sub

The resume line will never be executed unless you right click on it and tell VBA to resume execution at that line.  You will then be returned to the line which caused the error.

I frequently don't put error trapping myself until a routine is mostly completed and then this little trick helps.

How do I maximize the report preview after it's opened?

After the following line:
    DoCmd.OpenReport stDocName, acPreview
Insert
    DoCmd.Maximize
    DoCmd.RunCommand acCmdFitToWindow

Don't use environment variables such as Environ("username")

They can be changed by the user using a command prompt. Use alternatives where possible.  Especially for the username and computername variables.  Instead use the API calls
Network User Name - Retrieve the network login ID of the user.
Current Computer Name - Retrieve the network name for the local machine

[ Access Tips | Access | Main ]

   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available