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

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.

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

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

Contents

• Common Forms for Your Applications

• Custom Form Navigation

• Summary

• Additional Resources

Common Forms for Your Applications

Many applications have forms, ranging from the simplest to the complex. For example, a menu form may be used to provide navigation to other parts of the application, or a dashboard form might contain information to provide a summary view of the data in the database. In this section, we look at several types of forms that can be added to applications to provide a professional touch.

Dynamic Menu Forms and Dashboards

Menu forms and dashboards can be combined into a single form that provides both navigation and rich summary views. The main menu form can also be used for core functionality that you want to highlight in the application, such as a search feature. With so many pieces of data that can be presented on the form, how can you prevent information overload? One way is to create several different configurations for a dashboard and then allow users to select the one that they want. Different components that plug into the dashboard form can be selected to display in a particular place on the form. This enables users to configure the application to meet their needs and to see the pieces of data they are most interested in.

Note
You can find out more about configuring applications in Chapter 12.

Defining the Dashboard Layouts

The first thing we do is define the different form layouts for the dashboard forms. Our dashboards are simple with no more than four or five components on the form. Each dashboard is a separate form that hosts the components. Components on the form are displayed using subforms, which provide flexibility and allow movement of objects without affecting the main form.

Each dashboard should have the same properties set so they have a consistent appearance. Start by creating four dashboard forms with the common properties shown in the table that follows.

Property Name	Property Value
Caption	Dashboard
Allow Datasheet View	No
Allow PivotTable View	No
Allow PivotChart View	No
Record Selectors	No
Navigation Buttons	No
Scroll Bars	Neither

Save the forms with the name USysFrmDashboard1, USysFrmDashboard2, USysFrmDashboard3, and USysFrmDashboard4.

Note
These forms are included with the sample code available for download with this book from www.wrox.com. The sample file for this section is called Dashboards.accdb. The data used in the example is from the Northwind 2007 sample database.

We're also going to take a screen shot of each form once the controls have been added to act as a preview for users to choose. More on this later in this section.

USysFrmDashboard1

The design of dashboard 1 is similar to that of a Web page with a large navigation frame on the left side of the page and a top and bottom frame on the right. Add three subforms to the form and delete their associated labels. Set properties on the subforms as shown in the table that follows.

Control Name	Property Name	Property Value
Child0	Width	1.9167”
	Height	4.7083”
	Top	0.0417”
	Left	0.0833”
	Border Style	Solid
	Horizontal Anchor	Left
	Vertical Anchor	Both
	Name	sfrmLeft
Child1	Width	5.375”
	Height	2.4583”
	Top	0.0417”
	Left	2.0833”
	Border Style	Solid
	Horizontal Anchor	Both
	Vertical Anchor	Top
	Name	sfrmTop
Child2	Width	5.375”
	Height	2.2083”
	Top	2.5417”
	Left	2.0833”
	Border Style	Solid
	Horizontal Anchor	Both
	Vertical Anchor	Both
	Name	sfrmBottom

After setting the properties, you should have a form that resembles the one shown in Figure 8-7.

Figure 8-7. How the form looks after you set the properties

Open the form in form view and take a screenshot of the form. Using a graphics program such as Microsoft Paint, save the screenshot to the directory where the database resides in the PNG format as dashboard1.png.

USysFrmDashboard2

The design of dashboard 2 is similar to that of dashboard 1 with the exception that it includes a subform on the far right. The subforms on the top and bottom right of dashboard 1 appear in the middle of dashboard 2.

Add four subforms to the form and delete their associated labels. Set properties on the subforms as shown in the table that follows.

Control Name	Property Name	Property Value
Child0	Width	1.9167”
	Height	4.7083”
	Top	0.0417”
	Left	0.0833”
	Border Style	Solid
	Horizontal Anchor	Left
	Vertical Anchor	Both
	Name	sfrmLeft
Child1	Width	3.9167”
	Height	2.4583”
	Top	0.0417”
	Left	2.0833”
	Border Style	Solid
	Horizontal Anchor	Both
	Vertical Anchor	Top
	Name	sfrmTop
Child2	Width	3.9167”
	Height	2.2083”
	Top	2.5417”
	Left	2.0833”
	Border Style	Solid
	Horizontal Anchor	Both
	Vertical Anchor	Both
	Name	sfrmBottom

After setting the properties, you should have a form that looks something like the one shown in Figure 8-8.

Figure 8-8. How the form looks after you set the properties

Open the form in form view and take a screenshot of the form. Using a graphics program such as Microsoft Paint, save the screenshot to the directory where the database resides in the PNG format as dashboard2.png.

USysFrmDashboard3

