Microsoft Access Workflow Designer: Creating a Team Solution User Interface Using Microsoft Visual Basic

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Doug Yoder
Microsoft Corporation

September 1999

Summary: This article walks through the creation of a Microsoft® Visual Basic® 6.0 application that takes advantage of the core set of services available in the Microsoft Access 2000 Workflow Designer for Microsoft SQL Server™ version 7.0. When completed, you will have a simple Visual Basic application that illustrates how to use these services in Visual Basic. To complete this walkthrough, you should be familiar with creating applications in Visual Basic. (40 printed pages)

Contents

Introduction What Is a Team Solution? Step 1: Design the Grid Form Step 2: Build the Workflow Toolbar Step 3: Design the Detail Form Step 4: Test Workflow Functionality Step 5: Provide Offline Access Step 6: Enable Data Synchronization Step 7: Test Offline Functionality Conclusion For More Information

Introduction

The Microsoft® Access 2000 Workflow Designer for Microsoft SQL Server™ 7.0 provides a robust core set of services that can be used to build powerful tracking solutions. The solution developed, the team solution, and its associated user interface typically are described as a Web site that contains a set of data access pages developed using Microsoft Access 2000. However, solutions using the Microsoft Access Workflow Designer extensions can be developed using a number of different development tools, depending on the requirements of the users and the features desired in the solution.

One such development tool is Microsoft Visual Basic® 6.0, which is one of the most popular development tools for the Microsoft Windows® platform. This article provides a walkthrough on how to build an application in Visual Basic that takes advantage of the core features available in the Microsoft Access Workflow Designer. After completing the walkthrough, you will have created a complete Visual Basic application that takes advantage of workflow features. This sample application has been kept basic, so you can apply these concepts to your own applications.

This article walks you step-by-step through the process of building an application in Visual Basic that uses features available in the Microsoft Access Workflow Designer. The code snippets throughout the article are provided for you to copy and paste into your application without having to retype them.

This article does not explain how to create a database using the Microsoft Access Workflow Designer, but it explains how to build an application using an existing database. The application uses the Issue Tracking database provided as a template with the Microsoft Access Workflow Designer. For details about using the template, see the Microsoft Access Workflow Designer online documentation.

Prerequisites

To follow the procedures in this article and create the Visual Basic application, you must have the following software and components installed on the server and client:

  • A server set up with the Microsoft Access Workflow Designer server components.
  • An instance of the Issue Tracking template created on the server using the New Team Solutions wizard.
  • A client set up with either the Microsoft Access Workflow Designer Tools or the End-User Client Components.
  • Visual Basic 6.0 installed on the client.

What Is a Team Solution?

A team solution is an application that uses one or more of the features provided by the Microsoft Access Workflow Designer—for example, workflow and offline capabilities. A typical team solution created using the Microsoft Access Workflow Designer has a SQL Server database and a user interface created using data access pages.

The Microsoft Access Workflow Designer tools help you add these features to your database; but to use them in your Visual Basic application, you must expose this functionality to the user.

Using Visual Basic as the interface has drawbacks. Microsoft Access Workflow Designer can be used to create a template of a team solution. A solution template makes it possible for users to create additional applications easily from a pre-defined solution that has been created into a template. The Microsoft Access Workflow Designer can create templates only from solutions that have a Web site interface. Therefore, the Visual Basic application created using this article cannot become a template. However, you could deliver a Visual Basic team solution by creating a template of the Microsoft Access Workflow Designer database and then separately distributing the Visual Basic application that uses that database.

The Visual Basic Team Solution

The Visual Basic team solution created in this article is structured in a similar manner to the Web-based Issue Tracking solution provided as a template in the Microsoft Access Workflow Designer. The application contains the following user interface elements: a Grid form (Form1) that shows a summary of issues and a Detail form (Form2) that shows details about a specific issue. The user interacts with the database through these two forms.

Grid Form (Form1)

The Grid form uses the Hierarchical FlexGrid control to provide a list of the Issues in the Issue Tracking database. Along the upper portion of the Grid form, a series of command buttons provides access to actions defined for the workflow process. This series of buttons is referred to as the workflow toolbar. The lower portion of the Grid form contains two buttons that control the offline behavior of the application. See Figure 1.

Figure 1. Grid form (Form1)

Although the completed Grid form is simple, it does provide you with an understanding of how to use the features of the Microsoft Access Workflow Designer in Visual Basic.

Detail Form (Form2)

The Detail form is a data-bound form that is used to view and modify individual rows in the Issue Tracking database. It is displayed by selecting one of the actions listed on the workflow toolbar on the Grid form. Because nearly all of the Microsoft Access Workflow Designer functionality is implemented in the Grid form, only minimal time is spent on creating this form. See Figure 2.

Figure 2. Detail form (Form2)

Step 1: Design the Grid Form

The Grid form is used to provide a list or summary of all issues stored in the Issue Tracking database. The form is a standard Visual Basic form with a Hierarchical FlexGrid control. The data binding is done using the Data Environment designer.

Creating the Data Environment

The Data Environment designer provides a design-time interface for specifying the data you want to access at run time. The designer generates Microsoft ActiveX® Data Objects (ADO) Connections, Commands, and recordsets at run time that you can manipulate programmatically.

In addition, Visual Basic provides a way for developers to automatically bind Command objects defined in the Data Environment to data-aware controls on a form. This makes it possible to display data on a Visual Basic form without any programming.

