Export (0) Print
Expand All
Expand Minimize

Walkthrough: Creating a Workflow Application for SQL Server with an Access User Interface

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.
 

Brady Deal
Marc La Pierre
Gurpal Hundal
Rita Nikas
Microsoft Corporation

May 2001

Applies to:
   Microsoft Office XP Developer

Summary: Microsoft Office XP Developer contains the Workflow Designer for SQL Server, which provides a robust core set of services that can be used to build powerful tracking applications. The workflow process and its associated user interface typically are described as a Web application containing a set of data access pages developed in Microsoft Access. However, you can design a user interface for the workflow process using a number of different development tools, depending on the requirements of the users and the features desired in the application. (14 printed pages)

Contents

Introduction
Creating the Access User Interface
Creating the Stored Procedures
Building the Workflow Toolbar
Testing Workflow Functionality

Introduction

Although a workflow application typically is designed with a Web-based user interface, the interface can be developed using any development tool that can access Microsoft® SQL Server™ data. This walkthrough builds a user interface in Microsoft Access 2002 using core features available in the Workflow Designer for SQL Server.

Use this sample interface to apply these concepts to your own workflow applications. The code snippets throughout this white paper are provided for you to copy and paste into your application.

This white paper uses the Issue Tracking sample workflow application, which is provided as a template with the Workflow Designer for SQL Server.

Prerequisites

To follow this white paper and create the Access user interface, the following software and components must be installed on the development computer:

  • A Microsoft Windows® 2000 server set up with the Workflow Services for SQL Server
  • An instance of the Issue Tracking sample template instantiated on the server
  • Access 2002
  • In addition, to complete this walkthrough successfully, you should be familiar with:
  • Creating an Access 2002 query
  • Using Microsoft Visual Basic® for Applications code
  • Creating Microsoft SQL Server stored procedures through the Access user interface

Creating the Access User Interface

You create the connection to the Issue Tracking sample database, stored in SQL Server on the development computer, by creating a new Access project (.adp) that points to the Issue Tracking database on the development computer. The project connects the Access interface to the SQL Server database.

To create the connection to the Issue Tracking Sample Database

  1. Start Access 2002 and, from the New File window, select Project(Existing Data).
  2. Type IssueTracking.adp for the file name and click Create. The Data Link Properties dialog box appears.
  3. In the Data Link Properties dialog box, select the server where Workflow Services for SQL Server is installed.
  4. Select Windows NT Integrated security.
  5. Select the IssueTracking sample database.
  6. Click Test Connection to ensure a valid database connection.
  7. Click OK.

This successfully connects the Microsoft Access interface to the Issue Tracking database, stored on the development computer. Now, you can create new database objects, such as forms, reports, views, and tables, and modify existing database objects.

After designing and creating the form interface, users can add new issues to the Issue Tracking application or change the current state of an existing issue. The form interface helps users interact with the data stored in the workflow application.

Next, you will create the form interface, including command buttons, text boxes, and combo boxes, in Access 2002. In addition, you will set property values for these form controls. This form resides in the Access project that was created earlier.

