Team LiB
Previous Section Next Section

SQL Server Form Issues

As noted earlier, one of the great strengths of Access projects is that you can program them in a way that's very similar to programming Access database files. Therefore, many of the topics covered in Chapter 5 apply to the use of forms in Access projects. However, some issues are unique to Access projects. (This section considers two of these issues—see the first two subsection titles.) In addition, although not solely applicable to Access projects, some other topics are more salient for them. Server filters, which we'll discuss in this section, is such a subject.

Specifying a Form's RecordsetType Property in Access Projects

Forms have a RecordsetType property in both Access projects and Access database files, but the settings for this property—along with the capabilities they provide—differ between the two kinds of files. Access projects offer two settings for a form's RecordsetType property. The default setting is an updatable snapshot. This setting allows a user to edit all fields bound to any table. You can programmatically assign this setting with a value of 4. A form's RecordsetType property settings do not belong to an enum class. Therefore, you have to use a numeric constant. When your applications call for read-only access to data, you can use the snapshot setting, which has a numeric value of 3. With this setting, users can use a form to browse data, but they cannot edit it. In addition, they cannot add or delete records from the record source for a form.

The following procedure illustrates the syntax for working with a form's RecordsetType property. Although you can run the procedure automatically and have it provide feedback, I urge you to step through the code. This will give you a better feel for the performance of the RecordsetType settings.

The procedure has a compiler flag that simplifies working with the frmOrderDetails form in two ways. When you run the ReadWriteOrReadOnly procedure using a value of True for the ReadOnly compiler constant, as in the following code listing, the procedure assigns a snapshot setting to the form. This makes it impossible to update the Order Details table through the form. If you change the ReadOnly compiler constant to False, the form opens with an updatable snapshot setting for its RecordsetType property. This enables users to manually modify the Orders Details table through the frmOrderDetails form.

The procedure simulates a manual update of the data through the form by attempting to modify the Quantity field on the form's first record. If the form has an updatable snapshot setting, the update succeeds. Otherwise, the initial attempt to update the form fails. However, the procedure asks the user whether she is sure she wants to edit the quantity. When the user responds yes, the procedure resets the RecordsetType property so that the form provides read-write access to its data. Then the procedure tries again.

The following procedure begins by opening the frmOrderDetails form. Then it sets the form's RecordsetType property to conReadOnly. A Const declaration assigns a value of 3 to conReadOnly. This is the RecordsetType setting for a snapshot that does not permit updates, additions, or deletions from a form's record source. Then the sample multiplies the value of the Quantity field by 100 and stores the result in the text box. Next the code invokes the form's Refresh method, updating the database on the server with the value in the form's Quantity text box. After updating the server, the procedure ends with an Exit Sub statement. If you run the sample with the code listing's value for the ReadOnly compiler constant, it won't take the main path through the program. Instead, when the procedure attempts to write over the Quantity value in the text box, it will generate error number -   2147352567. The procedure traps this error and prompts the user, asking whether he's sure about wanting to edit the value. If the user responds yes, the procedure resets the form's RecordsetType property to conReadWrite, which has a numeric value of 4. This setting permits the procedure to succeed at updating the record source behind the form.

If you run this procedure a second time without restoring the Quantity field for the first record to its initial value (or some other relatively small value), you can generate an overflow. This is easy to do because Quantity has a Smallint data type with a maximum value of 32,767 and the expression for revising the value of Quantity multiplies it by 100. The procedure traps this overflow error and prints a message to remind the user that one common cause of this error is failing to reset the value of Quantity for the first record to its initial value of 12. The procedure leaves the reset process to the user. However, it does offer another procedure for accomplishing this process automatically.

'Step through this procedure to see the flow and understand
'its logic
Sub ReadWriteOrReadOnly() 
On Error GoTo ReadWrite_Trap
   
Dim frm1 As Form
Dim int1 As Integer
Const conReadOnly = 3
Const conReadWrite = 4
#Const ReadOnly = True
   
'Open frmOrderDetails, set reference to it, and
'assign ReadOnly setting to RecordsetType property
DoCmd.OpenForm "frmOrderDetails"
Set frm1 = Forms("frmOrderDetails")
#If ReadOnly = True Then
    frm1.RecordsetType = conReadOnly