To create the Data Environment

  1. Start Visual Basic, and select Standard EXE from the New Project dialog box.

  2. From the Project menu, select Add Data Environment.

    **Note   **If this menu item is not available, you must make the Data Environment designer available from the Components dialog box. Select Components from the Project menu, and then in the Designers tab, select Data Environment. See Figure 3.

    Figure 3. Data Environment menu item

    A new Data Environment is always created with one Connection object named "Connection1." The Connection object defines which SQL Server and database contains the data to use.

  3. Right-click Connection1, and select Properties.

  4. Select Microsoft OLE DB Provider for SQL Server from the list of providers, and click Next.

  5. Enter the name of your SQL Server in the Select or enter a server name box, and select Use Windows NT integrated security.

  6. Enter or select the name of the database to use in the Select the database on the server box. See Figure 4.

  7. Click OK.

    Figure 4. Data link properties

    The Connection object defines the database to access. A Command object must be defined that specifies which table or view to access. For example, in the Issue Tracking database, the IssuesBaseView view should be used. This view contains all fields in the Issues table and all the lookup tables (such as Category, Priority, and Status).

  8. Right-click Connection1, and select Add Command. A new item, Command1, is listed under Connection1.

  9. Right-click Command1, and select Properties. See Figure 5.

  10. Enter IssuesBaseView for the Command Name.

  11. Select View from the Database Object box, and select dbo.IssuesBaseView from the Object Name box.

  12. Click OK.

    Figure 5. Command properties page

    The Data Environment window lists the IssuesBaseView Command and all fields within the view under it. See Figure 6.

    Figure 6. Data Environment with IssuesBaseView listed

The data returned from the IssuesBaseView is now available in the application using this Data Environment object.

Creating the Hierarchical FlexGrid Control

The Hierarchical FlexGrid Microsoft ActiveX control provides an easy way to display multiple rows of data from a data source, because it supports direct binding to an ADO object defined in a Data Environment object. The control also provides built-in property pages that make it easier to format the fields you want displayed within the grid.

To create the Hierarchical FlexGrid control

  1. Arrange the windows, so both the Data Environment window and Form1 are visible on the screen.

  2. Right-click IssuesBaseView in the Data Environment window, and drag it onto Form1.

  3. Select Hierarchical Flex Grid from the shortcut menu.

    A Hierarchical FlexGrid control is created on the form. In addition, because dragging the IssuesBaseView node from the Data Environment created it, the grid automatically binds to the data from that view.

  4. Resize the grid, so it covers most of the form, as shown in Figure 7.

    Figure 7. Form with Hierarchical FlexGrid control at design time

    Using the Hierarchical FlexGrid control property pages, the run-time display and behavior of the grid can be modified.

  5. Right-click the Hierarchical FlexGrid control, and select Properties.

  6. On the General tab, specify None for the FocusRect property and 1 – Row for the SelectionMode property, and click OK.

    Setting these properties makes it possible for the user to select only one row at a time in the grid and ensures the entire row is highlighted.

    In addition, you can change the grid to show only certain fields from the IssuesBaseView and change the order of the fields displayed.

  7. Right-click the Hierarchical FlexGrid control, and select Retrieve Structure.

    The fields available in the IssuesBaseView to which the control is bound are displayed in the grid. This is the default layout of the fields in the grid.

  8. Right-click the Hierarchical FlexGrid control, select Properties, and then select the Bands tab.

  9. In the box, clear those fields you do not want displayed. In this example, only the following fields are selected: ItemID, AssignedTo, Subject, and Status.

  10. Select the ItemID field, and click the UP ARROW key until this field is the first field in the list. See Figure 8.

    **Note   **This step is important for later in the walkthrough, because this example will not work if the ItemID field is not the first field in the grid.

    Figure 8. Formatting the Hierarchical FlexGrid control

  11. Click OK.

The fields listed in the grid change to those you selected in the property page.

Running the Grid Form

The basic design of the Grid form is now complete. Select Start from the Run menu to run the application.

Form1 displays a list of the issues in your database. If you have not entered any issues in the database, then none are listed. Note that only those fields selected are displayed. If you have issues in the database and you click a row, the entire row is highlighted. This is the behavior enabled by the FocusRect and SelectionMode property settings. See Figure 9.

Figure 9. Grid form at run time

Step 2: Build the Workflow Toolbar

Now that the basic Grid form is working, it is time to add some Microsoft Access Workflow Designer-specific functionality. The workflow toolbar makes the actions that have been defined for the workflow process available to the user. In the case of the Issue Tracking database, the actions defined include Resolve, Close, Edit, Active, and so on.

Two main steps are required to build the workflow toolbar. The first step is to execute a set of queries against the database that returns information about the workflow process (such as the actions defined), and the second step is to use the information returned to build the toolbar dynamically.

Using the Data Environment to Read Workflow Information

Just as the Data Environment designer was used to define the data to specify what data to display in the Hierarchical FlexGrid control, the Data Environment is also a great way to read the workflow information. It provides a way to define visually the queries required, and it simplifies the code required to read the information.

In this example, the same Data Environment object is used to retrieve the IssuesBaseView data as is used when adding an additional Command object to it. Alternatively, you could create an additional Data Environment object if you want to keep a logical separation between "user data" and "workflow information" queries.

To create the workflow queries objects

  1. Double-click DataEnvironment1 in the Project Window to display the Data Environment window.

  2. Right click Connection1, and select Add Command.

    A new item, Command1, is listed under Connection1.

  3. Right-click Command1, and then select Properties. See Figure 10.

  4. Enter Actions for the Command Name.

  5. Select SQL Statement, and enter the following SQL SELECT statement in the edit box:

    SELECT IssuesWorkflowActions.Caption, Min(Event) As Event, 