To design the form interface

  1. In the Object Bar, select Forms, and click New.
  2. In the New Form dialog box, click Design View.
  3. Choose IssuesView for the source data.
  4. Click OK.
  5. From the Field List, drag each field onto the form.
  6. Right-click CategoryID.
  7. Highlight Change To, and click Combo Box.
  8. Repeat Step 7 for AssignedTo, PriorityID, ResolutionID, StatusID, and SubCategoryID.
  9. Right-click CategoryID. Click Properties, and specify the following property values:
    PropertyValue
    Column Count (Format Tab)2
    Column Widths (Format Tab)0";1"
    Row Source (Data Tab)SELECT Category.CategoryID, Category.Category FROM Category
    Bound Column (Data Tab)1
  10. With the Properties window still open, select the AssignedTo control on the form, and specify the following property value:
    PropertyValue
    Row Source (Data Tab)SELECT modUserList.SAMAccountName FROM modUserList ORDER BY SAMAccountName
  11. Select the PriorityID control on the form, and specify the following property values:
    PropertyValue
    Column Count (Format Tab)2
    Column Widths (Format Tab)0";1"
    Row Source (Data Tab)SELECT Priority.PriorityID, Priority.Priority FROM Priority
    Bound Column (Data Tab)1
  12. Select the ResolutionID control on the form, and specify the following property values:
    PropertyValue
    Column Count (Format Tab)2
    Column Widths (Format Tab)0";1"
    Row Source (Data Tab)SELECT Resolution.ResolutionID, Resolution.Resolution FROM Resolution
    Bound Column (Data Tab)1
  13. Select the StatusID control on the form, and specify the following property values:
    PropertyValue
    Column Count (Format Tab)2
    Column Widths (Format Tab)0";1"
    Row Source (Data Tab)SELECT Status.StatusID, Status.Status FROM Status
    Bound Column (Data Tab)1
  14. Select the SubCategoryID control on the form, and specify the following property values:
    PropertyValue
    Column Count (Format Tab)2
    Column Widths (Format Tab)0";1"
    Bound Column (Data Tab)1
  15. Select the StartDate text box. In the Data tab, change the Default Value property to Date().
  16. Click File, click Save, and then save the form as frmIssueTracking.

After adding events to the form, users can add and save new records, save the new workflow state for a record, and build the workflow state action toolbar with appropriate next workflow states. Without these events, it would be difficult to interact with new or existing data stored in the Issue Tracking application.

The next step is to add events to the form by adding code in the Visual Basic Environment.

To add events to the form

  1. From the View menu, click Code to open the Visual Basic Editor.
  2. Add the following After Update event procedure to filter the SubCategoryID field based on the selected CategoryID:
    Private Sub CategoryID_AfterUpdate()
    On Error GoTo Err_Trap
    If Not IsNull(Me!CategoryID.Column(0)) Then
    Me!SubCategoryID.RowSource = "SELECT " & _ 
    "[SubCategory].[SubCategoryID], " & _ 
    "[SubCategory].[SubCategory] " & _
    "FROM [SubCategory] WHERE " & _ 
    "[CategoryID] = " & _
    Me!CategoryID.Column(0)
       Else
          Me!SubCategoryID.RowSource = ""
       End If
    Err_Exit:
    Exit Sub
    Err_Trap:
    MsgBox Err.Description, vbExclamation, _
    Err.Number
    GoTo Err_Exit
    End Sub
    
  3. From the View menu, select Microsoft Access to switch back to the form.
  4. Using the Toolbox, add a command button to the form.
  5. Right-click the new command button, and select Properties. Specify the following property values:
    PropertyValue
    Caption (Format Tab)Save Record
    Name (Other Tab)CmdSaveRecord
  6. In the Visual Basic Editor, add the following On Click event procedure for the cmdSaveRecord command button:
    Private Sub cmdSaveRecord_Click()
    On Error GoTo Err_Trap
    
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, _ 
    acSaveRecord, , acMenuVer70
    UnloadActions
    BuildActions
    
    Err_Exit:
    Exit Sub
    Err_Trap:
    MsgBox Err.Description, vbExclamation, _
    Err.Number
    GoTo Err_Exit
    End Sub
    

    This code saves the current record and rebuilds the actions in the workflow toolbar that you will create later in this white paper.

  7. Add the following code to the current event of the form to trap recordset movement:
    Private Sub Form_Current()
    On Error GoTo Err_Trap
    UnloadActions
    
    If Me.NewRecord = False Then
    BuildActions
    End If
    
    Err_Exit:
       Exit Sub
    Err_Trap:
       MsgBox Err.Description, vbExclamation, Err.Number
       GoTo Err_Exit
    End Sub
    
  8. Save and then close the form.

Now, you have added events (through code) to the user interface form successfully. This makes it possible for users to see the Save Record command button on the form and see the appropriate subcategories available for a specific category. For example, if the issue being added or modified by the user is designated with a category of Accounting, the user can select a subcategory of Payroll, Budget, Audit, and Balance Sheet.

Creating the Stored Procedures