The design of dashboard 3 consists of a navigation subform on the left side and then four equally sized subforms on the right. This is a classic dashboard form that you might use to display multiple charts on screen.

Add five subforms to the form and delete their associated labels. Set properties on the subforms as shown in the following table.

Control Name	Property Name	Property Value
Child0	Width	1.9167”
	Height	4.7083”
	Top	0.0417”
	Left	0.0833”
	Border Style	Solid
	Horizontal Anchor	Left
	Vertical Anchor	Both
	Name	sfrmLeft
Child1	Width	3.5”
	Height	2.5”
	Top	0.0417”
	Left	2.0833”
	Border Style	Solid
	Horizontal Anchor	Left
	Vertical Anchor	Top
	Name	sfrmTopLeft
Child2	Width	3.5”
	Height	2.5”
	Top	0.0417”
	Left	5.625”
	Border Style	Solid
	Horizontal Anchor	Left
	Vertical Anchor	Top
	Name	sfrmTopRight
Child3	Width	3.5”
	Height	2.5"
	Top	2.5833"
	Left	2.0833"
	Border Style	Solid
	Horizontal Anchor	Left
	Vertical Anchor	Top
	Name	sfrmBottomLeft
Child4	Width	3.5”
	Height	2.5”
	Top	2.5833”
	Left	5.625”
	Border Style	Solid
	Horizontal Anchor	Left
	Vertical Anchor	Top
	Name	sfrmBottomRight

After setting the properties, you should have a form that resembles the one shown in Figure 8-9.

Figure 8-9. How the form looks after you set the properties

Open the form in form view and take a screenshot of the form. Using a graphics program such as Microsoft Paint, save the screenshot to the directory where the database resides in the PNG format as dashboard3.png.

USysFrmDashboard4

The design of dashboard 4 consists of a subform on the top similar to a banner on a Web page and two subforms on the bottom.

Add three subforms to the form and delete their associated labels. Set properties on the subforms as shown in the following table.

Control Name	Property Name	Property Value
Text0	Width	1.9167”
	Top	0.2917”
	Left	0.0833”
	Enabled	No
	Locked	Yes
	Name	txtCurrentDashboard
Label1	Caption	Current Dashboard
	Top	0.0833”
	Left	0.0833”
Combo2	Width
	Top
	Left
	Row Source Type
	Allow Value List Edits
	Name
Child2	Width	5.916”
	Height	2.5”
	Top	1.0417”
	Left	3.0833”
	Border Style	Solid
	Horizontal Anchor	Both
	Vertical Anchor	Both
	Name	sfrmRight

After setting the properties, you should have a form that resembles the one shown in Figure 8-10.

Figure 8-10. How the form looks after you set the properties

Open the form in form view and take a screenshot of the form. Using a graphics program such as Microsoft Paint, save the screenshot to the directory where the database resides in the PNG format as dashboard4.png.

Creating the Tables

To store information about the different components that can be used in a dashboard, we need some tables. The tables store the names of objects that can be used as components, the different dashboard layouts with a preview of the dashboard, and then a junction table that determines where the components are placed in a dashboard.

Create the Dashboard Table

The dashboard table stores information about the dashboards in the application. Create a new table with the following fields. Save the table with the name USysDashboards after you've created the fields. We're using the USys prefix to hide our tables in the Access navigation pane by default.

Field Name	Data Type (Size)
DashboardID	AutoNumber (Primary Key)
DashboardName	Text (255)
DashboardForm	Text (64)
DashboardDescription	Text (255)
DashboardPreview	Attachment
IsSelected	Yes/No

After you save the table, fill it with data as shown in Figure 8-11. Use the screenshots created in the section “Defining the Dashboard Layouts” as the attachment in the DashboardPreview field.

Figure 8-11. Fill the table with data

Create the Component Table

The component table stores information about the components that can be displayed in a dashboard. Create a new table with the following fields. Save the table with the name USysComponents after you've created the fields.

Field Name	Data Type (Size)
ComponentID	AutoNumber (Primary Key)
ComponentName	Text (255)
ComponentObject	Text (64)

Fill the table with the names of forms in your application in the ComponentObject field.

Create the Dashboard Components Table

The dashboard components table stores data about the components that appear in a given dashboard. The maximum number of components that can appear in the dashboards is fixed so we're actually going to pre-populate this table with the names of the subforms that appear on the dashboard forms. Begin by creating a new table with the following fields. Save the table with the name USysDashboardComponents when you're done.

Field Name	Data Type (Size)
DashboardComponentID	AutoNumber (Primary Key)
ComponentID	Number (Long Integer)
DashboardID	Number (Long Integer)
DashboardControl	Text