Min(Position) As Position 
FROM IssuesWorkflowActions 
INNER JOIN IssuesWorkflowView ON 
IssuesWorkflowActions.WorkflowId = IssuesWorkflowView.Id 
GROUP BY IssuesWorkflowActions.Caption 
ORDER BY 3

    Figure 10. Actions Command property page

  6. Click OK.

    This Command object, Actions, is used to return a list of all of the available actions defined for the workflow process associated with the Issues table.

    The IssuesWorkflowActions table contains a row for each action defined in the process, and the Caption field specifies the text that should be listed in the user interface. The IssuesWorkflow table contains the workflow rules along with other information required for each action, such as the workflow event fired for a particular action. Therefore, a two-table join is used to combine the tables, so all the information required is returned in one query.

  7. Repeat the earlier steps. Enter AvailableActions for the Command Name. See Figure 11. Enter the following in the SQL Statement edit box:

    SELECT IssuesWorkflowActions.Caption, IssuesWorkflowView.State, 
IssuesWorkflowView.Next_State 
FROM IssuesWorkflowView 
INNER JOIN IssuesWorkflowActions ON 
IssuesWorkflowView.Id = IssuesWorkflowActions.WorkflowId 
WHERE (State = ?) OR (State = -1)

    Figure 11. AvailableActions Command property page

    This Command object, AvailableActions, is used to read the actions dynamically that are available based on a specific state. For example, in the Issue Tracking database, if a row has a state of Active (StatusID = 1), the available actions include Resolve and Edit but not Close or Activate.

    The question mark included in the SQL SELECT statement is a query parameter, which can be provided at run time to alter dynamically the WHERE clause each time the Command is executed.

  8. Repeat the earlier steps. Enter NextState for the Command Name. See Figure 12. Enter the following in the SQL Statement edit box:

    SELECT IssuesWorkflowView.Id, Status.Status, IssuesWorkflowView.State, 
IssuesWorkflowActions.Caption, IssuesWorkflowView.Next_State, IssuesWorkflowView.Event 
FROM IssuesWorkflowView 
LEFT OUTER JOIN Status ON 
IssuesWorkflowView.Next_State = Status.StatusID 
INNER JOIN IssuesWorkflowActions ON 
IssuesWorkflowView.Id = IssuesWorkflowActions.WorkflowId 
WHERE (IssuesWorkflowActions.Caption = ?) 
AND (IssuesWorkflowView.State = ? OR IssuesWorkflowView.State = - 1)

    Figure 12. NextState Command property page

    This Command object, NextState, is used to retrieve the next state value when transitioning from one state to another. For example, in the Issue Tracking database, when the user executes the Resolve action, it means the StatusID field of the row is changing from 1 to 2. This query is used to identify that 2 is the next value for the StatusID field.

    **Note   **This SQL SELECT statement also uses parameters to supply dynamically the WHERE clause at run time.

When you have completed these steps, the Data Environment object contains the original IssuesBaseView Command object along with the three new Command objects: Actions, AvailableActions, and NextState. See Figure 13.

Figure 13. Data Environment with Workflow Commands

Creating the Workflow Toolbar

The workflow information is now available using the Command objects in the Data Environment object. The next step is to use the information returned to build a user interface that lists the available actions.

The user interface created here contains a control array of a series of CommandButtons—one for each action. This is a very basic example, but it provides the concepts required to create more elaborate interfaces. Most of the work is done using code at run time, because at design time, it is unknown what actions are available.

To create the workflow toolbar

  1. Double-click Form1 in the Project Window to display the Form1 window.

  2. Click the CommandButton object on the Toolbox, and draw a CommandButton at the upper left of the form.

    At design time, only one button is created, which is used as a template. For each action available, the CommandButton is copied and moved next to the previous one to form a series of buttons across the upper portion of the form.

  3. Right-click Command1, and select Properties.

  4. Change the (name) property to cmdAction and the Index property to 0.

    Changing the Index property to 0 causes the CommandButton to become a control array that can contain multiple command objects at run time.

  5. Right-click Command1, and select View Code.

  6. In the Code Editor, add the following routine:

    Public Function BuildActions() As Variant
    Dim DataEnvironment1 As New DataEnvironment1, intControl As Integer
    DataEnvironment1.Actions
    intControl = 0
    While Not DataEnvironment1.rsActions.EOF
        If intControl <> 0 Then
            Load cmdAction(intControl)
            cmdAction(intControl).Top = cmdAction(intControl - 1).Top
            cmdAction(intControl).Left = _ 
  cmdAction(intControl - 1).Width + cmdAction(intControl _
  - 1).Left
        End If
        With cmdAction(intControl)
            .Visible = True
            .Enabled = True
            .Caption = _
  DataEnvironment1.rsActions.Fields("Caption").Value
            .Tag = DataEnvironment1.rsActions.Fields("Event").Value
        End With
        intControl = intControl + 1
        DataEnvironment1.rsActions.MoveNext
    Wend

End Function

    This routine accomplishes the following:

    • Creates an instance of the Data Environment object that contains the queries to retrieve the workflow information.
    • Executes the Actions command.
    • Walks through each row returned from the query.
    • Uses the button drawn at design time for the first action. However, for any action after that, a CommandButton must be created dynamically at run time. The new button is positioned immediately to the right of the previous CommandButton.
    • Changes the caption of the CommandButton to the Caption defined in the workflow action information and uses the Tag property to store the event name. The event name is used later when the user executes the action.
    • Navigates to the next action.

  7. In the Code Editor, add the following routine:

    Private Sub Form_Load()
    BuildActions
End Sub

This causes the BuildActions routine to be executed when the form is first loaded.

Running the Grid Form

Test the building of the workflow toolbar by running the Grid form. Select Start from the Run menu to run the application.

You should see a series of buttons along the upper portion of the form that list each of the actions available in the workflow process of the Issue Tracking database. See Figure 14.

Figure 14. Actions in Issue Tracking database

Updating the Workflow Toolbar

Whether an action listed on the workflow toolbar is available depends on the currently selected issue. Only valid actions for the selected issue should be available. For example, if an issue is Active, then Closed is disabled, because it is not a valid action during the Active state. The CommandButtons on the toolbar must be enabled and disabled based on the currently selected issue.