#Else
    frm1.RecordsetType = conReadWrite
#End If
   
'Multiply the value of Quantity for the first record by 100,
'and refresh value on server
int1 = frm1.Quantity
frm1.Quantity = 100 * int1
frm1.Refresh
   
'Close form and exit sub
ReadWrite_Exit:
DoCmd.Close acForm, frm1.Name
Exit Sub
   
ReadWrite_Trap:
If Err.Number = -2147352567 And frm1.RecordsetType = conReadOnly Then
'If data is not updatable and RecordsetType is ReadOnly, 
'offer option to change setting
    If MsgBox("Are you sure you want to edit quantity?", _
        vbYesNo, _
        "Programming Microsoft Access 2003") = vbYes Then
        frm1.RecordsetType = conReadWrite
        Resume
    Else
        Resume ReadWrite_Exit
    End If
ElseIf Err.Number = 6 Then
'If it is an overflow error, you might be able to recover
'by restoring Quantity to its initial value
    MsgBox "Quantity may exceed its limit; " & _
        "its current value is " & frm1.Quantity & ".  " & _
        "Consider running RestoreFirstQuantityTo12", _
        vbInformation, _
        "Programming Microsoft Access 2003"
Else
    Debug.Print Err.Number; Err.Description
End If
    
End Sub

If a user runs this procedure with a ReadOnly compiler constant of True but doesn't accept the option to revise the value of the Quantity field, the sample leaves the RecordsetType property equal to conReadOnly. This complicates working with the form because you cannot update its data manually without first resetting the RecordsetType property to an updatable snapshot. This is especially important when your application requires you to update form field values, such as when you want to restore the value of Quantity in the first record. The following code sample, RestoreFirstQuantityTo12, illustrates this process of attempting to restore the Quantity field in the first record to its initial value of 12.

Sub RestoreFirstQuantityTo12() 
On Error GoTo RestoreFirstQuantity_Trap
Dim frm1 As Form
Const conReadOnly = 3
Const conReadWrite = 4
   
'Open frmOrderDetails, set reference to it, and
'assign ReadOnly setting to RecordsetType property
DoCmd.OpenForm "frmOrderDetails"
Set frm1 = Forms("frmOrderDetails")
   
'Restore Quantity with saved value
frm1.Quantity = 12
frm1.Refresh
   
'Close form
DoCmd.Close acForm, frm1.Name
   
RestoreFirstQuantity_Exit:
Exit Sub
   
RestoreFirstQuantity_Trap:
If Err.Number = -2147352567 And frm1.RecordsetType = conReadOnly Then
'If data is not updatable and RecordsetType is ReadOnly, 
'offer option to change setting
    If MsgBox("Are you sure you want to restore quantity to 12?", _
        vbYesNo, _
        "Programming Microsoft Access 2003") = vbYes Then
        frm1.RecordsetType = conReadWrite
        Resume
    End If
Else
    Debug.Print Err.Number, Err.Description
End If
   
End Sub

Processing Hyperlinks in an Access Project Form

Access database files permit a Hyperlink data type that embraces and extends the standard hyperlinks found on Web pages. Recall from Chapter 5 that the Hyperlink data type permits you to navigate from Access to Web pages on a local intranet or the Internet. Additionally, applications can use hyperlinks to let users navigate between database objects in the current database file, another Access database file, or any Office document file. A hyperlink data value can also open the e-mail package on a workstation with a specified recipient and subject. Access projects do not offer a Hyperlink data type. This is because SQL Server does not directly support such a data type. However, Access projects do permit the setting of an IsHyperlink property for form fields in text box and combo box controls. When the value of this property is True, Access interprets the value in a control as though it were a hyperlink. When the IsHyperlink property is False, the control's contents appear as a normal text field.