In most cases, you would probably store only the components for the currently selected dashboard, which at most would be five in the case of USysFrmDashboard3. However, we're going to pre-populate this table for several reasons:

• The total number of subforms is fixed at 15.

• Switching dashboards would require deleting records. Because the Access database engine doesn't reclaim space until the database is compacted — the potential is there for unnecessary growth even though it may be minimal.

• Pre-populating the table allows the user to switch between dashboards and preserve their layout each time.

Add the name of each subform control in the DashboardControl field along with the appropriate DashboardID value from the USysDashboards table. The table should look something like the one shown in Figure 8-12 when you're done.

Figure 8-12. Add the name of each subform control

Creating Relationships for the Tables

With the tables created and filled, we need to create some relationships. View the Relationships diagram by selecting the Database Tools tab in the Ribbon, and then click the Relationships button. Add the USysDashboards, USysComponents, and USysDashboardComponents tables to the relationships window. Join the fields as shown in Figure 8-13. You'll notice that referential integrity is set on the relationships, as shown in Figure 8-13.

Figure 8-13. Join the fields

Choosing a Dashboard

The currently selected dashboard is stored in the IsSelected field in the USysDashboards table. To set value and give the user a choice of dashboards, create a new form that is bound to the USysDashboards table called USysFrmDashboards as follows.

1. Create a new form in design view. Bind the form to the USysDashboards table. Save the form as USysFrmDashboards. Set the properties shown in the table that follows on the form.

   Property Name	Property Value
   Caption	Choose Dashboard
   Allow Datasheet View	No
   Allow PivotTable View	No
   Allow PivotChart View	No
   Allow Layout View	No
   Width	4.5”
   Border Style	Dialog
   Record Selectors	No
   Navigation Buttons	No
   Record Source	USysDashboards
   Allow Additions	No
   Allow Deletions	No
   Pop Up	Yes
   Modal	Yes

2. Add a list box to the form with the properties shown in the table that follows.

   Property Name	Property Value
   Column Count	2
   Column Widths	0”;1”
   Width	1”
   Height	2.9167”
   Top	0.25”
   Left	0.0417”
   Row Source	SELECT DashboardID, DashboardName FROM USysDashboards ORDER BY DashboardName;
   Name	lstDashboards

3. Add an attachment control to the form with the properties shown in the table that follows.

   Property Name	Property Value
   Picture Size Mode	Stretch
   Width	3.3333”
   Height	2.6667”
   Top	0.25”
   Left	1.0833”
   Control Source	DashboardPreview
   Enabled	No
   Locked	Yes
   Name	DashboardPreview

4. Add a text box to the form with the properties listed in the table that follows. Delete the associated label.

   Property Name	Property Value
   Width	3.3333”
   Height	0.2188”
   Top	2.9583”
   Left	1.0833”
   Control Source	DashboardDescription
   Enabled	No
   Locked	Yes
   Name	DashboardDescription

5. Add a command button to the form with the properties listed in the table that follows.

   Property Name	Property Value
   Width	1”
   Height	0.25”
   Top	3.2083”
   Left	2.375”
   Caption	OK
   Name	cmdOK
   Default	Yes

6. Add a command button to the form with the properties listed in the table that follows.

   Property Name	Property Value
   Width	1”
   Height	0.25”
   Top	3.2083”
   Left	3.4167”
   Caption	Cancel
   Name	cmdCancel
   Cancel	Yes

The form should look something like the one shown in Figure 8-14.

Figure 8-14. The form should look something like this

With the controls created, it's time to add some code. We're going to use the list box to filter the form to show the selected dashboard. Before we do however, we need to retrieve the selected dashboard when the form loads to apply a filter. Add the following code to the Load event of the form.

(Visual Basic for Applications)

Private Sub Form_Load()
    ' select the currently selected id
    Dim id As Long
    Dim i As Long

We're using the DLookup function to get the selected DashboardID value. If we retrieve a value, then we create the filter string.

    id = Nz(DLookup(“DashboardID”, “USysDashboards”, “IsSelected=True”), -1)

    If (id <> -1) Then
        Me.Filter = “DashboardID = “ & id
        Me.FilterOn = True

Remember that we didn't bind the list box so in order to make the list box display the currently selected dashboard we need to add some additional code. The following code loops through the items in the listbox and finds the matching ID. Once found, select it using the Selected property of the list box.

        ' select the item in the listbox
        For i = 0 To Me.lstDashboards.ListCount - 1
            If (Me.lstDashboards.ItemData(i) = id) Then
                Me.lstDashboards.Selected(i) = True
                Exit For
            End If
        Next
    End If
End Sub

When the user selects a dashboard in the list, we create a filter to display the selected dashboard. Add the following code to the AfterUpdate event of the list box.