To update the workflow toolbar

  1. Add the following routine to the Form1 code module:

    Private Sub UpdateActions(intState As Integer)
    Dim DataEnvironment1 As New DataEnvironment1, intControl As Integer
    DataEnvironment1.AvailableActions intState
    For intControl = 0 To cmdAction.UBound
        cmdAction(intControl).Enabled = False
    Next
    While Not DataEnvironment1.rsAvailableActions.EOF
        For intControl = 0 To cmdAction.UBound
            If cmdAction(intControl).Caption = _
  DataEnvironment1.rsAvailableActions.Fields("Caption")._
  Value Then
                cmdAction(intControl).Enabled = True
            End If
        Next
    DataEnvironment1.rsAvailableActions.MoveNext
    Wend
End Sub

    This routine accomplishes the following:

    • Creates an instance of the Data Environment object that contains the queries to retrieve the workflow information.
    • Executes the AvailableActions Command and passes in the state value. This passes the state value to the command, replacing the parameter (question mark) in the query with the state value.
    • Walks through each button and disables it.
    • Walks through each row returned from the query.
    • Walks through the buttons to find a button caption that matches the current row's caption value. Each time it finds a match, the button is enabled, because the query indicates that the action is valid for this state.
    • Moves to the next row in the query.

  2. Add the following code to the Form1 code module:

    Private Sub MSHFlexGrid1_RowColChange()
    DataEnvironment1.rsIssuesBaseView.MoveFirst
    DataEnvironment1.rsIssuesBaseView.Find "ItemID = " + _
       CStr(MSHFlexGrid1.TextMatrix(MSHFlexGrid1.Row, 0))
    UpdateActions _
       DataEnvironment1.rsIssuesBaseView.Fields("StatusID").Value
End Sub

    This routine accomplishes the following:

    • The routine is based on the RowColChange event of the Hierarchical FlexGrid control. Whenever a different row is selected in the grid, this event is fired.
    • The MoveFirst and Find methods of the recordset are used to ensure the current row of the recordset is the same row that is highlighted on the Hierarchical FlexGrid control. This is done by retrieving the ItemID field value from the highlighted row in the Hierarchical FlexGrid control using the TextMatrix property of the grid.
    • Retrieves the current state of the row from the StatusID field from the recordset when the highlighted record is found in the Data Environment recordset.

  3. In the Code Editor, find the Form_Load routine, and add the line MSHFGrid1_RowColChange, so it contains:

    Private Sub Form_Load()

    BuildActions

    MSHFlexGrid1_RowColChange

End Sub

This makes sure the toolbar is updated not only when the user changes the selected row, but also when the form is first loaded.

Make sure to place the RowColChange command after the execution of BuildActions. Otherwise, no buttons will be available.

Now you are ready to test it out. Select Start from the **Run****menu. You will see the workflow toolbar with the appropriate actions enabled and the others disabled. Select an issue that has a different status, and the workflow toolbar automatically updates. See Figure 15.

Figure 15. Running the form with updated actions

This completes the building of the primary functionality of the Grid form. The next step is to create the Detail form and make it possible for the execution of the actions by displaying the Detail form when an action is selected on the workflow toolbar.

Step 3: Design the Detail Form

The Detail form is used to view and modify one issue at a time. Similar to the Grid form, the Detail form is bound to the IssuesBaseView Command object in the Data Environment. However, instead of being bound to a grid, the fields are bound to individual TextBox controls.

Enabling Updates on IssuesBaseView

By default, Command objects created in the Data Environment are read-only. An additional setting and a line of code are required to make the Command object updateable.

To enable updates on IssuesBaseView command

  1. Double-click DataEnvironment1 in the Project Window to display the Data Environment window.

  2. Right-click IssuesBaseView, and select Properties.

  3. On the Advanced tab, select 3 – Optimistic from the Lock Type box. See Figure 16.

    Figure 16. Advanced Command property page

  4. Click OK.

In addition to setting the Lock Type property, another property value must be changed.

One of the features of the Microsoft Access Workflow Designer makes it possible for users to set row-level permissions. This functionality is enforced through a view that only returns the rows to which the user has access, based on the row-level permissions.

To prevent users from bypassing the row-level permissions and viewing rows to which they do not have access, the base table does not make read or write permissions possible to any users.

In the case of the Issue Tracking solution, the base table Issues is not accessible, but the views IssuesView and IssuesBaseView are accessible. To make updates to the Issues table view, IssuesBaseView, the Command object must be told to make updates only using the primary key of the base table. To do this, change the property setting to the Update Criteria property.

This property must be set whenever the Command object is opened. This can be done by adding code to the Initialize event in the module behind the Data Environment.

To set the Update Criteria property to enforce row-level permissions

  1. Right-click DataEnvironment1, and select View Code.

  2. In the Code Editor, add the following routine:

    Private Sub DataEnvironment_Initialize()
    DataEnvironment1.rsIssuesBaseView.Properties("Update Criteria") = 0
End Sub

Creating the Detail Form

The Detail form can be created in a similar manner as the Grid form—by dragging the Data Environment onto a form.

