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

Article
06/14/2014

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)

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

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

In the Object Bar, select Forms, and click New.
In the New Form dialog box, click Design View.
Choose IssuesView for the source data.
Click OK.
From the Field List, drag each field onto the form.
Right-click CategoryID.
Highlight Change To, and click Combo Box.
Repeat Step 7 for AssignedTo, PriorityID, ResolutionID, StatusID, and SubCategoryID.
Right-click CategoryID. Click Properties, and specify the following property values:

Property Value

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
With the Properties window still open, select the AssignedTo control on the form, and specify the following property value:

Property Value

Row Source (Data Tab) SELECT modUserList.SAMAccountName FROM modUserList ORDER BY SAMAccountName
Select the PriorityID control on the form, and specify the following property values:

Property Value

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

Property	Value
Row Source (Data Tab)	SELECT modUserList.SAMAccountName FROM modUserList ORDER BY SAMAccountName

Select the ResolutionID control on the form, and specify the following property values:

Property	Value
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

Select the StatusID control on the form, and specify the following property values:

Property Value

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
Select the SubCategoryID control on the form, and specify the following property values:

Property Value

Column Count (Format Tab) 2

Column Widths (Format Tab) 0";1"

Bound Column (Data Tab) 1
Select the StartDate text box. In the Data tab, change the Default Value property to Date().
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

From the View menu, click Code to open the Visual Basic Editor.

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

From the View menu, select Microsoft Access to switch back to the form.
Using the Toolbox, add a command button to the form.
Right-click the new command button, and select Properties. Specify the following property values:

Property Value

Caption (Format Tab) Save Record

Name (Other Tab) CmdSaveRecord

Property	Value
Caption (Format Tab)	Save Record
Name (Other Tab)	CmdSaveRecord

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.

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

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

From the Object Bar, click Queries, and select New.
Select Design Stored Procedure.
In the Add Tables dialog box, click Close.
From the View menu, select SQL View.
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.

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.

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.

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

Click on Forms in the Object Bar, and open the frmIssueTracking form in Design view.
Click the Command Button object on the Toolbox, and place three command buttons on the form.
Select all three command buttons.
Right-click and choose Properties.
Set the command buttons' Visible property (in the Format tab) to No.
Name the three command buttons cmd1, cmd2, cmd3, respectively.
Open the View menu, and click Code to open the Visual Basic Editor.

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.

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

Open the Workflow Manager for SQL Server.
Click on the User Information tab.
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

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

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

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.

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

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

On the View menu, click Form View. The frmIssuesTracking form is displayed with the proper events available.
On the record navigation toolbar, click New Record.
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.
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.
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.
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:

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

Contents

Introduction

Prerequisites

Creating the Access User Interface

Creating the Stored Procedures

Running the Form

Testing Workflow Functionality

Additional resources

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

Contents

Introduction

Prerequisites

Creating the Access User Interface

Creating the Stored Procedures

Building the Workflow Toolbar

Creating the Workflow Toolbar

Running the Form

Executing Events on the Toolbar

Testing Workflow Functionality

Additional resources