< Back

Table of Contents

Apply ‘After Update’ Event Procedure

Create a Splash Screen

Create an Audit Trail of Changes

Validate Data

Automatically open a combo box.

Bold a Control

Valuable Access Keyboard Shortcuts

Open Outlook from an Access Form

Toggle a Form between View-Only and Edit Mode

A well-designed form can make data entry easier and ensure that the data is consistent- with a minimum of errors. The following is a collection of tips, sample code, and excerpts of articles that have helped us design and enhance complex database applications.

These tips can be implemented with a minimum of technical or programming knowledge. In most cases, you just have to enter the code presented and change the actual control, query, table and form names to match your database.

Apply an ‘After Update’ event to find a selected record.

Use the Bookmark function to move to the selected record within your database.
Create a Combo box in the Header section of your form; name it SelectCustomer.
As the Record Source, create a query to display a list of your customers.
Enter the following code in the AfterUpdate Event Procedure. This uses the bookmark function to find the selected name. Since it does not require a filter, after moving to the selected record you can use the navigation buttons to move forward and backward within your full database.

******Start Code ***********

Private Sub SelectCustomer_AfterUpdate()

On Error GoTo Err_SelectCustomer_AfterUpdate

' Find the record that matches the control.

Me.RecordsetClone.FindFirst "[AttendeeID] = " & Me![SelectCustomer]

Me.Bookmark = Me.RecordsetClone.Bookmark

SelectCustomer = ""

Prefix.SetFocus

Exit_SelectCustomer_AfterUpdate:

SelectCustomer.SetFocus

Exit Sub

Err_SelectCustomer_AfterUpdate:

MsgBox "Enter first several letters of last name."

Resume Exit_SelectCustomer_AfterUpdate

End Sub

*******End Code ***********

You can also use the 'Apply Filter' function to find several records that match the criteria. For example, you can find all records from the selected company.

Create a Combo box in the Header section of your form; name it SelectCompany.
As the Record Source, create a query to display a list of company names from your contact list.
Enter the following code in the AfterUpdate Event Procedure. This uses the ApplyFilter function to find all records for the selected company. Since it is filtering your dataset, after moving to the first record found you can use the navigation buttons to move forward and backward within the filtered results.

'*******Start Code ***********

Private Sub SelectCompany_AfterUpdate()

On Error GoTo Err_SelectCompany_AfterUpdate

DoCmd.ApplyFilter , "CompanyName = Forms!Attendees!SelectCompany"

SelectCompany = ""

Prefix.SetFocus

Exit_SelectCompany_AfterUpdate:

SelectCompany.SetFocus

Exit Sub

Err_SelectCompany_AfterUpdate:

MsgBox "Enter first several letters of company name."

Resume Exit_SelectCompany_AfterUpdate

End Sub

'*******End Code ***********

Return to Top

Validate Data

First create the following macro called ValidateID

Condition:     DLookup("[ProductID]","[Products]","[ProductID]=Form.[ProductID]") Is Not Null
Action:          MsgBox
Argument:    Message: The ProductID you entered already exists.
Action:          CancelEvent

On the Products form, set the BeforeUpdate property of the ProductID control to: Products.ValidateID.

Return to Top

Automatically open a combo box.

This is a simple, but very useful enhancement. You can make your combo box ‘open’ every time the cursor moves into the field – this eliminates having to click the down-arrow.

Just add “comboName.Dropdown” as a OnGotFocus event procedure.

This predesigned sample database is included on our Access Developer CD.

Return to Top

Bold Control

Did you know you can Bold (or un-Bold) the text of a control based on a condition.

For example, the following code will make the text of the Payment field Bold if the value of the field is greater than zero. Insert this code in the After Update event and the After Change event.

 If [Payment] > 0 Then
  Control.FontWeight = 700
Else
  Control.FontWeight = 400
End If

Return to Top

Valuable Keyboard Shortcuts For Access

[F11]: Opens the database window in your database.

[F2]: Toggles you in and out of Edit mode. If you press [Tab] to

navigate to a control on a form or to a cell on a datasheet, any

existing data is already selected. If you start typing, that data will

go away. To get out of Edit mode, just press [F2] again.

[F4]: Opens a combo box list.

[Ctrl]+[F2]: This triggers the builder button from the Properties window. You'll only see this particular button when you click a specific property and it has three little dots displayed.

[F6]: Moves you between panes in the Design view of a table.

[Shift]+[F2]: Opens the Zoom dialog box. You can also open this

window via the shortcut menu in most spots.

[Shift]+[Arrow key]: To select items with the keyboard, instead of "clicking and dragging" with the mouse.

Return to Top

Open Outlook from a Form.

You can create a command button to activate Outlook and send the text in the field called “Emailname” to the “To” field in outlook.

Make sure the field that holds the emailname is a text field not a hyperlink.
Create a form and place the Emailname field on it.
In the Form OnCurrent event add the following code:

‘*****Paste these into your form event ********

Private Sub Form_Current()

On Error Resume Next

EnableEmail

End Sub

Private Sub EnableEmail()

' Update the email button

On Error Resume Next

cmdEmail.HyperlinkAddress = "mailto: " & Me!Emailname 'this links the command button to the emailname field

End Sub

‘********* end *********

Add a command button on the form (name it cmdEmail). You will click this to send the email.
In the OnClick event of the command button add the following code:

‘ ******start code *******

Private Sub cmdEmail_Click()

On Error GoTo HandleErr

' Save the record

If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord

' Make sure the hyperlink property is current

EnableEmail

' We don't actually have to do anything here. The button uses its own

' hyperlink property to send mail, as specified in the Form_Current

ExitHere:

Exit Sub

HandleErr:

Select Case Err

Case Else

MsgBox Err & ": " & Err.Description, _

, "Form_Instructors.cmdEmail_Click"

End Select

Resume ExitHere

End Sub

‘ ****end ***************

(Note: You must have the reference to Outlook Object 9.0 Library set. Open any module in design mode, click Tools | References and make sure Microsoft Outlook Object 9.0 Library is checked. Close and Save)

Return to Top

Toggle a Form between View-Only and Edit Mode

There are four steps to creating this function.

1. First paste the following code into a new module:

Option Compare Database

Option Explicit

Global Const cTeal As Long = 8421440

Global Const cGrey As Long = 12632256

Function ToggleEdit(frm As Form, bEdit As Boolean)

On Error GoTo err_ToggleEdit

' toggle edit and backcolor

If bEdit Then

With frm

.AllowAdditions = True

.AllowDeletions = True

.AllowEdits = True

.Detail.BackColor = cTeal

End With

Else

With frm

.AllowAdditions = False

.AllowDeletions = False

.AllowEdits = False

.Detail.BackColor = cGrey

End With

End If

exit_ToggleEdit:

Exit Function

err_ToggleEdit:

MsgBox "Err Number " & Str(err) & ": " & err.Description

Resume exit_ToggleEdit

End Function

2. Second paste the following in the Form Properites Declaration:

Option Compare Database

Option Explicit

Dim bEdit As Boolean

Dim x As Integer

3. Paste the following code in the Form Open event:

Private Sub Form_Open(Cancel As Integer)

bEdit = False

x = ToggleEdit(Me, bEdit)

bEdit = Not bEdit

End Sub

4. Create a command button called: cmdModify. Place the following code in the OnClick event of the button:

Private Sub cmdModify_Click()

On Error GoTo Err_cmdModify_Click

If bEdit Then

cmdModify.Caption = "View All Records"

x = ToggleEdit(Me, bEdit)

'filter records

DoCmd.ApplyFilter , "PK = " & Me!PK

Else

cmdModify.Caption = "Modify Current Record"

x = ToggleEdit(Me, bEdit)

'unfilter records

DoCmd.ShowAllRecords

End If

bEdit = Not bEdit

Exit_cmdModify_Click:

Exit Sub

Err_cmdModify_Click:

MsgBox err.Description

Resume Exit_cmdModify_Click

End Sub

Return to Top

How to Create an Audit Trail of Record Changes in a Form

To create an audit trail of changes to a form, follow these steps,

Open the sample database Northwind.mdb.
Create a module and type the following line in the Declarations section if it is not already there:

Option Explicit

Type the following procedure:

Function AuditTrail()
On Error GoTo Err_Handler
    Dim MyForm As Form, C As Control, xName As String
    Set MyForm = Screen.ActiveForm
    'Set date and current user if form has been updated.
    MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
    "Changes made on " & Date & " by " & CurrentUser() & ";"

    'If new record, record it in audit trail and exit sub.

    If MyForm.NewRecord = True Then
        MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
        "New Record """
    End If

    'Check each data entry control for change and record
    'old value of Control.
    For Each C In MyForm.Controls

    'Only check data entry type controls.
    Select Case C.ControlType
        Case acTextBox, acComboBox, acListBox, acOptionGroup

            ' Skip Updates field.
            If C.Name <> "Updates" Then

            ' If control was previously Null, record "previous  value was blank."
                If IsNull(C.OldValue) Or C.OldValue = "" Then
                    MyForm!Updates = MyForm!Updates & Chr(13) & _
                    Chr(10) & C.Name & "--previous value was blank"

                ' If control had previous value, record previous value.
                ElseIf C.Value <> C.OldValue Then
                    MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
                    C.Name & "==previous value was " & C.OldValue
                End If
            End If
        End Select
    Next C

TryNextC:
    Exit Function

Err_Handler:
    If Err.Number <> 64535 Then
        MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description
    End If
    Resume TryNextC

End Function

Save the Module as Module1 and close the Visual Basic Editor.
Open the Customers table in Design view and add a new field called Updates. Set the data type of the field to Memo. Close and save the table.
Open the Customers form in Design view.
In the BeforeUpdate event of the form, type =AuditTrail().
If the field list is not displayed, click Field List on the View menu and drag the Updates field from the field list to the form.
Open the form in Form view, make a change to the Company Name field of the current record, and press SHIFT+ENTER to save the record.

Note that the Updates field has an entry showing the change that you made to the Company Name field. You can also hide the Updates field if you do not want to see it on the form.

Additional query words: Tracking Information date time stamp

Return to Top

Create a splash screen
Create a splash screen that pops up when a user opens your database and then disappears after three seconds.

Create a new form called Splashfrm.
Add the text or images that you want to display when this form "splashes" onto the screen. Save the form.
Choose Tools | Startup from the menu bar. Select Splashfrm from the Display Form drop-down list, and click OK.
Create a macro called CloseSplashmac. It needs only one action: Close. In the Object Type field, select Form.
Select Splashfrm from the Object Name field.
In the Save field, select No. Then save the macro.
Open Splashfrm in Design view and open the Properties window.
On the Event tab, set the On Timer property to CloseSplashmac.
Set the Timer Interval property to 3000. Then save your changes.

Return to Top