To create the Detail form

  1. From the Project menu, select Add Form.

  2. In the Add Form dialog box, select Form, and then click OK.

  3. Arrange the windows, so both the Data Environment window and Form2 are visible on the screen.

  4. Drag the AssignedTo field listed under IssuesBaseView in the Data Environment,****and drop it onto Form2.

    A TextBox control is created on the form with a label, with a caption of "AssignedTo." The TextBox is set up automatically to bind to the AssignedTo field.

  5. Repeat the steps to create a Textbox control for each of the following fields:

    Field
    AssignedTo
    Subject
    Description
    ResolutionDescription
    ResolutionID

    Form2 contains five text boxes and looks similar to the following one in Figure 17, depending on how you placed the fields on the form.

    Figure 17. Form 2 with fields from IssuesBaseView

    The Detail form will be accessed from the Grid form. The user must have a way to save changes made to the row and then close the Detail form.

  6. Click the CommandButton object on the Toolbox, and draw two CommandButtons controls on the lower portion of the form.

  7. Right-click Command1, and select Properties.

  8. Change the (name) property to cmdOK,****the Caption to OK, and Default to True.

  9. Right-click Command2, and select Properties.

  10. Change the (name) property to cmdCancel, Cancel to True, and Caption to Cancel. See Figure 18.

    Figure 18. OK and Cancel buttons on the Detail form

  11. Right-click cmdOK, and select View Code.

  12. In the Code Editor, add the following routines:

    Private Sub cmdCancel_Click()
    DataEnvironment1.rsIssuesBaseView.Cancel
    Unload Form2
End Sub
Private Sub cmdOK_Click()
    DataEnvironment1.rsIssuesBaseView.Update
    Unload Form2
End Sub

    The cmdCancel routine accomplishes the following:

    • Executes the Cancel method to undo any changes made on the form.
    • Closes the Detail form.

    The cmdOK routine accomplishes the following:

    • Executes the Update method to commit any changes made on the form.
    • Closes the Detail form.

Displaying the Detail Form by Executing an Action on the Toolbar

For each action defined on the workflow toolbar, there is an associated event. The event associated with the action defines the functionality that is executed. The events and typical functionality are as follows:

  • **OnCreate—**Inserts a new row into the database.
  • **OnUpdate—**Modifies a field, other than the workflow field, in the database.
  • **OnDelete—**Deletes a row from the database.
  • **OnTransition—**Modifies the workflow state for a row that is changed to a different state.

For each of these events, except OnDelete, the user should have the opportunity to view and modify all fields from the Issues table. In addition, for the OnTransition event, the next state based on the current state of the row must be identified, and the workflow field must be updated with this new state.

The actions are executed by clicking a button on the workflow bar, which is built using the cmdAction control array. Therefore, code must be added on the cmdAction_Click event that identifies the button clicked and executes functionality based on the event associated with the button action. Remember that when the workflow toolbar is generated, the Tag property of the control array is set to the event associated with the action. Code must retrieve this value and, based on the value, execute the appropriate functionality.

To make it possible for the form to execute actions

  1. Right-click Form1, and select View Code. Add the following code to handle the OnCreate event. For OnCreate, the code must display the Detail form and create a new row.

    Private Sub cmdAction_Click(Index As Integer)
    Select Case cmdAction(Index).Tag
        Case "OnCreate"
            Load Form2
            DataEnvironment1.rsIssuesBaseView.AddNew
            Form2.Show vbModal, Me

    This section of the routine accomplishes the following:

    • Begins a Select Case comparison against the Tag property, which contains the event name, of the CommandButton clicked.
    • Loads the Detail form (Form2) into memory if the event is OnCreate but is not yet visible.
    • Creates the recordset associated with the IssueBaseView Command object by loading the Detail form. The AddNew method of the recordset is executed, so a new row is created.
    • Makes the Detail form visible in a modal state, with the Grid form as its parent window.

  2. Add the following code immediately after the earlier code to handle the OnUpdate event. For OnUpdate, the user must see the Detail form to edit the currently selected row.

            Case "OnUpdate"
            Form2.Show

    Continuing this routine accomplishes the following:

    • Immediately shows the Detail form (Form2) if the event is OnUpdate. Because the form is bound to the same recordset as the Hierarchical FlexGrid, the form already navigated the recordset to the same row that is highlighted, so the form only must be displayed.

  3. Add the following code immediately after the earlier code to provide functionality for the OnDelete event. For OnDelete, the Detail form does not even have to be displayed. The user only must verify that the current row should be deleted.

            Case "OnDelete"
            If MsgBox("Are you sure?", vbYesNo, "Delete Issue") = _
                vbYes Then
                DataEnvironment1.rsIssuesBaseView.Delete
            End If

    This code does the following:

    • Displays a message box to confirm the user wants to delete the current row if the event is OnDelete.
    • Selects the row by executing the Delete method if the user selects Yes.

  4. Add the following code immediately after the earlier code to provide functionality for the OnTransition event, which is the "meat" of the workflow functionality. An OnTransition event is the same as an edit, except the status of the row being edited must be changed. The status the row is changed to depends on the action the user is executing.

    Add the following code:

            Case "OnTransition"
            intState = _
              DataEnvironment1.rsIssuesBaseView.Fields("StatusID").Value
            strCaption = cmdAction(Index).Caption
            DataEnvironment1.NextState strCaption, intState
            intNextState = _
              DataEnvironment1.rsNextState.Fields("Next_State").Value
            DataEnvironment1.rsIssuesBaseView.Fields("StatusID").Value _
              = intNextState
            Form2.Show vbModal, Me
        End Select

    This code accomplishes the following:

    • Retrieves the status of the current row and the Caption of the button that the user clicked if the event is OnTransition.
    • Passes this information as parameters to execute the NextState Command object that was created in the Data Environment. After it is executed, the row returned will contain the next StatusID to which that row should be set.
    • Retrieves this value and sets the StatusID column of the current row to it.
    • Shows the Detail form, so the user can modify any of the other fields.

  5. Add the rest of this code to complete the routine. Add this code to update the Hierarchical FlexGrid control, so it contains any modifications that the user may have made using the Detail form:

        DataEnvironment1.rsIssuesBaseView.Requery
    Set MSHFlexGrid1.DataSource = DataEnvironment1
    MSHFlexGrid1.DataMember = "IssuesBaseView"
    MSHFlexGrid1_RowColChange
