Chapter 8: Using Code to Add Advanced Functionality to Forms (1 of 2)

  • Article

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

This article is an excerpt from Expert Access 2007 Programming by Rob Cooper and Michael Tucker from Wrox(ISBN 978-0-470-17402-9, copyright Wrox 2007, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

Next Part: Chapter 8:Using Code to Add Advanced Functionality to Forms (2 of 2)

Aside from the data itself, forms are another key part of an application. Many Microsoft Access developers tend to prevent users interacting with the tables directly, which makes forms the primary mechanism for presentation for data in the application. Unlike reports, forms also provide users with the ability to update data, making them the primary means for interacting with the data as well.

Because forms represent the user interface of the application, what you can do with them is virtually unlimited (or at least within the amount of hours you can bill if you're a consultant). Regardless of the layout and presentation, however, the ultimate goal for creating forms is to create something for the user that is both useful and enjoyable. Because the requirements for users and the application itself can greatly vary from one application to the next, this is an open statement. Keeping issues of style aside, however, there are several form-related features found throughout database applications and this chapter focuses on those. More specifically, in this chapter you will:

  • Work with events on forms to perform tasks such as validating data and moving a borderless form

  • Work with events on controls to perform tasks such as working with list boxes to present usable interfaces, creating custom progress bars, and to validate data

  • Learn how to create common types of forms such as menus for navigation, splash screens, and dashboards

  • See different ways you can create navigation experiences for your users

Important noteImportant

The code marked as Visual Basic in this chapter is written in Visual Basic for Applications (VBA).

Contents

  • Working with Form Events

  • Working with Controls

Working with Form Events

Events are one of the key components of Windows programming, and Access forms take full advantage of them. Access forms provide events that enable you, as the developer, to manipulate both the data and the user interface (UI). This section discusses some of the different events you can use at the form level in Access 2007.

How to Determine When Data Is Added

The two events on a form that are related to inserting data are BeforeInsert and AfterInsert. The BeforeInsert event fires when the user types the first character in a new record, and the AfterInsert event fires after a new record has been added to the form. As with the other Before events in Access, the BeforeInsert event handler includes a Cancel argument to cancel the event if needed.

Let's say that you are creating a form that tracks orders and to receive credit for placing the order, you want to know who placed the order. The BeforeInsert event can be used to fill a control with the logon name of the user as shown here.

(Visual Basic for Applications)

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.txtPlacedBy = Environ(“USERNAME”) 
End Sub

The AfterInsert event can be used to take an action after a new record has been added. For example, let's say that you sell books online and want to send an e-mail to customers who place orders with you as confirmation. Here's how you could do this using the AfterInsert event.

(Visual Basic for Applications)

Private Sub Form_AfterInsert()
    ' send mail after an order is placed 
    Dim strTo As String 
    Dim strSubject As String 
    Dim strBody As String

    strTo = Me.EmailAddress
    strSubject = “Order Confirmation - Order Number: “ & Me.[Order ID]
    strBody = “Thank you for your order!%0a%0d” & _ 
              “Order Date: “ & Me.[Order Date] & “%0a%0d” & _ 
              “Shipping Date: “ & Me.[Shipped Date] & “%0a%0d” & _ 
              “Ship To: “ & “%0a%0d” & _ 
              Me.[Ship Name] & “%0a%0d” & _ 
              Me.[Ship Address] & “%0a%0d” & _ 
              Me.[Ship City] & “, “ & Me.[Ship State/Province] & “ “ & _
              Me.[Ship ZIP/Postal Code]
    SendEmail strTo, strSubject, strBody
End Sub

We're using the SendEmail routine defined in Chapter 2 to create the e-mail. Here is that code as a reminder.

(Visual Basic for Applications)

Private Declare Function ShellExecute Lib “shell32.dll” Alias “ShellExecuteA” ( _ 
  ByVal hWnd As Long, _ 
  ByVal lpOperation As String, _ 
  ByVal lpFile As String, _ 
  ByVal lpParameters As String, _ 
  ByVal lpDirectory As String, _ 
  ByVal nShowCmd As Long) As Long 
Private Const SW_SHOW As Long = 5 
Sub SendEmail(strTo As String, strSubject As String, strBody As String) 
    Dim rc As Long 
    Dim strFile As String
    ' build the lpFile argument
    strFile = “mailto:” & strTo
    strFile = strFile & “?subject=” & strSubject
    strFile = strFile & “&body=” & strBody
    rc = ShellExecute(0, “open”, strFile, “”, “”, SW_SHOW)
End Sub

How to Determine When Data Is Changed

The AfterUpdate event of a form fires when the value of any control in a record is changed. There are many scenarios in which you might use this event. Here are some of them:

  • Creating a change history of data in a table

  • Updating some other information on screen such as status or calculations

  • Showing or hiding controls

  • Synchronizing record values between databases

  • Synchronizing records in a subform

  • Writing the results of a query to a file

Let's say that by default you provide a read-only view of data in a form by setting the AllowAdditions and AllowEdits properties of the form to False. On the form, you might include a button that sets these properties to True to allow users to edit a record. Use the AfterUpdate event of the form as follows to lock the form after the user commits a change to the data.

(Visual Basic for Applications)

Private Sub Form_AfterUpdate():
    Me.AllowEdits = False
    Me.AllowAdditions = True
End Sub

How to Determine When Data Is Deleted

Access fires one of three different events when records are deleted. The Delete event fires for each record that is being deleted. The BeforeDelConfirm event fires when Access is about to delete records and prompts you to do so. And the AfterDelConfirm event fires after the records have been deleted (or the deletion was cancelled.

Of the three, the Delete event is perhaps the most useful. This event includes a Cancel argument so that it can be canceled based on some criteria. In addition, because it fires for each record that is being deleted, you can also use this event to mark records for deletion instead of actually deleting them. Many types of applications have requirements about how data is handled and deletions are an important aspect of such applications. Say that you have a table with a Yes/No field called MarkedForDeletion. The following code in the Delete event of a form shows you how to update this field and cancel the actual deletion.

(Visual Basic for Applications)

Private Sub Form_Delete(Cancel As Integer) 
    ' mark records for deletion
    Dim stSQL As String
    stSQL = “UPDATE tblCustomersDeleteEvents SET MarkedForDeletion=True “ & _
            “WHERE ID=” & Me.ID

    ' run the sql
    CurrentDb.Execute stSQL

    ' cancel the delete
    Cancel = True

    ' refresh
    Me.Refresh
End Sub

Validating Form Data

One of the more important tasks you're likely to do in a form is to validate data. For this you need the BeforeUpdate event of the form. If you want to validate data at the control level use the BeforeUpdate event of the control instead.

Because validation may fail for multiple controls when using the BeforeUpdate event of the form, it might be helpful to notify the user of all places where validation has failed. This prevents them from seeing an error message on individual controls until validation succeeds. As with most things in Access, there are a number of ways you can do this. We like to give visual cues for the controls where validation has failed rather than a list in a message box so that the user doesn't have to write down the controls. We sometimes include error text in the control itself or in another text box that displays the errors.

Using our examples of Orders from earlier, create a new form based on the Orders table in the Northwind 2007 sample database. In order for an order entry to be valid in this form, it must meet the following criteria:

  • The order date cannot be in the past.

  • The shipped date must be greater than or equal to the order date.

  • When the payment type is specified, the paid date must be specified.

  • When the paid date is specified, the payment type must be specified.

To validate these criteria, use a function called IsValidData, as shown in the code that follows. This function returns a Boolean value that determines whether the data on the form is valid. You start out by assuming that all data is valid and initialize a flag to True. When you hit an invalid case, set it to False.

(Visual Basic for Applications)

Private Function IsValidData() As Boolean
    Dim fValid As Boolean
    Dim iRule As Integer

    ' assume all is good
    fValid = True

    ' contains the validation data for the form
    ResetControlFormatting
    ' if the payment type has been set, make sure the paid date is set
    If (Not IsNull(Me![Payment Type]) And IsNull(Me![Paid Date])) Then
        Me.[Paid Date].BackColor = vbYellow
        fValid = False
    End If

    ' if the paid date is set, make sure the payment type is set
    If (Not IsNull(Me![Paid Date]) And IsNull(Me![Payment Type])) Then
        Me.[Payment Type].BackColor = vbYellow
        fValid = False
    End If

    ' make sure shipped date is > order date
    If ([Shipped Date] < [Order Date]) Then
        Me.[Shipped Date].BackColor = vbYellow
        fValid = False
    End If

    ' make sure order date is >= today's date
    If ([Order Date] < Date) Then
        Me.[Order Date].BackColor = vbYellow
        fValid = False
    End If

    ' return
    IsValidData = fValid
End Function

If you encounter invalid criteria, set the BackColor property of the control to yellow using the vbYellow constant. Because controls may have this property set before validation, you need some way to reset the back color. Add the following routine to the form to reset this property for combo boxes and text boxes.

(Visual Basic for Applications)

Private Sub ResetControlFormatting()
    Dim c As Control

    ' reset control backcolor
    For Each c In Me.Controls
        If (c.ControlType = acTextBox Or c.ControlType = acComboBox) Then
            c.BackColor = vbWhite
    End If
    Next
End Sub

Now, to prevent an update to the database with invalid data, add the following code to the BeforeUpdate event of the form. This code sets the Cancel parameter value of the event handler based on the return value of the IsValidData function.

(Visual Basic for Applications)

Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' validate data in the form
    Cancel = Not IsValidData()
End Sub

There may still be controls that have the yellow back color set if a user presses the escape key to undo their changes. Add the following code to the Undo event of the form to reset the BackColor property.

(Visual Basic for Applications)

Private Sub Form_Undo(Cancel As Integer)
    ResetControlFormatting
End Sub

The Undo event fires when changes are undone on the form.

Suppressing Access Error Messages

When you're working with data in a form, several data-related errors can occur. For example, errors can occur if the user enters a duplicate primary key, or violates a validation rule, or hits a write conflict. An error may also occur if the user tries to delete records in a parent table and records cannot be deleted from a child table because cascade delete is not enforced. In many cases, the error messages provided by Access are long and difficult to read. Use the Error event of the form to provide your own error message and take action based on a particular error, such as setting focus back to a control.

Using the Northwind 2007 sample database, create a new form based on the Customers table. This table is related to the Orders table and the cascade delete option is not set for the relationship. If there are related records in the foreign table, you get an error if you try to delete records in the parent table. The following code shows you how to use the Error event of the form to provide your own error message for this scenario.

(Visual Basic for Applications)

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Const ERR_RELATED_RECORDS As Long = 3200 ' Cannot delete related records
    Const ERR_PRIMARYKEY As Long = 3022 ' Primary key violation
    Const ERR_VALIDATION_RULE As Long = 3317 ' Validation rule violation
    Const ERR_INVALID_INPUTMASK As Long = 2113 ' Invalid entry for input mask

    Dim stMsg As String
    Dim fHandled As Boolean
    ' assume we're handling the error
    fHandled = True
    Select Case DataErr
        Case ERR_INVALID_INPUTMASK
            stMsg = “Invalid input mask”
        Case ERR_PRIMARYKEY
            stMsg = “You have entered a duplicate primary key”

            ' put focus back in the field
            Me.ID.SetFocus
        Case ERR_RELATED_RECORDS
            stMsg = “The company [“ & Me.Company & “] has related orders. “ & _
                    “Please delete these orders prior to deleting the company.”
        Case ERR_VALIDATION_RULE
            stMsg = “Invalid data has been entered”
        Case Else
            ' unknown - let Access handle it
            fHandled = False
    End Select
    ' hide Access' error message and show our own
    If (fHandled) Then
        Response = acDataErrContinue
        MsgBox stMsg, vbExclamation
    End If
End Sub

Notice that we're handling several errors here as defined by the constants in the code. We're also using a flag called fHandled to indicate whether we are handling the error or if Access should handle it. When we handle a given error, we've set the Response argument of the event to acDataErrContinue. This tells Access not to display its error message.

How to Determine If a Modifier Key Is Pressed

There might be times when you want to process keystrokes on a form. For example, you might want to provide custom navigation using arrow keys or handle keystrokes that the autokeys macro does not handle. Because the autokeys macro enables you to specify modifier keys (Alt, Shift, and Control), you need some way to do this with a form as well.

Forms include an event called KeyDown that includes an argument called Shift. This argument is a bitmask that includes the different modifier keys. Because this is a bitmask, the following code tests for the different modifiers.

(Visual Basic for Applications)

fIsShiftSet = ((Shift And acShiftMask) = acShiftMask)
fIsAltSet = ((Shift And acAltMask) = acAltMask)
fIsCtrlSet = ((Shift And acCtrlMask) = acCtrlMask)

Let's say that you want to enhance the selection semantics for a form in datasheet view to make it easier to copy data to the clipboard. The semantics to add are: Shift+Ctrl+Down Arrow to select an entire column, Shift+Right Arrow to select a row, Ctrl+Right Arrow to move to the last field, and Ctrl+Left Arrow to move to the first field. Add the following code to the KeyDown event of a form in datasheet view to enable these semantics.

(Visual Basic for Applications)

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    If (KeyCode = vbKeyRight And ((Shift And acShiftMask) = acShiftMask)) Then
        ' select an entire row: Shift+Right
        Me.SelLeft = 1
        Me.SelWidth = Me.Recordset.Fields.Count ' # of fields in the datasheet
    ElseIf (KeyCode = vbKeyDown And ((Shift And acShiftMask) = acShiftMask) And _
        ((Shift And acCtrlMask) = acCtrlMask)) Then
        ' select an entire column: Shift+Control+Down
        Me.SelTop = 1
        Me.SelHeight = Me.Recordset.RecordCount ' Records in the datasheet
    ElseIf (KeyCode = vbKeyRight And ((Shift And acCtrlMask) = acCtrlMask)) Then
        ' move to the last field: Control+Right
        Me.SelLeft = 10
    ElseIf (KeyCode = vbKeyLeft And ((Shift And acCtrlMask) = acCtrlMask)) Then
        ' move to the first field: Control+Left
        Me.SelLeft = 1
    End If
End Sub
Important noteImportant

Be sure to set the KeyPreview property of the form to Yes or this event will not fire.

Windows defines key codes for a given keyboard using what is called a virtual key code. These codes are defined in VBA in the KeyCodeConstants module. This module in VBA defines the vbKeyDown and vbKeyRight constants in the previous code.

The Difference Between KeyDown and KeyPress

You've probably noticed that there are two events in Access that appear to be similar in nature: KeyDown and KeyPress. While it's true that both events fire when the user presses a key on the keyboard, the similarity ends there. The KeyPress event only responds when ASCII keys are pressed on the keyboard, hence the name of the argument for its event handler: KeyAscii. The KeyDown event on the other hand is considerably more granular — it fires for any key pressed on the keyboard, including control keys, such as the arrow keys.

If you need to handle arrow keys or other control keys, use the KeyDown event. If you need to determine whether a modifier is pressed, use the KeyDown event. If you only need ASCII keys, use the KeyPress event.

The other difference between these two events is that the KeyDown event also enables you to suppress keystrokes by setting the KeyCode argument to 0.

Periodic Backups Using the Timer Event

Here's a little confession. When we first discovered the Timer event of a form, we just used it to provide cute little animations and to flash labels on the form. While we still use it to provide animations (more for progress bars these days), we've since discovered that it's far more useful than we originally realized.

It goes without saying that backing up data is pretty important. There may even be requirements for backing up data, depending upon the industry in which you're working. To make sure that data is always backed up, you might consider implementing a backup strategy into your application if you haven't already. The Timer event can be used here to create a backup of the data on a particular form to a file on a periodic basis. This is only a partial solution — it only works when the form is open, but over the course of a day it may help ensure that there is always a backup available.

Add the following code to the Timer event of a form. In this example, the data is backed up to the same file every time the event fires. Depending on your requirements, you might extend this to write data to a different file each time. This code creates a backup of data in a table called Customers. To run this code, you need to have a table with this name or replace the name of the table in the code. You also need to set the TimerInterval property of the form to an acceptable value in milliseconds. To back up data every hour, set the TimerInterval property to 60000.

(Visual Basic for Applications)

Private Sub Form_Timer()
    ' do a periodic backup of records
    Dim stFile As String
    Dim rs As DAO.Recordset2
    Dim fld As DAO.Field2
    Dim stData As String

    ' show status
    Me.lblWait.Visible = True
    DoEvents

    ' open the file name - latest data always available
    stFile = CurrentProject.Path & “\CustomerBackup.txt”
    Open stFile For Output As #1

    ' get the data
    Set rs = CurrentDb().OpenRecordset(“Customers”)
    While (Not rs.EOF)
        ' loop through fields and build the data string
        For Each fld In rs.Fields
            If (Not fld.IsComplex) Then
                stData = stData & fld.Value & “|”
            End If
        Next

        ' print
        Print #1, Left(stData, Len(stData) - 1)
        rs.MoveNext
        DoEvents
    Wend

    ' cleanup
    rs.Close
    Set rs = Nothing
    Set fld = Nothing

    Close #1

    ' hide status
    Me.lblWait.Visible = False
End Sub
NoteNote

When we first started using the Timer event, we ran into a rather interesting problem. Every time we were writing code, IntelliSense would appear and then suddenly go away. Each line of code we typed would turn red as if there were a syntax error. It turned out that there was a Timer event running on an open form. The running timer would close any open windows such as the IntelliSense window in the code module.

Moving a Form Without a Border

Depending on the type of look that you're after for your application, you may choose to create borderless forms. We use borderless forms for About dialog boxes described later in this chapter, but they are also useful for popup types of items, such as calendars and calculators. Without a border, however, it can be difficult to move the form! Fortunately for us, however, Access provides mouse events for forms and controls that can be used to move a form on the screen.

Start by creating a new form and set the Border Style property of the form to None. Next, add the following line of code to the declarations section of the form's module.

(Visual Basic for Applications)

Private fMoving As Boolean

Then, add the following code to the MouseDown event of the Detail section to set the fMoving flag. This begins the move operation.

(Visual Basic for Applications)

Private Sub Detail_MouseMove(Button As Integer, _
    Shift As Integer, X As Single, Y As Single)
    fMoving = True
End Sub

Most of the work happens on the MouseMove event of the Detail section. Here, you're using the window dimensions of the form to determine where to move it. The calculation shown here is designed to prevent the mouse from jumping to the top-left hand corner of the form. Once you've calculated the new position, move the form by calling its Move method.

(Visual Basic for Applications)

Private Sub Detail_MouseMove(Button As Integer, _
    Shift As Integer, X As Single, Y As Single)
    Dim sngNewX As Single
    Dim sngNewY As Single
    If (fMoving) Then
        ' calculate using window dimensions so the mouse doesn't jump
        sngNewX = (Me.WindowLeft - Me.WindowWidth / 2) + X
        sngNewY = (Me.WindowTop - Me.WindowHeight / 2) + Y

        ' move the form
        Me.Move sngNewX, sngNewY
    End If
End Sub

Last, you need to stop the move operation when the mouse is released. To do this, add the following code to the MouseUp event of the Detail section. This resets the flag so that when the MouseMove event fires again, you don't move the form unless you're in a move operation.

(Visual Basic for Applications)

Private Sub Detail_MouseMove(Button As Integer, _
    Shift As Integer, X As Single, Y As Single)
    fMoving = False
End Sub

Run the form and click in the Detail section to begin moving the form.

Customizing ControlTipText

Control tips, or tooltips are used to provide informational text when you hover over an object, such as a control. In many cases, this property contains a static value, but wouldn't it be cool if it could contain actual data? We think it would, so let's take a look.

Because the Current event of the form fires for every record, use this event to bind the ControlTipText property of a control based on values in the form.

(Visual Basic for Applications)

Private Sub Form_Current()
    ' add controltips
    If (Not Me.NewRecord) Then
        Me.[Last Name].ControlTipText = Me.[First Name] & “ “ & _
            Me.[Last Name] & vbCrLf & _
            “Business: “ & Nz(Me.[Business Phone]) & vbCrLf & _
             “Home: “ & Nz(Me.[Home Phone]) & vbCrLf & _
             “Mobile: “ & Nz(Me.[Mobile Phone])
    End If
End Sub

Figure 8-1. This might look something like the form pictured here

The result might look something like this form

Working with Controls

We've taken a look at scenarios using form events, so it's time to move our attention to controls. In this section, we take a look at ways to validate data at the control level, as well as how to use list boxes in advanced scenarios that provide user interfaces that are intuitive. We also discuss creating custom progress bars and the new attachment control in Access 2007.

Validating Control Data

Early in this chapter, we looked at how you could use the BeforeUpdate event of a form to validate all the controls on a form. For some forms, this technique is good but it can make complex forms more difficult for the user. If you want to notify the user that there's a problem in a control without having to validate everything, you use the BeforeUpdate event of a control instead.

The following code shows you how to cancel the BeforeUpdate event based on some criteria.

(Visual Basic for Applications)

Private Sub Taxes_BeforeUpdate(Cancel As Integer)
    ' no taxes for the following states:
    Const LIST_NO_SALESTAX As String = “AK|DE|MT|NH|OR”

    ' get the state for the customer
    Dim stState As String
    stState = DLookup(“State/Province”, “Customers”, “ID=” & Me.[Customer ID])

    ' verify tax is not applied for the defined states
    If (Me.Taxes < 0) Then
        Cancel = True
        MsgBox “Taxes cannot be negative”, vbExclamation
        Exit Sub
    ElseIf (Me.Taxes > 0 And InStr(LIST_NO_SALESTAX, stState) > 0) Then
        Cancel = True
        MsgBox “Cannot apply tax for customer located in: “ & stState, _
            vbExclamation
        Exit Sub
    End If
End Sub

In this case, we're checking two things. First, we check that the amount of the tax is greater than 0 (this should probably be a validation rule in the table). Second, we're retrieving the state for the customer placing the order. If the state where the customer resides does not have a sales tax, then we cancel the event and alert the user.

Disabling all Controls

Rather frequently, we perform an action on all controls on a form. Typically, this is to apply common formatting, such as the BackColor property that you saw earlier, or to disable or enable controls on a form.

The following routine locks or unlocks all bound controls on a form. Notice that this routine includes error handling for the control types that don't support the ControlSource property.

(Visual Basic for Applications)

Private Sub LockControls(fLock As Boolean)
    Dim c As Control
    For Each c In Me.Controls
        On Error Resume Next

        ' lock all bound controls

        If (Len(c.ControlSource) > 0) Then
            c.Locked = fLock
        End If

        On Error GoTo 0
    Next
End Sub

Reusing a Subform Control

Subform controls are really powerful. They are used to show related data on a form or report, but can take up a fair amount of space depending on what you put in them. For that reason, as well as for performance reasons, we like to reuse subform controls to display one subform at a time. Using subforms also enables you to create what we call a frame-based application where the main form is a frame used for navigation or dashboard and the content for the application is switched in a subform.

Figure 8-2. Example of a frame-based application

Example frame-based application

In this application, selecting one of the buttons in the Microsoft Office Fluent user interface Ribbon changes the subform shown in the bottom of the screen. To reuse a subform control, simply set the SourceObject property of the subform control to the name of a form in the database.

Extended List Box Functionality

List boxes are a staple of Access forms. As with combo boxes, they display data from a table or query, a list of values, or the field list from a table or query. In this section, we take a look at how you can extend them to create highly functional user interfaces.

Moving Items Up and Down in a List Box

In Chapter 7, we discuss using a field that defines a custom sort order for items in a list. A list box is a natural approach for displaying these items, but editing the sort order might be difficult. To help make this easier, we'll add up/down buttons to a form, enabling users to move items up and down using these buttons.

Create the Table

Start with a simple categories table, as shown in Figure 8-3. Notice that this table contains a field called SortOrder that defines the order in which items should appear in the list. Save the table as tblCategories.

Figure 8-3. Start with a simple categories table

Simple categories table with a SortOrder field

Create the Form

Next, create a new form named frmCategories and set the Pop Up property of the form to Yes. Add a list box, a text box, and two command buttons to the form. Set the properties of the controls, as shown in the table that follows.

Table 1. Form Controls Properties

Control Name

Property Name

Property Value

List0

Column Count

3

Column Widths

0”;1”;0”

Row Source

SELECT CategoryID, Category, SortOrder FROM tblCategories ORDER BY SortOrder;

Name

lstCategories

ControlTipText

Press {delete} to delete a category

Text2

Name

txtNewCategory

Command4

Caption

Up

Name

cmdMovep

Command5

Caption

Down

Name

cmdMoveDown

Arrange the form so that it resembles the one shown in Figure 8-4.

Let's start by adding code to the Click events of the two buttons. These event handlers call other routines that we define in a moment.

(Visual Basic for Applications)

Private Sub cmdMoveDown_Click()
    MoveCategoryDown
End Sub
Private Sub cmdMoveUp_Click()
    MoveCategoryUp
End Sub

Figure 8-4. Arrange the form to resemble this one

Arrange the form to resemble this one

Because you're handling up and down behavior in the list, you should enable or disable the buttons depending on where you are in the list. Add the following code to the AfterUpdate event of the list box. This code disables the Up button when you're at the top of the list or the Down button when you're at the bottom of the list.

(Visual Basic for Applications)

Private Sub lstCategories_AfterUpdate()
    DoEvents

    ' enable the move buttons if the first or last item has been set
    If (Me.lstCategories.ListIndex = Me.lstCategories.ListCount - 1) Then
        Me.cmdMoveUp.Enabled = True
        Me.cmdMoveUp.SetFocus
        Me.cmdMoveDown.Enabled = False
    Else
        Me.cmdMoveDown.Enabled = True
    End If

    If (Me.lstCategories.ListIndex = 0) Then
        Me.cmdMoveDown.Enabled = True
        Me.cmdMoveDown.SetFocus
        Me.cmdMoveUp.Enabled = False
    Else
        Me.cmdMoveUp.Enabled = True
        End If

    Me.lstCategories.SetFocus
End Sub

When you click the Up button, items move up in the list. This is accomplished by swapping the sort order for the current item and the item above it. Start by adding the MoveCategoryUp routine that is called from the cmdMoveUp button.

(Visual Basic for Applications)

Private Sub MoveCategoryUp()
    Dim lOldOrder As Long
    Dim lNewOrder As Long
    Dim lSelRow As Long

If there is a category selected, first determine the selected row.

    ' move the selected item up
    If (Not (IsNull(Me.lstCategories))) Then
        ' Get the current selected row
        lSelRow = Me.lstCategories.ListIndex

Remember that the sort order is part of the row source for the list box so use the Column property to get the old sort order. The new sort order is stored in the same column but for the previous row.

        If (Not IsNull(Me.lstCategories.Column(2)) And _
            Not IsNull(Me.lstCategories.Column(2, lSelRow + 1))) Then

            ' get the sortorder values for the current row and the next row
            lOldOrder = Me.lstCategories.Column(2)
            lNewOrder = Me.lstCategories.Column(2, lSelRow - 1)

To swap the two sort orders, call a routine called SwapSortOrders that you'll define in a moment.

            ' swap the sort order for the selected item and the one above it
            SwapSortOrders lOldOrder, lNewOrder

Last, requery the list box so it has the current sort orders and refresh the button states by calling the AfterUpdate event handler for the list box.

            ' requery the listbox
            Me.lstCategories.Requery

            ' update the buttons enabled state
            lstCategories_AfterUpdate
        End If
    End If
End Sub

The MoveCategoryDown routine is very similar to MoveCategoryUp except that you want to get the sort order for the next item instead of the previous item.

(Visual Basic for Applications)

Private Sub MoveCategoryDown()
    Dim lOldOrder As Long
    Dim lNewOrder As Long
    Dim lSelRow As Long

    ' move the selected item down
    If (Not (IsNull(Me.lstCategories))) Then
        ' Get the current selected row
        lSelRow = Me.lstCategories.ListIndex

        If (Not IsNull(Me.lstCategories.Column(2)) And _
            Not IsNull(Me.lstCategories.Column(2, lSelRow + 1))) Then

            ' get the sortorder values for the current row and the next row
            lOldOrder = Me.lstCategories.Column(2)
            lNewOrder = Me.lstCategories.Column(2, lSelRow + 1)

            ' swap the sort order for the selected item and the one above it
            SwapSortOrders lOldOrder, lNewOrder

            ' requery the listbox
            Me.lstCategories.Requery
            ' update the buttons enabled state
            lstCategories_AfterUpdate
        End If
    End If
End Sub

To swap the sort orders, you need to update the tblCategories table. Start by defining the SwapSortOrders routine as follows.

(Visual Basic for Applications)

Private Sub SwapSortOrders(lOldOrder As Long, lNewOrder As Long)
    Dim stSQL As String
    Dim lTemp As Long

This is a pretty straightforward swap operation. You first need to save the old sort order.

    ' cache item1
    lTemp = lOldOrder

Next, set the old sort order to a dummy value.

    ' set the item1 sort order to -1 temporarily
    stSQL = “UPDATE tblCategories SET SortOrder = -1 “ & _
            “WHERE SortOrder = “ & lNewOrder
    CurrentProject.Connection.Execute stSQL

Then update the old sort order to the new sort order.

    ' set the new sort order to the old sort order
    stSQL = “UPDATE tblCategories SET SortOrder = “ & lNewOrder & _
            “ WHERE SortOrder = “ & lOldOrder
    CurrentProject.Connection.Execute stSQL

And last, update the dummy value of -1 to the old sort order.

    ' final swap
    stSQL = “UPDATE tblCategories SET SortOrder = “ & lTemp & _
            “ WHERE SortOrder = -1”
    CurrentProject.Connection.Execute stSQL
End Sub

The last thing you want to do is to add a category to the list box using the txtNewCategory text box. If a category already exists, you don't want to add it again so use the BeforeUpdate event of the text box to make this determination.

(Visual Basic for Applications)

Private Sub txtNewCategory_BeforeUpdate(Cancel As Integer)
    ' make sure the new category does not exist
    Dim i As Integer
    Dim stCat As String

    ' replace text
    If (Not IsNull(Me.txtNewCategory)) Then
        stCat = Replace(Replace(Me.txtNewCategory, “'“, “''“), _
            Chr(34), Chr(34) & Chr(34))

        i = Nz(DCount(“*“, “tblCategories”, “Category='“ & stCat & “'“), 0)

        If (i > 0) Then
            MsgBox “The category '“ & Me.txtNewCategory & “' already exists. “ & _
                   “Please specify a new category name.”, vbCritical
            Cancel = True
        End If
    End If
End Sub

Notice that we've used the DCount function to determine whether the specified category already exists. If it does, cancel the event and alert the user.

If the category does not exist, add it to the underlying table with a sort order. To do this, use the AfterUpdate event of the control. Start by adding the event handler.

(Visual Basic for Applications)

Private Sub txtNewCategory_AfterUpdate()
    ' add a new category
    Dim stSQL As String
    Dim stCat As String
    Dim lNewSortOrder As L

Next, do some cleanup of the text.

    If (Not IsNull(Me.txtNewCategory)) Then
        ' replace text
        stCat = Replace(Replace(Me.txtNewCategory, “'“, “''“), _
            Chr(34), Chr(34) & Chr(34))

We need to determine the new sort order based on the maximum sort order in the table and add one to that.

        ' get the new sort order (put the new item at the end)
        lNewSortOrder = Nz(DMax(“SortOrder”, “tblCategories”), 0) + 1

Next, run a SQL statement to add the new category and refresh the controls.

        ' build and run the SQL
        stSQL = “INSERT INTO tblCategories (Category, SortOrder) VALUES ('“ & _
            stCat & “',” & lNewSortOrder & “)“
        CurrentDb.Execute stSQL

        ' update the controls
        Me.lstCategories.Requery
        Me.txtNewCategory = Null
        Me.txtNewCategory.SetFocus
    End If
End Sub

Deleting Items in a List Box with the Delete Key

In the list box created in the previous example, we defined a tooltip that told the user you would handle deleting items in the list using the delete key, so let's do that now. Add the following code to the KeyUp event of the lstCategories list box to handle the delete key.

(Visual Basic for Applications)

Private Sub lstCategories_KeyUp(KeyCode As Integer, Shift As Integer)
    ' trap deletes
    Select Case KeyCode
        Case vbKeyDelete
            DeleteCategory
    End Select
End Sub

Next, define the DeleteCategory routine.

(Visual Basic for Applications)

Private Sub DeleteCategory()
    ' handle deletes
    Dim stCat As String
    Dim stSQL As String
    Dim stMsg As String
    Dim stSelected As String
    Dim lID As Long
    Dim lSelOrder As Long

Start by retrieving the category ID and name from the list box.

    ' get the id and selected item
    lID = CLng(Me.lstCategories.ItemData(Me.lstCategories.ListIndex))
    stSelected = Me.lstCategories.Column(1, Me.lstCategories.ListIndex)

    ' message
    stMsg = “Are you sure you want to delete the category: “ & stSelected

Deleting a category affects sort order. Get the sort order from the list box.

    ' existing sort order
    lSelOrder = Me.lstCategories.ListIndex + 1

Prompt to delete, and if the user selects yes, then delete the category from the tblCategories table.

    If (MsgBox(stMsg, vbExclamation + vbYesNo, “Delete Category”) = vbYes) Then
        ' delete the category
        stSQL = “DELETE * FROM tblCategories WHERE CategoryID = “ & lID
        CurrentDb.Execute stSQL

Then update all of the subsequent sort orders to the current order minus one.

        ' update the sort orders that follow
        stSQL = “UPDATE tblCategories SET SortOrder = SortOrder - 1 “ & _
                “WHERE SortOrder > “ & lSelOrder
        CurrentDb.Execute stSQL

        ' requery
        Me.lstCategories.Requery
        lstCategories_AfterUpdate
    End If
End Sub

Using a Multi-Select List Box for Filter Criteria

Because list boxes provide multiple values, they make a pretty nice choice for a query form. However, the Value property for a multi-select list box is Null so you need to build the criteria dynamically.

Using the Northwind 2007 sample database, start by creating a new form and adding a list box and a subform control. The list box stores the customers in the Customers table and the subform stores the results of the filter. Set the properties of the list box as shown in the table that follows.

Property Name

Property Value

Column Count

2

Column Widths

0"

Row Source

SELECT ID, Company FROM Customers ORDER BY Company;

Name

lstCustomers

Multi Select

Extended

Set the properties of the subform control as follows:

Property Name

Property Value

Source Object

Table.Orders

Name

sfrmResults

Next, add two command buttons to the form named cmdFilter and cmdClearFilter. Add the following code to the cmdFilter button to create the filter for the subform.

(Visual Basic for Applications)

Private Sub cmdFilter_Click()
    Dim stFilter As String
    Dim vItem As Variant

    ' Build the filter string
    For Each vItem In Me.lstCustomers.ItemsSelected
        stFilter = stFilter & “[Customer ID] = “ & _
        Me.lstCustomers.ItemData(vItem) & “ OR “
    Next

    'remove the last ' OR '
    stFilter = Left(stFilter, Len(stFilter) - 4)
    Me.sfrmResults.Form.Filter = stFilter
    Me.sfrmResults.Form.FilterOn = True
End Sub

Add the following code to the cmdClearFilter button to remove the filter.

(Visual Basic for Applications)

Private Sub cmdClearFilter_Click()
    ' clear the filter
    Me.sfrmResults.Form.Filter = “”
    Me.sfrmResults.Form.FilterOn = False
End Sub

Selecting All Items in a Multi-Select List Box Using the Keyboard

If we continue with the multi-select list box from the previous example, let's say that you want to use the CTRL+A keyboard shortcut to select all items in a list box. Add the following code to the KeyDown event of the lstCustomers list box from the previous example to do this.

(Visual Basic for Applications)

Private Sub lstCustomers_KeyDown(KeyCode As Integer, Shift As Integer)
    Dim lItem As Long
    If (KeyCode = vbKeyA And ((Shift And acCtrlMask) = acCtrlMask)) Then

    ' select all items in the list
        For lItem = 0 To Me.lstCustomers.ListCount - 1
            Me.lstCustomers.Selected(lItem) = True
        Next
    End If
End Sub

This code walks through each item in the list box and sets its Selected property to True. When you select the list box, you should be able to press CTRL+A to select all items in the list box.

Custom Progress Bars

Way back when, the Office Developer Edition and Microsoft Office Developer released ActiveX versions of a progress bar control that you could use to provide feedback on a long running progress. ActiveX controls can simplify programming tasks but have deployment issues. As an alternative to an ActiveX control, the SysCmd function in Access can be used to display a progress bar in the status bar space at the bottom of the screen. This is pretty cool but what if the user chooses not to show his status bar or if you want to use that real estate? Unfortunately, the progress bar at the bottom of the screen sometimes goes unnoticed by users as well.

To solve these problems, we create lightweight progress bars using rectangle controls in Access. Visually we tend to prefer these controls for popup or modal forms that don't take up the entire screen and lean toward using the SysCmd function for full screen forms.

Creating this type of progress bar is pretty straight forward. It requires two rectangle controls that we call boxOutside and boxInside. As you can imagine from the names, boxInside is placed inside boxOutside. To update progress, the width of boxInside is adjusted as a percentage of the width of boxOutside. As with the progress bar created by the SysCmd function, this technique also requires that you know the number of items that you're measuring. Let's take a look at this in action.

Create the Form

The form will be fairly simplistic as we want to focus on the progress bar itself. Create a new form with two rectangles and a command button. Name the rectangles boxInside and boxOutside as mentioned earlier. Set the width of boxOutside to 4˝ so you can see the progress. Set the width of boxInside to 0˝ to start. Make sure that boxInside is not transparent or you won't be able to see anything! Name the command button cmdGo.

Run the Process

Because file operations may take some time, we write to a file by hand as a long running process. The file only contains the values from the loop as an example. Add the following code to the Click event of the button.

(Visual Basic for Applications)

Private Sub cmdGo_Click()
    ' create a file in the current directory
    Dim i As Integer
    Dim stFile As String
    Dim sngOffset As Single
    Dim n As Long
    stFile = CurrentProject.Path & “\NewFile.txt”

    ' open the file
    Open stFile For Output As #1

    ' number of items
    n = 5000
    For i = 1 To n
        ' print to the file
        Print #1, i
        ' update the progress - includes an offset calculation for aesthetics
        sngOffset = Me.boxInside.Left - Me.boxOutside.Left
        Me.boxInside.Width = i * ((Me.boxOutside.Width - (sngOffset * 2)) / n)
        Me.Repaint
        DoEvents
    Next

    ' close
    Close #1

    MsgBox “Done!”
End Sub

The algorithm shown here is one we use quite a bit and it includes an offset that bears some digging into. The rectangle boxOutside is formatted to have a border, whereas boxInside is not. To prevent boxInside from completely covering boxOutside, we tend to position the Left property of boxInside just barely to the right of boxOutside as shown in Figure 8-5, which has been zoomed to show the offset.

Figure 8-5. Position the Left property of boxInside just barely to the right of boxOutside

Positioning the Left property of boxInside

The offset calculation then is simply the difference in the Left property between boxInside and boxOutside. To keep this offset on both the left and right inside edges of boxOutside, multiply this by 2 when subtracting it from the width of boxOutside. Once the aesthetics are accounted for, divide the width of boxOutside by the number of items (n), and multiply by the current location in the loop (i). This dynamically determines the width of boxInside.

Custom Up/Down Buttons

Speaking of ActiveX controls, the toolkits mentioned in the previous section also had a pretty cool up/down control that users can click and hold a button to set values. This is pretty useful but it turns out that command buttons in Access let you do this without an ActiveX control.

Create a form with a text box and two command buttons named cmdDown and cmdUp, and a text box named txtInterval. Set the Auto Repeat property of the two command buttons to Yes. When this property is set, the Click event of a button fires repeatedly while the user is holding down the button.

Add the following code to the two buttons. This code adjusts the value of the text box in increments of 1000. When scrolling up, it scrolls without a limit. When scrolling down, it scrolls until it reaches 0.

(Visual Basic for Applications)

Private Sub cmdDown_Click()
    ' init
    Me.txtInterval = Nz(Me.txtInterval, 1000)

    ' decrement to 0
    If (CLng(Me.txtInterval) >= 1000) Then
        Me.txtInterval = CLng(Me.txtInterval) - 1000
    End If
    DoEvents
End Sub

Private Sub cmdUp_Click()
    ' init
    Me.txtInterval = Nz(Me.txtInterval, 1000)

    ' increment (unbounded)
    Me.txtInterval = CLng(Me.txtInterval) + 1000
    DoEvents
End Sub

Displaying Multiple Attachments Onscreen

The Attachment control in Access 2007 is used to display data in the Attachment data type. This is a multivalued field where multiple attachments can be stored in a single record. This data type is extremely useful and a big improvement over the OLE Object data type. Because it stores multiple files, however, navigation of attachments tends to happen within a single attachment control. For this example, let's say that you're developing a real estate application and want to display multiple attachments for a given property at once using a thumbnail. This is common in Web applications, such as www.realtor.com.

For this example, create a table called tblRealEstate with the fields shown in the following table.

Field Name

Data Type (Size)

ID

Autonumber

Address

Text (255)

City

Text (255)

State

Text (255)

HousePictures

Attachment
NoteNote

Sample images that you can use for the attachment field are included with the sample available with this book on www.wrox.com.

Fill the table with address information and images in the attachment field, as shown in Figure 8-6.

Next, create a form bound to this table. Set the Visible property of the HousePictures attachment control to No to hide the control. Add three new unbound attachment controls named Attachment1, Attachment2, and Attachment3 respectively. Set their height and width to 1.5” each to give them the appearance of being thumbnails.

Figure 8-6. Fill the table with address information and images in the attachment field

Add the address information and the images

Time for the code, which is surprisingly simple. You'll remember that the HousePictures field stores the actual attachment files for each property in the tblRealEstate table. We know that we want to update the unbound attachment controls for each record so we need to use the Current event of the form. The AttachmentCount property of the attachment control returns the number of attachments in the control. The control also includes a property called DefaultPicture that is used to display an image if there are no attachments in the control. In our case, because we are using unbound controls, we can simply loop through the attachments in the HousePictures control to set the DefaultPicture property of the unbound controls, as shown in the following code. Navigation through the HousePictures field is done using the Forward method of the control.

(Visual Basic for Applications)

Private Sub Form_Current()
    Dim i As Integer
    Dim att As Attachment

    ' bind the attachments
    For i = 0 To Me.HousePictures.AttachmentCount - 1
        Set att = Me.Controls(“Attachment” & i + 1)
        att.DefaultPicture = Me.HousePictures.FileName(i)
        Me.HousePictures.Forward
    Next
End Sub
NoteNote

The graphics used in this sample are also used in the section “Kiosk Forms” later in this chapter. We originally created the sample kiosk form with these same images using unbound OLE Object controls as the result of a copy/paste from another application. After pasting all nine images, the size of the database was approximately 28MB. After adding them to the database as PNG files in an attachment field, the database shrunk to 1.75MB after compact!

Next Part: Chapter 8:Using Code to Add Advanced Functionality to Forms (2 of 2)