Walkthrough: Creating a Workflow Application for SQL Server with an Access User Interface
Marc La Pierre
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)
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.
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
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
- 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
- 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.
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.
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
- 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.
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
- 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.
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.
- In the ResolutionDescription field, type in a valid ResolutionDescription, such as, This is a test, and select a valid ResolutionID.
- 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: