Export (0) Print
Expand All
4 out of 4 rated this helpful - Rate this topic

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

Office 2007

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

Dd548012.Important(en-us,office.12).gifImportant:

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

Contents

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.

Dd548012.note(en-us,office.12).gifNote:

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.

Dd548012.note(en-us,office.12).gifNote:

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

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

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

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

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

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

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

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

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

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

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.

Dd548012.note(en-us,office.12).gifNote:

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

Example of a splash screen
Dd548012.note(en-us,office.12).gifNote:

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

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 a kiosk form

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

Figure 8-20. An example of a button layout

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

Fill the table 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

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.