(Visual Basic for Applications)

Private Sub lstDashboards_AfterUpdate()
    ' filter
    If (Not IsNull(Me.lstDashboards)) Then
        Me.Filter = “DashboardID = “ & Me.lstDashboards
        Me.FilterOn = True
    Else
        Me.Filter = “”
        Me.FilterOn = False
    End If
End Sub

When the user clicks OK we want to update the USysDashboards table to set the IsSelected field for the dashboard. This is a two step process. We can only have one selected dashboard so we need to first clear the field for all records. Next, we update the IsSelected field for the selected dashboard.

(Visual Basic for Applications)

Private Sub cmdOK_Click()
    Dim stSQL As String

    ' update the selected menu
    If (Not IsNull(Me.lstDashboards)) Then
        stSQL = “UPDATE USysDashboards SET IsSelected = False”
        CurrentDb.Execute stSQL

        stSQL = “UPDATE USysDashboards SET IsSelected = True “
        stSQL = stSQL & “WHERE DashboardID = “ & Me.lstDashboards
        CurrentDb.Execute stSQL
    End If

    ' close
    DoCmd.Close acForm, Me.Name
End Sub

The last bit of code we need is to close the form when the user clicks Cancel.

(Visual Basic for Applications)

Private Sub cmdCancel_Click()
    DoCmd.Close acForm, Me.Name
End Sub

Opening the Selected Dashboard

Once the user has selected the dashboard form they'd like to see, we need to load the correct one. Create a new module called basDashboard and add the following code. This code opens the selected dashboard and returns it as an open Form object. This function will be used in other places in the application.

(Visual Basic for Applications)

Public Function GetCurrentDashboard() As Form
    ' returns the current dashboard form as an open form
    Dim stDashboardForm As String

    ' get the selected dashboard
    stDashboardForm = Nz(DLookup(“DashboardForm”, “USysDashboards”, _
        “IsSelected=True”), “”)
    ' if no dashboard is selected default to dashboard 1
    If (Len(stDashboardForm) = 0) Then
        stDashboardForm = “USysFrmDashboard1”
    End If

    ' open the dashboard form if it is not already open
    If (Not CurrentProject.AllForms(stDashboardForm).IsLoaded) Then
        DoCmd.OpenForm stDashboardForm
    End If

    ' return the dashboard form
    Set GetCurrentDashboard = Forms(stDashboardForm)
End Function

Next, add an initialization routine that you would typically call when the application loads. This can be called from a startup form or autoexec macro.

(Visual Basic for Applications)

Public Function Init() As Long
    GetCurrentDashboard
End Sub

Choosing Components

So far so good. We've created a few dashboards and a mechanism for selecting a dashboard. We think the dashboards are pretty cool, but without any content there isn't much to them. So, let's spice them up with components. Start by creating a new form called USysFrmComponents with the properties listed in the table that follows.

Property Name	Property Value
Caption	Choose Components
Border Style	Dialog
Record Selectors	No
Navigation Buttons	No
Scroll Bars	Neither
Width	2.0417”
Pop Up	Yes

Next, add one text box, two combo boxes, and three command buttons to the form. Set their properties as shown in the table that follows.

Control Name	Property Name	Property Value
Text0	Width	1.9167”
	Top	0.2917”
	Left	0.0833”
	Enabled	No
	Locked	Yes
	Name	txtCurrentDashboard
Label1	Caption	Current Dashboard
	Top	0.0833”
	Left	0.0833”
Combo2	Width	1.9167”
	Top	0.8021”
	Left	0.0833”
	Row Source Type	Value List
	Allow Value List Edits	No
	Name	cboDashboardFrames
Label3	Caption	Select frame
	Top	0.5938”
	Left	0.0833”
Combo4	Column Count	2
	Column Widths	0”
	Width	1.9167”
	Top	1.2813”
	Left	0.0833”
	Row Source	SELECT ComponentID, ComponentName FROM USysComponents ORDER BY ComponentName;
	Name	cboComponents
Label5	Caption	Select component 1.0729”
	Top	1.0729”
	Left	0.0833”
Command6	Caption	set
	Back Style	Transparent
	Name	cmdSet
	Cursor on Hover	Hyperlink hand
	Width	0.2708”
	Left	0.0833”
Command7	Caption	clear selected
	Back Style	Transparent
	Name	cmdClearSelected
	Cursor on Hover	Hyperlink hand
	Left	0.375”
	Width	0.8958”
Command8	Caption	clear all
	Back Style	Transparent
	Name	cmdClearAll
	Cursor on Hover	Hyperlink hand
	Left	1.2917”
	Width	0.5833”
	Name	cboComponents