Recall that a Hyperlink data type can have up to four segments. The pound sign (#) delimits the segments (or elements) from one another within a field. The first segment is the text that a hyperlink displays. By default, a hyperlink field displays the combination of its second and third segments, but you can override this setting with a text label for the first segment. The second and third elements of a hyperlink value designate the destination to which the link navigates. These two elements can take on different meanings depending on the type of destination. The links that appear in the next sample illustrate the broad range of possibilities. The final hyperlink segment represents the text that appears when the cursor hovers over a link. (Search Access Help for "About Hyperlinks" for more in-depth coverage of hyperlink parts, and see Chapter 5 for a couple of practical uses for hyperlinks within Access applications.)

Before you can use hyperlinks in an Access project, you need a table of hyperlink values. The following code sample demonstrates one approach to creating such a table. The sample also includes the Drop_a_table procedure. This short procedure initially appeared in Chapter 11. The following sample repeats the listing so that you have it readily available as you move through the other samples in this chapter. This sample starts by referencing the current project with a Connection object and then drops any prior version of the Links table.

This Links table stores the hyperlinks that the sample creates. After dropping an earlier version of the table, the code creates a new table that has four columns. The first of these is an autonumber field that serves as the table's primary key. The second column holds a descriptive name for the field. This serves the same purpose as the first hyperlink element, but it's not a physical part of the hyperlink. The third column contains the actual hyperlink data value. This field is 128 characters long, which is sufficient for the sample data. Keep in mind, however, that each hyperlink segment can contain up to 2,000 characters. The fourth column designates the purposes of the hyperlink—for example, to open a Web page, an e-mail message, or a database object. This column is limited to six characters.

The sample procedure concludes by exercising the INSERT INTO statement five times. Each instance of the INSERT INTO statement adds another link to the Links table. The first link in the table illustrates the format for specifying a hyperlink to a Web page. This link assigns ProgrammingMSAccess.com as the display text that appears when a user navigates to http://www.programmingmsaccess.com . The second link starts the e-mail package on a workstation. This link specifies my e-mail address and a subject line of "Your book is great!" (Feel free to use this link to send me messages with feedback about the book or suggestions for ways that I can improve it in future editions.) The third link indicates the format of a hyperlink that points at the Links table in the current project. The fourth link points at the Employees table in the Northwind.mdb file. This link specifies the default location for the file. The final hyperlink navigates to a bookmark on a Web page. A bookmark is a reference location on a page. In this instance, the bookmark points to descriptive materials for the prior edition of this book.

Sub CreateLinksTable()
Dim str1 As String
Dim cnn1 As ADODB.Connection
Dim TableName As String
   
'Point a connection object at the current project
Set cnn1 = CurrentProject.Connection
   
'Delete the table if it exists already
TableName = "Links"
Drop_a_table cnn1, TableName
   
'Create the table
str1 = "CREATE TABLE " & TableName & " " & _
    "( " & _
    "LinkID int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED, " & _
    "LinkName varchar(96) NULL , " & _
    "LinkURL varchar(128) NOT NULL , " & _
    "LinkType varchar(6) NULL " & _
    ")"
cnn1.Execute str1
   
str1 = "INSERT INTO " & TableName & " " & _
    "VALUES('Link to Web page', " & _
        "'ProgrammingMSAccess.com#" & _
        "http://www.programmingmsaccess.com', " & _
        "'page') " & _
    "INSERT INTO " & TableName & " " & _
    "VALUES('Start email for praise', " & _
        "'Link for email#mailto:rickd@cabinc.net" & _
        "?subject=Your book is great!#', " & _
        "'email') " & _
    "INSERT INTO " & TableName & " " & _
    "VALUES('Link for tables in current project', " & _
        "'Links##Table Links', " & _
        "'object') " & _
    "INSERT INTO " & TableName & " " & _
    "VALUES('Link to a table in a database file', " & _
        "'Link to Access database file table#" & _
        "../../Program Files/Microsoft Office/" & _
        "Office11/Samples/Northwind.mdb#Table Employees', " & _
        "'object') " & _
    "INSERT INTO " & TableName & " " & _
    "VALUES('Link for bookmark on a Web page', " & _
        "'Bookmark link#http://www.programmingmsaccess.com/thebook/" & _
        "#A2002', 'page') "
cnn1.Execute str1
   
'Refresh Database window to show new table
RefreshDatabaseWindow
   
End Sub
   
Sub Drop_a_table(cnn1 As ADODB.Connection, TableName As String)
   
'Delete the table if it exists already
str1 = "IF EXISTS (SELECT TABLE_NAME " & _
    "FROM INFORMATION_SCHEMA.TABLES " & _
    "WHERE TABLE_NAME = '" & TableName & "') " & _
    "DROP TABLE " & TableName
cnn1.Execute str1
   
End Sub

Figure 12-11 shows an excerpt from the Links table that contains the five column values in the third column. Recall that this column holds the actual hyperlink data values. Use these sample hyperlinks to gain an appreciation of the various formatting options offered by the Access Hyperlink data type. Also, notice that the links in this table are not active. This is because SQL Server has no Hyperlink data type. SQL Server tables can hold hyperlink data, but they can't interpret its segments. Therefore, Access displays the values in the third column of the Links table as text values.

Click To expand
Figure 12.11: Column values containing hyperlink data from the CreateLinksTable procedure.

Figure 12-12 shows the frmLinks table that you can create with the AutoForm Wizard for the Links table. The most obvious feature is that the LinkURL field appears as a hyperlink. Unlike the third column in the Links table, the LinkURL field on the form is an active hyperlink (meaning users can activate the link by clicking it). To activate the table's links, you can set the LinkURL control's IsHyperlink property to True in VBA, or you can select Yes from the control's Properties dialog box for its IsHyperlink property. If you don't perform either of these steps, the form displays the LinkURL column values as ordinary text rather than as functioning links.

Click To expand
Figure 12.12: The frmLinks form that displays values from the Links table.

Programming Form Server Filters

Server filters operate at the database server, independently of the traditional form Filter property and the ActiveX Data Objects (ADO) recordset Filter property. In addition, the programming interface is different for all three of these filtering techniques. A server filter also operates differently than the WHERE clause syntax for the OpenForm method. Recall that the WHERE clause syntax for the OpenForm method lets you determine the records available to a form when it loads. The other filter methods allow an application to dynamically change the filtered records after the form loads.

Unlike a traditional filter, which operates on a form's local data cache, a server filter operates at the server. With a server filter, your applications can speed up performance by downloading smaller record sources for a form. When users finish working with a subset of a larger record source, they can filter for a new subset from the larger source. Server filters for forms apply to bound forms. This makes a server filter easier to use than the ADO Filter property, which applies to an ADO recordset rather than a form. Unlike the ADO Filter property, a server filter can accept SQL strings that reference multiple fields.

Figure 12-13 presents a sample form that we'll use to examine programming techniques for the form's server filter property. One good way to familiarize yourself with the flexibility provided by the server filter is to use this form. The name of the sample form is frmvwEmployeesOrderDetails, and you'll find it in Chapter12.adp. The record source for this sample form is the vwEmployeesOrderDetails view. The view merely links the Employees table to the Order Details table and then selects a subset of the columns from both tables. The form always opens without any filters. The sample in Figure 12-13 shows a total of 2,155 records in the record source for the form. You can elect to filter by the employee last name, the discount for an order, or both. Any filtering reduces the form record count to those records that satisfy the filter.

Click To expand
Figure 12.13: A sample form in the chapter's Access project that demonstrates how to program the ServerFilter property.

You can launch filtering by making selections from one or both combo boxes at the bottom of the form and clicking the command button with the caption Filter. When the form opens, the combo boxes show all employees and all discount percents. After a user selects an employee from the Last Name combo box, the other combo box shows just the discount percents for that employee. This prevents you from specifying a filter that returns no records. Clicking the command button labeled Show All clears any existing filters. The Show All button also clears both combo boxes and resets the combo box for discount percents so that it shows the discounts for all employees.

A server filter setting doesn't take effect until you refresh a form against the server. Recall that the filter operates at the server level. The invocation of the Refresh method makes a filter property operate. When you load a form that was closed with a filter in place, the filter setting persists. The sample form in Figure 12-13 addresses this behavior by clearing the server filter property setting when the form opens. This synchronizes the server filter setting with a newly opened form.

The following set of event procedures shows the code controlling the form that appears in Figure 12-13. The Form_Open event procedure prepares the form for filtering. After setting the caption, the code clears any existing filter from a prior session by setting the form's ServerFilter property to a zero-length string and refreshing the form against the server. Next the procedure sets the RowSource property for the two combo boxes. These settings display all employees and all distinct discount percents. To maintain the integrity of the filter criteria settings, the procedure limits the combo boxes to the items in their row source specification. The Form_Open event concludes by clearing the two filter combo boxes. This brings the display in line with the ServerFilter property setting made at the top of the procedure.

Private Sub Form_Open(Cancel As Integer)
Dim str1 As String
   
'Assign form's caption
Me.Caption = "Server Filter Sample"
   
'Clear any filter from a prior session
Me.ServerFilter = ""
Me.Refresh
   
'Develop row source string for cboLastNames
'and assign it
str1 = "SELECT LastName " & _
    "FROM Employees "
Me.cboLastNames.RowSource = str1
   
'Develop row source string for cboDiscounts
'independent of cboLastNames and assign it
str1 = "SELECT DISTINCT Discount " & _
    "FROM vwEmployeesOrderDetails "
Me.cboDiscounts.RowSource = str1
   
'Restrict combo boxes to list and clear them
Me.cboDiscounts.LimitToList = True
Me.cboLastNames.LimitToList = True
Me.cboDiscounts = Null
Me.cboLastNames = Null
   
End Sub

The cboLastNames_AfterUpdate event procedure revises the RowSource property setting for the combo box that shows discount percents. After a user selects an employee's last name, this event procedure restricts the selection list from the second combo box to just those discount percents that the selected employee offered on one or more occasions. The procedure also clears the combo box for showing discounts. This setting was relevant when the user previously filtered by discount percent.

Private Sub cboLastNames_AfterUpdate()
Dim str1
   
'Develop row source string for cboDiscounts
'and assign it
str1 = "SELECT DISTINCT Discount " & _
    "FROM vwEmployeesOrderDetails " & _
    "WHERE LastName = '" & cboLastNames & "'"
Me.cboDiscounts.RowSource = str1
   
'Clear prior setting from cboDiscounts
Me.cboDiscounts = ""
   
End Sub

The heart of the application is the cmdFilter_Click event procedure. This procedure reads the two combo box settings and assigns a value to the form's ServerFilter property based on the combo box values. The procedure closes by refreshing the form against the server to apply the ServerFilter property for the form. The procedure offers four possible paths for setting the ServerFilter property. The first applies to cases in which the Discount combo box has a selection but the Last Name combo box is blank. The second path is for the reverse scenario—a user selects an employee name but does not specify a discount amount. The third path uses both an employee last name and a discount percent to designate a filter. The final path clears the filter when both combo boxes are set to Null. This situation can occur when a user manually clears both boxes of their previous filter selections and clicks the Filter command button.

Private Sub cmdFilter_Click()
Dim str1 As String
   
'Set filter based on combo box settings
If IsNull(Me.cboLastNames) And _
    IsNull(Me.cboDiscounts) = False Then
    Me.ServerFilter = "Discount = " & cboDiscounts
ElseIf IsNull(Me.cboLastNames) = False And _
    IsNull(Me.cboDiscounts) Then
    Me.ServerFilter = "LastName = '" & cboLastNames & "'"
ElseIf IsNull(Me.cboLastNames) = False And _
    IsNull(Me.cboDiscounts) = False Then
    str1 = "LastName = '" & cboLastNames & "'"
    str1 = str1 & " AND Discount = " & cboDiscounts
    Me.ServerFilter = str1
ElseIf IsNull(Me.cboLastNames) And _
    IsNull(Me.cboDiscounts) Then
    Me.ServerFilter = ""
End If
   
'Apply the filter
Me.Refresh
   
End Sub

The cmdRemoveFilter_Click event procedure removes all existing filters. It also clears the combo boxes to synchronize them with the filter setting and resets the RowSource property setting for the combo box showing discount percents. The new setting shows discounts offered by any employee.

Private Sub cmdRemoveFilter_Click()

'Clear filter and combo boxes
Me.ServerFilter = ""
Me.Refresh
   
'Clear both combo boxes
Me.cboLastNames = Null
Me.cboDiscounts = Null
   
'Restore initial row source for cboDiscounts
str1 = "SELECT DISTINCT Discount " & _
    "FROM vwEmployeesOrderDetails "
Me.cboDiscounts.RowSource = str1
   
End Sub

Team LiB
Previous Section Next Section