End Sub

    This code accomplishes the following:

    • Requires the Command object bound to the Hierarchical FlexGrid control, so any changes made are returned.
    • Rebinds the Hierarchical FlexGrid control to the Data Environment, so the updates are reflected in the grid.
    • Calls the RowColChange event to update the workflow toolbar in case the state has change.

Step 4: Test Workflow Functionality

The previous steps complete all workflow functionality for the application. Follow the next steps to test the functionality.

To test the workflow functionality

  1. From the Run menu, select Start. Form1 is displayed with the proper actions available.

  2. Click New on the workflow toolbar. The Detail form (Form2) appears with a new row. See Figure 19.

    Figure 19. Detail form at run time

  3. Enter information in the AssignedTo, Subject, and Description fields. Because this is a new issue, you are not required to add anything to the ResolutionDescription or ResolutionType fields.

  4. Click OK.

    **Note   **The Issue Tracking database contains workflow scripts that require the value of the AssignedTo field to be associated with a user in the user directory. If you enter a user to the AssignedTo field who is not in the user directory, you will receive an error message. See Figure 20.

    **Note   **Because this code does not have any error-checking for this, you must click End to stop the application and then restart it. To complete your application, you must add error-checking routines to handle such errors appropriately.

    Figure 20. Error received when invalid AssignedTo value is entered

    After you successfully add the issue to the database, you will have a new row in the Grid form with a status of Active, the default for new rows.

  5. Select the newly added issue, and click Resolve on the workflow toolbar.

    The Detail form appears with the new row you added. Although it looks as if the row is being edited only, the code has executed a workflow transition automatically by changing the StatusID field from 0 (Active) to 1 (Resolved). This change is not committed until you click OK.

  6. Enter a value for ResolutionDescription and ResolutionID (such as 1).

    You must enter a value for these fields, because the Issue Tracking solution contains workflow scripts that validate these fields on a transition from Active to Resolved. See Figure 21.

    Figure 21. Resolving an issue

  7. Click OK.

The Grid form is updated, and you will see the status of the issue you entered has changed from Active to Resolved. In addition, the workflow toolbar has been updated automatically to reflect the change.

Test the rest of the actions available by selected issues and clicking actions on the workflow toolbar.

Step 5: Provide Offline Access

Offline access is the second major functionality provided by the Microsoft Access Workflow Designer. Enabling offline access in your application requires two steps. First, enable offline access by replicating a local copy of the network database. Then, have your application dynamically change its connection information to use the local copy when running offline. Once the database is available offline, functionality is required to make it possible for the user to synchronize any changes made to the network database.

To use the offline features of the Microsoft Access Workflow Designer, the user must install the client run-time components. These components include a COM object (MODReplSvr 1.0) that is used to set up offline access and provide the synchronization of data. In addition, the user must have a copy of the Microsoft Data Engine (MSDE) or the desktop version of SQL Server installed. This is used to store the local copy of the database.

Adding Offline Access Functionality to the User Interface

Users must have a way to enable offline access if they have not taken the application offline before. This functionality is enabled using a CommandButton on the Grid form.

To add offline access functionality to the Grid form

  1. Double-click Form1 in the Project Window to display the Form1 window.

  2. Click the CommandButton object on the Toolbox, and draw a CommandButton control on the lower portion of the form. You may want to increase the height of the form or to reduce the size of the Hierarchical FlexGrid control to fit the command button on the form.

  3. Right-click Command1, and select Properties.

  4. Change the (name) property to cmdOffline and the Caption to Enable Offline. See Figure 22.

    Figure 22. Grid form with the Enable Offline button at design time

    As mentioned earlier, a Microsoft Access Workflow Designer run-time COM object is used to enable and maintain the offline functionality. To use this COM object in the application, a reference in this project must be made to this COM object.

  5. From the Project menu, select References.

  6. In the References dialog box, select the MODReplSvr 1.0 Type Library reference, and then click OK. See Figure 23.

    Figure 23. References dialog box

    Selecting this COM object enables access to this object and the methods associated with it. Code in the Click event of the Enable Offline CommandButton uses this COM object to enable offline access.

  7. Right-click Enable Offline, and then select View Code.

  8. In the Code Editor, add the following code:

    Private Sub cmdOffline_Click()
    Dim oReplSvr As New MODReplSvr
    Dim strServer As String, strDB As String
    strServer = _
      DataEnvironment1.Connection1.Properties("Data Source").Value
    strDB = _
      DataEnvironment1.Connection1.Properties("Initial Catalog").Value
    oReplSvr.Init strServer, strDB
    oReplSvr.DeleteAllSubscriptions
    oReplSvr.RegisterSubscription 0
    oReplSvr.CreateSubscription
    oReplSvr.DoInitialSync
End Sub

    This function accomplishes the following:

    • Creates an instance of the MODReplSvr object.
    • Retrieves the name of the SQL Server for the network database (Data Source) and the database name being used (Initial Catalog). These are used by the init method of MODReplSvr.
    • Calls the DeleteAllSubscriptions method to clear any subscriptions that may be already registered in case offline has been enabled previously on the client. (A subscription is a SQL Server term that defines the functionality of replicating a SQL Server publication from one database to another. The Microsoft Access Workflow Designer uses this SQL Server functionality for its offline functionality. See the SQL Server Help for more information about publications and subscriptions.)
    • Subscribes to a new publication using the RegisterSubscription method. The Issue Tracking database by default has only one publication, All Issues. In this case, the use of this one publication is hard-coded. You may want to provide functionality in your application to list all available publications in the database and make it possible for the user to select a publication to which to subscribe. See the Issue Tracking team solution Offline.htm Web page for an example of how to provide a list of additional publications.
    • Creates the publication on the local database using the CreateSubscription method.
    • Downloads the data from the network database to the local database. In addition, this method installs a set of stored procedures and triggers that are required to enable workflow on the local database.

Dynamically Changing Connection Information