After setting the properties, you should have a form that looks something like the one shown in Figure 8-15.

Figure 8-15. How the form looks after you set the properties

Let's add some code, beginning with the Load event.

(Visual Basic for Applications)

Private Sub Form_Load()
    Dim id As Long
    Dim rs As DAO.Recordset
    Dim stSQL As String
    Dim stItem As String

Get the current dashboard form by calling the GetCurrentDashboard function. This also ensures that the form is open.

    ' get the current dashboard
    Me.txtCurrentDashboard = GetCurrentDashboard().Name
    id = CLng(Right(Me.txtCurrentDashboard, 1))

We also need to get the list of the subform controls from the USysDashboardComponents table. Notice that we're removing the sfrm prefix for presentation and adding them to the cboDashboardFrames combo box.

    ' get the frames for the dashboard
    stSQL = “SELECT * FROM USysDashboardComponents “
    stSQL = stSQL & “WHERE DashboardID = “ & id
    Set rs = CurrentDb().OpenRecordset(stSQL)
    ' fill the list of frames in the dashboard
    Me.cboDashboardFrames.RowSource = “”
    While (Not rs.EOF)
        stItem = Replace(rs(“DashboardControl”), “sfrm”, “”)
        Me.cboDashboardFrames.AddItem stItem
        rs.MoveNext
    Wend
    ' cleanup
    rs.Close
    Set rs = Nothing
End Sub

For aesthetics, we're going to add a visual cue to the dashboard form when the user selects a frame from the list of subforms. Add the following code to the AfterUpdate event of the cboDashboardFrames combo box.

(Visual Basic for Applications)

Private Sub cboDashboardFrames_AfterUpdate()
    Dim stControl As String
    Dim frm As Form
    ' get the dashboard form
    Set frm = GetCurrentDashboard()
    ' get the control name
    stControl = “sfrm” & Me.cboDashboardFrames
    ' highlight the frame and reset the others
    HighlightFrame frm, stControl
End Sub

This event handler calls a routine called HighlightFrame, which does the actual work of setting the visual cue. The cue we're using is to thicken the border of the selected subform control and change its border color to red. Add the HighlightFrame routine to the form as follows.

(Visual Basic for Applications)

Private Sub HighlightFrame(frm As Form, stControl As String)
    Const FRAME_BORDER_COLOR As Long = &HC0C0C0
    Const FRAME_BORDER_WIDTH As Long = 0 ' hairline

    Dim ctl As Control
    For Each ctl In frm.Controls
        If (ctl.ControlType = acSubform) Then
            If (ctl.Name = stControl) Then
                ctl.BorderColor = vbRed
                ctl.BorderWidth = 2
            Else
                ctl.BorderColor = FRAME_BORDER_COLOR
                ctl.BorderWidth = FRAME_BORDER_WIDTH
            End If
        End If
    Next
End Sub

This code loops through the controls on the specified form and changes the border color and width. After a selection is made, the dashboard form should resemble the one shown in Figure 8-16.

The last thing to do now is to set the components. For our purposes, we're going to set them one at a time using the cmdSet button. Add the following code to the Click event of the button.

(Visual Basic for Applications)

Private Sub cmdSet_Click()
    ' set
    Dim stSQL As String
    Dim stCtl As String
    Dim lComponentID As Long
    Dim lDashboardId As Long
    Dim frm As Form
    If (IsNull(Me.cboComponents) Or IsNull(Me.cboDashboardFrames)) Then
        MsgBox “Please select a component and a frame to continue”, vbExclamation
        Exit Sub
    End If

Figure 8-16. The resulting dashboard form looks like this

Again, we're getting the current dashboard form by calling the GetCurrentDashboard function.

    ' get the dashboard form
    Set frm = GetCurrentDashboard()

Next, retrieve the component and dashboard ID values from the form.

    ' names and values
    stCtl = “sfrm” & Me.cboDashboardFrames
    lComponentID = Me.cboComponents
    lDashboardId = CLng(Right(frm.Name, 1))

Build the SQL statement to update the USysDashboardComponents table and run it.

    ' build the SQL
    stSQL = “UPDATE USysDashboardComponents SET ComponentID = “ & lComponentID
    stSQL = stSQL & “ WHERE DashboardID = “ & lDashboardId
    stSQL = stSQL & “ AND DashboardControl = '“ & stCtl & “'“
    ' run the sql
    CurrentDb().Execute stSQL

Call a routine named FillDashboard to fill components in the selected dashboard. We'll implement this routine in a moment. Call this routine to refresh the currently open dashboard.

    ' refresh
    FillDashboard

Last, remove highlighting from the selected subform by calling the HighlightFrame routine and pass an empty string for the stControl argument.

    ' remove highlighting
    HighlightFrame frm, “”