Now that the form is created, you create stored procedures to return available events and next states available. There are two stored procedures: Actions and NextState. The Actions stored procedure returns a list of all available events defined for the workflow process associated with the Issues table. Without the Actions stored procedure, users would be able to see the workflow toolbar, but the toolbar would not display the next available workflow state for the current record.

The NextState stored procedure retrieves the next state value when transitioning from one state to another. Without the NextState stored procedure, the corresponding integer value for the next state would not be updated properly.

To create the stored procedures

  1. From the Object Bar, click Queries, and select New.
  2. Select Design Stored Procedure.
  3. In the Add Tables dialog box, click Close.
  4. From the View menu, select SQL View.
  5. Copy and paste the following code into the SQL View pane to create the Actions stored procedure, which returns a list of all available events defined for the workflow process associated with the Issues table:
    Create Procedure Actions
       @StateID int
    As
    SELECT IssuesWorkflowActions.Caption, Min(IssuesWorkflowView .Event) 
    As Event, 
    Min(IssuesWorkflowActions.Position) As Position 
    FROM IssuesWorkflowActions INNER JOIN IssuesWorkflowView ON 
    IssuesWorkflowActions.WorkflowId = IssuesWorkflowView.Id 
    WHERE (IssuesWorkflowView.State = @StateID) AND (IssuesWorkflowView 
    .Event <> 'OnUpdate')
    GROUP BY IssuesWorkflowActions.Caption 
    ORDER BY MIN(IssuesWorkflowActions.Position)
    

    The IssuesWorkflowActions table contains a row for each event 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 event, 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.

  6. Copy and paste the following code into the SQL View pane to create the NextState stored procedure, which retrieves the next state value when transitioning from one state to another. For example, in the Issue Tracking sample database when the user executes the Resolve event, 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.
    Create Procedure NextState
       @strCaption nvarchar(4000),
       @intState int
    As 
    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 = @strCaption) AND 
    (IssuesWorkflowView.State = @intState OR IssuesWorkflowView.State = 
    - 1)
    

Now, you have created the Actions and NextState stored procedures. This makes it possible for users to see the next available workflow state for the current record. In addition, you save the appropriate integer value, corresponding to the new workflow state, when transitioning from one state to another.

Building the Workflow Toolbar

Now that the form and stored procedures are all working, it is time to add the workflow toolbar. After adding the workflow toolbar, you will run and test the workflow toolbar to ensure the proper workflow events appear on the workflow toolbar.

Creating the Workflow Toolbar

The workflow information is available from the Actions stored procedures added to the database in the previous step. The next step is to use the information returned from the Actions stored procedure to build a toolbar listing the available events.

The user interface created here contains a series of command buttons, one for each event. This basic example 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 events are available.

To create the workflow toolbar

  1. Click on Forms in the Object Bar, and open the frmIssueTracking form in Design view.
  2. Click the Command Button object on the Toolbox, and place three command buttons on the form.
  3. Select all three command buttons.
  4. Right-click and choose Properties.
  5. Set the command buttons' Visible property (in the Format tab) to No.
  6. Name the three command buttons cmd1, cmd2, cmd3, respectively.
  7. Open the View menu, and click Code to open the Visual Basic Editor.
  8. Add the following code (the BuildActions function):
    Public Function BuildActions()
    On Error GoTo Err_Trap
    Dim ActRS As New ADODB.Recordset
    Dim intCounter As Integer
    
    If Not Me.NewRecord Then
    ActRS.Open "Exec Actions @StateID = " _ 
       & Me!StatusID, _
             CurrentProject.Connection, _
             adOpenForwardOnly, adLockReadOnly
       intCounter = 1
       While Not ActRS.EOF
             Me("cmd" & intCounter).Caption = _
                   ActRS("Caption").Value
             Me("cmd" & intCounter).ControlTipText = _
                   ActRS("Caption").Value
             Me("cmd" & intCounter).Tag = _
                   ActRS("Event").Value
             If Me("cmd" & intCounter).Visible = _ 
                   False Then
             Me("cmd" & intCounter). _
                               Visible = True
             End If
             intCounter = intCounter + 1
             ActRS.MoveNext
       Wend
    ActRS.Close
    End If
    
    Err_Exit:
    Set ActRS = Nothing
    Exit Function
    Err_Trap:
    MsgBox Err.Description, vbExclamation, Err.Number
    GoTo Err_Exit
    End Function
    

    This code makes it possible to do the following:

    • Execute the Actions stored procedure.
    • Walk through each row returned from the Actions stored procedure.
    • Toggle the command buttons' Visible property based on the number of events found.
    • Change the caption of each command button to the caption defined in the workflow event information. This code also uses the Tag property of each command button to store the event name.
    • Navigate to the next event.
  9. Add the following code, the UnloadActions function, to toggle the command buttons to an invisible state:
    Function UnloadActions()
       Me!cmd1.Visible = False
       Me!cmd2.Visible = False
       Me!cmd3.Visible = False
    End Function
    