When the offline database has been created, the application must provide a way for users to specify to use the offline database rather than the network database. One way is to prompt the user when the application is started to make the choice to work offline or online. You may want to provide a less intrusive interface for this, but this will give you the basics on what you must do to redirect your application to use the offline database.

When the user wants to use the offline database, the application must dynamically change the connection information stored in the Data Environment objects in the project to use the local database rather than the network database. To do this, the connection information must be changed before the connections are established. One way to change the connection is by adding code to the form's Initialize event that checks to see whether the application has been enabled for offline use and, if so, prompts the user to indicate whether to use the offline or online database.

The actual redirection of the connection information does not happen in the Initialize event but in the Data Environment objects. The Form_Initialize routine sets up a global variable to identify whether to use the offline or online database. Later, code is added to the Data Environment object to check this global variable and make changes to the connection objects based on it.

To change the connection information dynamically

  1. Right click Form1, and select View Code.

  2. Add the following code to the declarations section of the form's code module:

    Public gOffline As Boolean

  3. Add the following Form_Initialize routine to the code module:

    Private Sub Form_Initialize()
    If IsOfflineEnabled() Then
        If MsgBox("This application is enabled for offline use. " + _
             "Would you like to work offline?", _
             vbYesNo) = vbYes Then
            gOffline = True
        Else
            gOffline = False
        End If
    End If
End Sub

    This routine accomplishes the following:

    • Checks whether the application has been enabled for offline use using the custom function IsOfflineEnabled. (This custom function will be added to the application next.)
    • Displays a message box asking users if they want to use the application offline.
    • Sets the global variable to True if yes; otherwise, it is set to False.

As mentioned, a custom function is required to verify whether the application has been enabled for offline access. First, the function determines whether the online database has been enabled for offline. This information is saved in a Microsoft Access Workflow Designer modSystem table. Second, if the online database has been enabled for offline access, the function determines whether the current client has subscribed to the online database by running the Enable Offline routine added earlier.

To retrieve this information, three separate queries are run—two against the online database and one against the local database. These queries will be added as Command objects in the Data Environment object.

To create the queries required to verify offline access

  1. Double-click DataEnvironment1 in the Project Window to display the Data Environment window.

  2. Right-click Connection1, and select Add Command.

  3. Right-click Command1, and select Properties.

  4. Enter ServerOfflineEnabled for the Command Name. See Figure 24.

  5. Select SQL Statement, and enter the following SQL SELECT statement in the edit box:

    SELECT * FROM modProperties WHERE Name LIKE 'OfflineEnabled' AND Value = 1

    Figure 24. ServerOfflineEnabled Command property page

    This Command object queries the modProperties table and finds a row where the property name is OfflineEnabled and the value is 1. If a row is found, offline has been enabled for the online database.

  6. Repeat the earlier steps. Enter ClientOfflineEnabled for the Command Name. See Figure 25. Enter the following in the SQL Statement edit box:

    exec sp_modReplSubscribed ?

    Figure 25. ClientOfflineEnabled Command property page

    This Command object executes a stored procedure that uses a parameter to provide some information at run time. The parameter specifies what the current client SQL Server name is, which is what the online server uses to identify clients that have subscribed to it.

    To find out the name of the local SQL Server, a query must be executed—not against the online database but to the local SQL Server (or MSDE). Because Connection1 inside the Data Environment connects to the online database, a second Connection object must be created that connects to the local data source.

  7. Right-click DataEnvironment, and select Add Connection.

  8. Right-click Connection2, and select Properties.

  9. From the list of providers, select Microsoft OLE DB Provider for SQL Server, and click Next.

  10. Enter (local) in the Select or enter a server name field.

  11. Enter a User name and Password that is valid for your local SQL Server.

  12. Enter or select master in the Select the database on the server field.

    Using (local) for the server name specifies to use the local SQL Server no matter what the local computer is named. The database selected must be one that is always present, whether or not the database has been taken offline. In that case, the master database is used, because it always exists. See Figure 26.

    Figure 26. Data Link properties for local SQL Server

  13. Right-click Connection2, and select Add Command.

  14. Right-click Command1, and select Properties.

  15. Enter LocalServerName for the Command Name. See Figure 27.

  16. Select SQL Statement, and enter the following SQL SELECT statement in the edit box:

    SELECT @@SERVERNAME As ServerName

    Figure 27. LocalServerName Command properties

The query for this Command object returns the name of the SQL Server in the form of a recordset.

After these three Command objects are created, the DataEnvironment1 window will look similar to the one in Figure 28:

Figure 28. Data Environment with new Command objects

The queries required to verify whether the application is enabled for offline access are completed. Next, the custom function must be added that uses these Command objects to return whether the user has enabled the application for use offline.

To check whether the application is enabled for offline access

  1. Right-click Form1, and select View Code.

  2. In the Code Editor, add the following code to the form module:

    Function IsOfflineEnabled()
    DataEnvironment1.ServerOfflineEnabled
    If DataEnvironment1.rsServerOfflineEnabled.RecordCount > 0 Then
        DataEnvironment1.LocalServerName
        If DataEnvironment1.rsLocalServerName.RecordCount > 0 Then
            DataEnvironment1.ClientOfflineEnabled _
              DataEnvironment1.rsLocalServerName.Fields("ServerName")._
              Value
            If DataEnvironment1.rsClientOfflineEnabled.RecordCount _
              > 0 Then
                IsOfflineEnabled = True
            Else
                IsOfflineEnabled = False
            End If
        Else
            IsOfflineEnabled = False
        End If
    Else
        IsOfflineEnabled = False
    End If