End Sub

Before we test the component form, we need to add code for the cmdClearSelected and cmdClearAll buttons. The cmdClearSelected button clears the selected component, and the cmdClearAll button clears the selected dashboard. Add the following code to the form.

(Visual Basic for Applications)

Private Sub cmdClearSelected_Click()
    ' clear selected
    Dim stSQL As String
    Dim stCtl As String
    Dim lDashboardId As Long
    Dim frm As Form

    ' get the dashboard form
    Set frm = GetCurrentDashboard()

    ' names and values
    stCtl = “sfrm” & Me.cboDashboardFrames
    lDashboardId = CLng(Right(frm.Name, 1))

    ' build the SQL
    stSQL = “UPDATE USysDashboardComponents SET ComponentID = NULL”
    stSQL = stSQL & “ WHERE DashboardID = “ & lDashboardId
    stSQL = stSQL & “ AND DashboardControl = '“ & stCtl & “'“

    ' run the sql
    CurrentDb().Execute stSQL
    ' clear the selected subform
    frm.Controls(stCtl).SourceObject = “”
End Sub

Private Sub cmdClearAll_Click()
    ' clear all
    Dim frm As Form
    Dim lDashboardId As Long
    Dim stSQL As String
    Dim ctl As Control

    Set frm = GetCurrentDashboard()

    ' names and values
    lDashboardId = CLng(Right(frm.Name, 1))

    ' build the SQL
    stSQL = “UPDATE USysDashboardComponents SET ComponentID = NULL”
    stSQL = stSQL & “ WHERE DashboardID = “ & lDashboardId

    ' run the sql
    CurrentDb().Execute stSQL

    ' clear the subforms
    For Each ctl In frm.Controls
        If (ctl.ControlType = acSubform) Then
            ctl.SourceObject = “”
        End If
    Next

End Sub

To test the components, open the USysFrmComponents form and select frames and components.

Note
If you haven't already, you'll need components defined in the USysComponents table for this to work.

Filling the Dashboard

Once components have been selected, filling the dashboard is pretty straightforward. The first thing to do is retrieve the components for the currently selected dashboard. We'll do this using a query. Create a new query with the following SQL. Save the query as USysQrySelectedDashboard.

(SQL)

SELECT
    ComponentName,
    ComponentObject,
    DashboardControl
FROM USysDashboards
INNER JOIN (USysComponents INNER JOIN USysDashboardComponents ON
USysComponents.ComponentID = USysDashboardComponents.ComponentID) ON
USysDashboards.DashboardID = USysDashboardComponents.DashboardID
WHERE (((IsSelected)=True));

Next, open the basDashboard module created earlier and add the following routine.

(Visual Basic for Applications)

Public Function FillDashboard() As Long
    Dim rs As DAO.Recordset
    Dim frm As Form

Get the dashboard form object by calling the GetCurrentDashboard function.

    ' get the dashboard form
    Set frm = GetCurrentDashboard()

Get the components using the query you just created:

    ' get the components for the current dashboard
    Set rs = CurrentDb().OpenRecordset(“USysQrySelectedDashboard”)

Last, fill the dashboard by setting the SourceObject property of each subform in the dashboard to the name of the component in the ComponentObject field in the query.

    ' fill the dashboard
    While (Not rs.EOF)
        ' Set the SourceObject property of the dashboard subform
        ' to the specified component
        frm.Controls(rs(“DashboardControl”)).SourceObject = rs(“ComponentObject”)
        rs.MoveNext
    Wend

    ' cleanup
    rs.Close
    Set rs = Nothing
End Function

You also need to call this function in the Load event for each dashboard. To do this, set the OnLoad property of each of the four dashboard forms to =FillDashboard().

When you open the currently selected dashboard, any selected components should appear in their respective subforms.

Splash Screens

The splash screen is often the first thing that a user sees when launching an application. They are an optional component of many applications, but can be useful if you need to configure portions of the application during startup. If you have a long-running process such as refreshing linked tables at startup, a splash screen lets the user know what's going on.

Quite often, a splash screen has no border or even buttons because if the splash screen is doing work behind the scenes, allowing the user to interact with the form could pose problems. We like to use a rectangle control around the detail section of the form to give the appearance of a border. An example of such a splash screen is shown in Figure 8-17.

Figure 8-17. Example of a splash screen

Note

The buttons shown in the figure are for demo purposes and run either the Timer event or long running code that follows.

Note
The buttons shown in the figure are for demo purposes and run either the Timer event or long running code that follows.