Running the Form

Run the form to test the dynamic building of the workflow toolbar events. In the workflow toolbar, you should see a series of buttons on the form that lists each of the events available in the workflow process of the Issue Tracking database.

To run the form

  • From the View menu, select Form View.

If there are no records in the Issues table, you must add one or two manually after you make sure you have a valid logon.

Note   If you enter a record and save it, you must reopen the frmIssueTracking form, so you can see the available workflow events on the workflow toolbar.

To ensure you have a valid logon

  1. Open the Workflow Manager for SQL Server.
  2. Click on the User Information tab.
  3. Make sure that the domain and user name you typed in are listed. If the domain and user name do not appear in the list, click the Synchronize Now button to synchronize the user list with Active Directory.

Next, you are ready to add a record.

To add a record manually

  1. Open the Issues Table.
  2. In the AssignedTo field, type in your domain name and user name separated by a backslash ("\"), for example, MyDomain\JohnDoe.
  3. Press Shift+Enter to save the record.

Executing Events on the Toolbar

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

  • OnChange   Modifies the workflow state for a row that is changed to a different state, and modifies a field other than the workflow field for a transition within a state.
  • OnDelete   Deletes a row from the database.

Except for the OnDelete event, the user should have the opportunity to view and modify all fields from the Issues table. In addition, 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 events are executed by clicking a button on the workflow toolbar. Therefore, code must be added to each command button click event that identifies the button clicked and executes functionality based on the event associated with the button event. Remember that when the workflow toolbar is generated, the Tag property of the control array is set to the event associated with the user action. Code must retrieve this value and, based on the value, execute the appropriate functionality.

To make it possible for the form to execute events

  1. With the Visual Basic Editor open, add the following code to handle each command button’s click event.
    Private Sub cmd1_Click()
    On Error GoTo Err_Trap
    
    ActionCommand Me!cmd1.Tag, Me!cmd1.Caption
    
    Err_Exit:
    Exit Sub
    Err_Trap:
    MsgBox Err.Description, vbExclamation, Err.Number
    GoTo Err_Exit
    End Sub
    
    Private Sub cmd2_Click()
    On Error GoTo Err_Trap
    
    ActionCommand Me!cmd2.Tag, Me!cmd2.Caption
    
    Err_Exit:
    Exit Sub
    Err_Trap:
    MsgBox Err.Description, vbExclamation, Err.Number
    GoTo Err_Exit
    End Sub
    
    Private Sub cmd3_Click()
    On Error GoTo Err_Trap
    
    ActionCommand Me!cmd3.Tag, Me!cmd3.Caption
    
    Err_Exit:
    Exit Sub
    Err_Trap:
    MsgBox Err.Description, vbExclamation, Err.Number
    GoTo Err_Exit
    End Sub
    

    Each command button's click event calls the ActionCommand procedure. This procedure contains two sections, the OnDelete action and the OnTransition action. The OnDelete action does the following:

    • Makes sure the event is OnDelete. If the event is OnDelete, the OnDelete action displays a message box to confirm that the user wants to delete the current row.
    • Selects the row by executing the Delete method if the user confirms deletion.
  2. With the Visual Basic Editor open, add the following code to create the ActionCommand procedure.
    Private Sub ActionCommand(strEventTag As String, _
          strCmdCaption As String)
    
    On Error Goto Err_Trap
    
    Dim NextStateRS As New ADODB.Recordset
    Dim RecClone As Object
    Dim intState As Integer, intNextState As Integer
    Dim strCaption As String
    
    Select Case strEventTag
          Case "OnDelete"
                DoCmd.DoMenuItem acFormBar, acEditMenu, _ 
                      8, , acMenuVer70
                DoCmd.DoMenuItem acFormBar, acEditMenu, _ 
                      6, , acMenuVer70
                DoEvents
                Me!CreatedBy.SetFocus
                UnloadActions
                BuildActions
    
  3. Add the following code immediately after the previous code to provide functionality for the OnTransition action, which is central to workflow functionality. The status to which the row is set depends on the user’s action. The OnTransition action does the following:
    • Makes sure the event is OnChange. If the event is OnChange, the OnChange action retrieves the status of the current row and the caption of the button that the user clicked.
    • Passes this information as parameters to execute the NextState stored procedure. After execution, the returned recordset contains the next StatusID to which that row is set.
    • Retrieves and sets the StatusID column of the current row.
    Case "OnTransition"
          intState = Me!StatusID
          strCaption = strCmdCaption
    NextStateRS.Open "Exec NextState @intState = " & intState _ 
                & ", @strCaption = '" & strCmdCaption & "'", _
                CurrentProject.Connection, adOpenForwardOnly, _
                adLockReadOnly
          intNextState = NextStateRS("Next_State").Value
          Me!StatusID = intNextState
          NextStateRS.Close
          DoCmd.DoMenuItem acFormBar, acRecordsMenu, _
                acSaveRecord, , acMenuVer70
                Me!CreatedBy.SetFocus
          UnloadActions
          BuildActions
    End Select
    
    Err_Exit:
          Set NextStateRS = Nothing
          Exit Sub
    Err_Trap:
          MsgBox Err.Description, vbExclamation, Err.Number
          DoCmd.DoMenuItem acFormBar, acEditMenu, _
                acUndo, , acMenuVer70
          GoTo Err_Exit
    End Sub
    

Now, you have added the workflow toolbar, including adding code to toggle the Visible property for command buttons, and tested the workflow toolbar to ensure the proper workflow events appeared on the workflow toolbar.

Testing Workflow Functionality

The previous steps complete all workflow functionality for the application. Next, you will run the Issue Tracking form, making sure you can create and save new Issues. Follow these steps to test the full workflow functionality.

To test the full workflow functionality

  1. On the View menu, click Form View. The frmIssuesTracking form is displayed with the proper events available.
  2. On the record navigation toolbar, click New Record.
  3. Select a user in the AssignedTo field, and add information to the Description field. Because this is a new issue, you are not required to add anything to the ResolutionDescription or ResolutionType fields.
  4. Click Save Record.
    Note
       
    The Issue Tracking sample database contains workflow scripts that require the value of the AssignedTo field to be associated with a user contained in the user directory. If you enter a user who is not in the user directory in the AssignedTo field, you receive an error message.
  5. In the ResolutionDescription field, type in a valid ResolutionDescription, such as, This is a test, and select a valid ResolutionID.
    Note
       
    You must enter a value for these fields, because the Issue Tracking sample application contains workflow scripts that validate these fields on a transition from Active to Resolved.
  6. When the record is saved, on the workflow tool bar, click Resolve. The form is updated, and you see the status of the entered issue change from Active to Resolved. In addition, the workflow toolbar has been updated automatically to reflect the change.

Test the rest of the events available by selecting issues and clicking events on the workflow toolbar.

Now, you created an Access form interface that takes advantage of the core set of services available in the Workflow Designer. By reading workflow information, the interface is able to generate a workflow toolbar dynamically that guides the user in following the workflow process defined in the database.

For more information regarding the Issue Tracking sample application, see “Workflow Functionality in the Issue Tracking Sample Application” in Microsoft Office XP Developer online Help. For more workflow designer information, see the following technical articles in the MSDN Library:

Show:
© 2014 Microsoft