End Function

    This function accomplishes the following:

    • Executes the ServerOfflineEnabled Command object.
    • Verifies that the server has been enabled for offline if the recordset associated with the ServerOfflineEnabled Command returns at least one row.
    • Executes the LocalServerName Command object. If no rows are returned from this Command, there was probably an error accessing the local SQL Server; so the code returns False.
    • Executes the final query, ClientOfflineEnabled Command object. Note that the local server name parameter is provided from the LocalServerName recordset.
    • Verifies that all the criteria have been met and that the application is available for offline if that Command object returns at least one row. The code returns True.
    • Returns False in all other cases.

At this point, the application can identify whether it has been enabled for offline use, and if it has, it prompts users whether they want to use the offline database. What is missing is the actual work that redirects the connection objects in the Data Environment objects to use the local database when the user selects to use the offline database.

Remember that a global variable is used to identify whether the user wants to go offline or not. Code must be added to each Data Environment object that checks this global variable. If the variable is True, the code changes the server to be the local SQL Server.

To change data source connections dynamically

  1. Right-click DataEnvironment1 in the Project Window, and select View Code.

  2. In the Code Editor, add the following code to the Initialize event of the Data Environment:

    Private Sub DataEnvironment_Initialize()
    If Form1.gOffline = True Then
        DataEnvironment1.Connection1.Properties("Data Source").Value _
          = "(local)"
    End If
    DataEnvironment1.rsIssuesBaseView.Properties("Update Criteria") _
        = 0
End Sub

The added code contains a simple IF statement that retrieves the global variable. If it is True, the Data Source property of the Connection1 object is changed to "(local)," which tells the connection to use the local SQL Server no matter what it is named. That is all that is required!

Step 6: Enable Data Synchronization

Offline support is nearly completed. The user can enable offline access to the data and start up the application that is using the data offline. The one last item required is a way to synchronize changes made offline with the online database. To do this, a Synchronize button can be added to the form that the user clicks to update the online database.

To add the synchronize button

  1. Double-click Form1 in the Project Window to display the Form1 window. See Figure 29.

  2. Click the CommandButton object on the toolbox, and draw a CommandButton on the lower portion of the form next to the Enable Offline button.

  3. Right-click Command1, and select Properties.

  4. Change the (name) property to cmdSynch and the Caption to Synchronize.

    Figure 29. Grid form with a Synchronize button at design time

  5. Right-click Synchronize, and select View Code.

  6. In the Code Editor, add the following code:

    Private Sub cmdSynch_Click()

    Dim oReplSvr As New MODReplSvr
    Dim strServer As String, strDB As String
    strServer = _
      DataEnvironment1.Connection1.Properties("Data Source").Value
    strDB = _
      DataEnvironment1.Connection1.Properties("Initial Catalog").Value
    oReplSvr.Init strServer, strDB
    oReplSvr.Synchronize

End Sub

    The code accomplishes the following:

    • Creates an instance of the MODReplSvr object.
    • Retrieves the name of the SQL Server for the network database (Data Source) and the database name being used (Initial Catalog). These are used by the init method of MODReplSvr.
    • Executes the Synchronize method of the ModReplSvr object.

That is it. Your application now has workflow and offline functionality.

Step 7: Test Offline Functionality

The offline functionality is now complete in the application. The final step is to test that functionality.

To test offline functionality

  1. From the Run menu, select Start.

    The first time you run the application, you will not be asked whether you want to work offline, because offline features have not been enabled yet. When you first start the application, you will see the Grid form with the two new buttons added. See Figure 30.

    Figure 30. Grid form at run time with offline functionality

  2. Click Enable Offline on the form.

    This may take some time, so be patient while the local database is set up for offline use. Note that the code does not provide feedback to the user. To make the application more user-friendly, you should consider adding such code. When the control returns back to the form, you will not see any noticeable differences.

  3. Exit the application by clicking the close button on the form window, and restart the application by selecting Start from the Project menu.

    A message box is displayed asking whether you want to work offline. See Figure 31.

    Figure 31. Message box for choosing to work offline

  4. Click Yes.

    The Grid form is displayed. Once again, you will not see any noticeable differences, but the data is being retrieved from the local database. To verify this, you can disconnect from the network and still access the data in the local store.

  5. Click New on the workflow toolbar, and enter in a new issue.

  6. Exit the application by clicking the Close button on the form window, and restart the application by selecting Start from the Project menu.

  7. Click No when asked whether you want to work offline.

    You will notice the rows you entered or modified while offline do not appear, because you are now working on the online database.

  8. Click Synchronize.

    Again, this may take a bit of time. When control returns to the form, the grid will look the same, because no code was added to refresh the grid automatically.

  9. Exit the application by clicking the Close button on the form window, and restart the application by selecting Start from the Project menu.

  10. Click No when asked whether you want to work offline.

    The new row you added offline is available now in the online database. The online database was updated successfully with the changes you made to the offline database.

Conclusion

Although a Microsoft Access Workflow Designer team solution typically is described as a Web application, the team solution user interface can be developed using any development tool that can access SQL Server data. In this walkthrough, you created a Visual Basic application that takes advantage of the core set of services available in the Microsoft Access Workflow Designer—essentially becoming a team solution.

By reading workflow information, the application is able to generate dynamically a workflow toolbar that guides the user in following the workflow process defined in the database. Using the MODReplSvr COM object provided by Microsoft Access Workflow Designer, the application provides offline support and synchronization with the online database. And all of this functionality is tied around a set of data-bound Visual Basic forms to provide a complete application.

Take the basic concepts illustrated in this application, and create your own team solutions in Visual Basic or any other development tool that can access SQL Server data.

For More Information

For the latest information about Microsoft Access Workflow Designer for SQL Server, see the Microsoft Office Developer Web site at http://msdn.microsoft.com/Office/.

To access Knowledge Base information, consult the Product Support section of the Microsoft Office Developer Web site.

For additional information about developing team solutions, consult the Access Workflow Designer Developer's Guide online documentation.