If the splash screen is doing work, a progress bar or other indication of status is useful. Consider closing the splash screen after a very short period of time if it is only being displayed for aesthetics or advertising. The following code shows you how to use the Timer event of a form to close a splash screen. Set the TimerInterval property of a form to 1000 to start the timer one second after the form opens.

(Visual Basic for Applications)

Private Static Sub Form_Timer()
    ' countdown
    Const COUNTDOWN = 5

    Dim iCounter As Integer

    ' init or decrement
    If (iCounter = 0) Then
        iCounter = COUNTDOWN
    Else
        iCounter = iCounter - 1
    End If

    If (iCounter > 0) Then
        If (iCounter = 1) Then
            Me.txtClosing = “Closing in “ & iCounter & “ second”
        Else
            Me.txtClosing = “Closing in “ & iCounter & “ seconds”
        End If

        DoEvents
        Me.Repaint
    Else
        ' close
        Me.TimerInterval = 0
        DoCmd.Close acForm, Me.Name
    End If
End Sub

This code updates a text box to give the user an indication of how long the form will be open.

You can encapsulate long running processes by calling routines that contain these processes using the Run method of the application object. In this example, we have five sample routines, which represent long running processes.

(Visual Basic for Applications)

' represents code that would run at startup
Public Function Step1() As Long
    MsgBox “This is step1”
End Function
Public Function Step2() As Long
    MsgBox “This is step2”
End Function
Public Function Step3() As Long
    MsgBox “This is step3”
End Function
Public Function Step4() As Long
    MsgBox “This is step4”
End Function
Public Function Step5() As Long
    MsgBox “This is step5”
End Function

The following code shows you how to run these routines in a loop and update a progress bar. It’s shown here as being launched from a command button, but would likely be started from the Load event of a form.

(Visual Basic for Applications)

Private Sub cmdDoWork_Click()
    ' runs 5 simulated routines at startup and updates a progress bar
    Dim iStep As Integer
    Dim sOffset As Single

    ' calculate the offset
    sOffset = Me.boxInside.Left - Me.boxOutside.Left

    ' reset the progress bar
    Me.boxInside.Width = 0

    For iStep = 1 To 5
        ' run the routine
        Application.Run “Step” & iStep

        ' update the progress bar
        Me.boxInside.Width = (iStep * (Me.boxOutside.Width - (2 * sOffset)) / 5)
    Next

    ' close
    DoCmd.Close acForm, Me.Name
End Sub

About Dialog Boxes

The About dialog box is used to display information about your application such as the version number, application name, and company name. In addition to providing users with information about the application itself, it can also be used to display other information, such as system information or credit to other developers or companies who have contributed to an application. As both the About dialog box and splash screen are important for branding an application, the About dialog box is sometimes a scaled-down version of the splash screen.

Figure 8-18 shows an example of an About dialog box.

Kiosk Forms

This one is simple but fun. A kiosk form is one you might find in your local mall or shopping center. Consider a real estate firm who has space set up in the mall and has a monitor to display current listings. These types of forms typically consume the entire screen real estate (no pun intended) to display full screen. It turns out that Access forms can easily be configured this way by simply maximizing a popup form.

Figure 8-18. An About dialog box

Create a new form in design view and set the Pop Up property to Yes. Then, add the following code to the Load event of the form. We’re turning off Echo in this case so you don’t see the form being maximized when it opens.

(Visual Basic for Applications)

Private Sub Form_Load()
    With DoCmd
         .Echo False
         .Maximize
         .Echo True
    End With
End Sub

An example of a kiosk form is shown in Figure 8-19.

Custom Form Navigation

There are many ways to navigate through an application. You might provide buttons or hyperlinks, or give users a list of places to choose from. Here are some other ways to accomplish navigation that you might consider.

Navigation Bars

Web sites often contain navigation in a single collection of links or buttons organized into a bar at the top of the page. We can do the same thing with buttons on forms. Say that you have an application with tasks for customers, employees, and orders. Each area has a top-level button named cmdCustomers, cmdEmployees, and cmdOrders respectively. Underneath these buttons are groups of buttons that let the user drill into the selected area. Using the Tag property of the buttons, we can associate them with the top-level button so that when the user clicks on a top-level button, we show or hide the lower-level buttons as shown in the following code.

(Visual Basic for Applications)

Private Sub ShowButtons(stTag As String)
    Dim c As Control

    For Each c In Me.Controls
        If (Len(c.Tag) > 0) Then
            c.Visible = (c.Tag = stTag)
        End If
    Next
End Sub
Private Sub cmdCustomers_Click()
    ShowButtons Screen.ActiveControl.Name
End Sub
Private Sub cmdEmployees_Click()
    ShowButtons Screen.ActiveControl.Name
End Sub
Private Sub cmdOptions_Click()
    ShowButtons Screen.ActiveControl.Name
End Sub
Private Sub cmdOrders_Click()
    ShowButtons Screen.ActiveControl.Name
End Sub

Figure 8-19. An example of a kiosk form

An example of the button layout is shown in Figure 8-20.

Figure 8-20. An example of a button layout

“I Need To” Drop-Down

The “I Need To” drop-down gets its name from Web pages that we’ve seen that lists common tasks in a drop-down list. The label for the drop-down usually says “I Need To” followed by the choice of task. This is a nice way to allow for navigation in the application while preserving screen real estate.

From the navigation perspective, this drop-down is very easy to implement — simply store the friendly text and the name of a form to open in a table and then use the AfterUpdate event of a combo box control to open the form. Because the items in the list are geared toward tasks, however, you can really do much more with them. For example, you might include an item to preview a particular report or run a custom function that exports data to a file. Because of this, we like to store the name of a function (complete with its arguments) to run in a table and call it using the Eval function.

Create a new table with the fields shown in the table that follows.

Field Name	Data Type (Size)
ID	AutoNumber
DisplayText	Text (255)
Action	Text (255)

Save the table with the name USysActions and fill it with the data shown in Figure 8-21.

Figure 8-21. Fill the table by with this data

Create a new module and add the following code that will be used by the combo box.

(Visual Basic for Applications)

' main function
Public Function RunAction(stAction As String) As Long
    Eval stAction
End Function
' functions in the table
Public Function ViewReport(stReportName As String) As Long
    DoCmd.OpenReport stReportName, acViewReport
End Function
Public Function ViewForm(stFormName As String) As Long
    DoCmd.OpenForm stFormName
End Function
Public Function MyExportFunction() As Long
    ' custom export function
    MsgBox “This is the custom export function”, vbInformation
End Function

Create a form with a combo box with the following Row Source.

(SQL)

SELECT * FROM USysActions ORDER BY DisplayText

Set the Column Count property of the combo box to 3 and the Column Widths property of the combo box to: 0”;1”;0”. Then, add the following code to the AfterUpdate event of the combo box to call the RunAction routine defined earlier.

(Visual Basic for Applications)

Private Sub cboActions_AfterUpdate()
    RunAction Me.cboActions.Column(2)
End Sub

When the RunAction routine is called, it passes the name of the function with its arguments stored in the combo box to the Eval function in the Access object model. The Eval function then calls the code specified in the table.

Keyboard-Driven Navigation

Point-of-sale or warehouse applications may not necessarily have a mouse attached to the computer where the application is running. In order to interact with the application, it might be necessary to use the keyboard for navigation. One popular way to do this is to use the function keys defined at the top of most keyboards. The KeyDown event shown earlier in this chapter can be used to listen for these keystrokes and take the appropriate action. The following code shows an example of what this might look like. Remember to set the Key Preview property of the form to Yes before running this code.

(Visual Basic for Applications)

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    ' handle keyboard navigation (or tasks)
    Dim stCmd As String
    Select Case KeyCode
        Case vbKeyF1
            MsgBox “You pressed help!”, vbInformation
            ' suppress Access help
             KeyCode = 0
        Case vbKeyF2
            ViewReport “rptRealEstate”
        Case vbKeyF3
            stCmd = “calc.exe”
        Case vbKeyF4
        stCmd = “notepad.exe”
        Case vbKeyF5
            MyExportFunction
    End Select

    ' run the command
    If (Len(stCmd) > 0) Then
        Shell stCmd, vbNormalFocus
    End If
End Sub

Summary

While the data is important to a database application, so is the design. A good design can go a long way in terms of enhancing the usability of an application and keeping users coming back, instead of making them go looking for other solutions. In several cases in this chapter we were able to extend functionality of an application by adding a little code that can also be made reusable by refactoring.

This chapter highlighted (not just controls), but really how code, when used with forms and controls, can also help to add the wow factor to an application. And it’s not all about the wow factor — in many cases, code can be used to create user interfaces that users find intuitive and just make sense. In this chapter you saw:

• How to track form activity when records were added, updated, or deleted

• That you have a pretty good amount of control over which error messages are displayed to users

• How to use visual cues that draw the user’s eye toward issues on the form that need their attention

• How to create dashboard forms to create applications that keep users engaged

• How to create standard types of forms such as splash screens and About dialog boxes that help brand your applications

In the next chapter, we look at how you can use code to create exciting, interactive reports using the new Report view of Access 2007, as well as a type of report that is not included with Access — the Calendar report.

Additional Resources

For more information, see the following resources:

• Book Chapter Landing Page: Chapter 11: Creating Dynamic Ribbon Customizations

• Access Developer Portal

• Microsoft Office Developer Center