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.

 'Stepthroughthisproceduretoseetheflowandunderstand 'itslogic SubReadWriteOrReadOnly()  OnErrorGoToReadWrite_Trap     Dimfrm1AsForm Dimint1AsInteger ConstconReadOnly=3 ConstconReadWrite=4 #ConstReadOnly=True     'OpenfrmOrderDetails,setreferencetoit,and 'assignReadOnlysettingtoRecordsetTypeproperty DoCmd.OpenForm "frmOrderDetails" Setfrm1=Forms("frmOrderDetails") #IfReadOnly=TrueThen frm1.RecordsetType=conReadOnly #Else frm1.RecordsetType=conReadWrite #EndIf     'MultiplythevalueofQuantityforthefirstrecordby100, 'andrefreshvalueonserver int1=frm1.Quantity frm1.Quantity=100*int1 frm1.Refresh     'Closeformandexitsub ReadWrite_Exit: DoCmd.CloseacForm,frm1.Name ExitSub     ReadWrite_Trap: IfErr.Number=-2147352567Andfrm1.RecordsetType=conReadOnlyThen 'IfdataisnotupdatableandRecordsetTypeisReadOnly, 'offeroptiontochangesetting IfMsgBox("Areyousureyouwanttoeditquantity?",_ vbYesNo,_  "ProgrammingMicrosoftAccess2003")=vbYesThen frm1.RecordsetType=conReadWrite Resume Else ResumeReadWrite_Exit EndIf ElseIfErr.Number=6Then 'Ifitisanoverflowerror,youmightbeabletorecover 'byrestoringQuantitytoitsinitialvalue MsgBox "Quantitymayexceeditslimit; " &_  "itscurrentvalueis " &frm1.Quantity& ". " &_  "ConsiderrunningRestoreFirstQuantityTo12",_ vbInformation,_  "ProgrammingMicrosoftAccess2003" Else Debug.PrintErr.Number;Err.Description EndIf EndSub 

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.

 SubRestoreFirstQuantityTo12()  OnErrorGoToRestoreFirstQuantity_Trap Dimfrm1AsForm ConstconReadOnly=3 ConstconReadWrite=4     'OpenfrmOrderDetails,setreferencetoit,and 'assignReadOnlysettingtoRecordsetTypeproperty DoCmd.OpenForm "frmOrderDetails" Setfrm1=Forms("frmOrderDetails")     'RestoreQuantitywithsavedvalue frm1.Quantity=12 frm1.Refresh     'Closeform DoCmd.CloseacForm,frm1.Name     RestoreFirstQuantity_Exit: ExitSub     RestoreFirstQuantity_Trap: IfErr.Number=-2147352567Andfrm1.RecordsetType=conReadOnlyThen 'IfdataisnotupdatableandRecordsetTypeisReadOnly, 'offeroptiontochangesetting IfMsgBox("Areyousureyouwanttorestorequantityto12?",_ vbYesNo,_  "ProgrammingMicrosoftAccess2003")=vbYesThen frm1.RecordsetType=conReadWrite Resume EndIf Else Debug.PrintErr.Number,Err.Description EndIf     EndSub 

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.

 SubCreateLinksTable() Dimstr1AsString Dimcnn1AsADODB.Connection DimTableNameAsString     'Pointaconnectionobjectatthecurrentproject Setcnn1=CurrentProject.Connection     'Deletethetableifitexistsalready TableName= "Links" Drop_a_tablecnn1,TableName     'Createthetable str1= "CREATETABLE " &TableName& " " &_  "(" &_  "LinkIDintIDENTITY(1,1)NOTNULLPRIMARYKEYCLUSTERED, " &_  "LinkNamevarchar(96)NULL, " &_  "LinkURLvarchar(128)NOTNULL, " &_  "LinkTypevarchar(6)NULL " &_  ")" cnn1.Executestr1     str1= "INSERTINTO " &TableName& " " &_  "VALUES('LinktoWebpage', " &_  "'ProgrammingMSAccess.com#" &_  "http://www.programmingmsaccess.com', " &_  "'page') " &_  "INSERTINTO " &TableName& " " &_  "VALUES('Startemailforpraise', " &_  "'Linkforemail#mailto:rickd@cabinc.net" &_  "?subject=Yourbookisgreat!#', " &_  "'email') " &_  "INSERTINTO " &TableName& " " &_  "VALUES('Linkfortablesincurrentproject', " &_  "'Links##TableLinks', " &_  "'object') " &_  "INSERTINTO " &TableName& " " &_  "VALUES('Linktoatableinadatabasefile', " &_  "'LinktoAccessdatabasefiletable#" &_  "../../ProgramFiles/MicrosoftOffice/" &_  "Office11/Samples/Northwind.mdb#TableEmployees', " &_  "'object') " &_  "INSERTINTO " &TableName& " " &_  "VALUES('LinkforbookmarkonaWebpage', " &_  "'Bookmarklink#http://www.programmingmsaccess.com/thebook/" &_  "#A2002','page') " cnn1.Executestr1     'RefreshDatabasewindowtoshownewtable RefreshDatabaseWindow     EndSub     SubDrop_a_table(cnn1AsADODB.Connection,TableNameAsString)     'Deletethetableifitexistsalready str1= "IFEXISTS(SELECTTABLE_NAME " &_  "FROMINFORMATION_SCHEMA.TABLES " &_  "WHERETABLE_NAME='" &TableName& "') " &_  "DROPTABLE " &TableName cnn1.Executestr1     EndSub 

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.

 PrivateSubForm_Open(CancelAsInteger) Dimstr1AsString     'Assignform'scaption Me.Caption= "ServerFilterSample"     'Clearanyfilterfromapriorsession Me.ServerFilter= "" Me.Refresh     'DeveloprowsourcestringforcboLastNames 'andassignit str1= "SELECTLastName " &_  "FROMEmployees " Me.cboLastNames.RowSource=str1     'DeveloprowsourcestringforcboDiscounts 'independentofcboLastNamesandassignit str1= "SELECTDISTINCTDiscount " &_  "FROMvwEmployeesOrderDetails " Me.cboDiscounts.RowSource=str1     'Restrictcomboboxestolistandclearthem Me.cboDiscounts.LimitToList=True Me.cboLastNames.LimitToList=True Me.cboDiscounts=Null Me.cboLastNames=Null     EndSub 

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.

 PrivateSubcboLastNames_AfterUpdate() Dimstr1     'DeveloprowsourcestringforcboDiscounts 'andassignit str1= "SELECTDISTINCTDiscount " &_  "FROMvwEmployeesOrderDetails " &_  "WHERELastName='" &cboLastNames& "'" Me.cboDiscounts.RowSource=str1     'ClearpriorsettingfromcboDiscounts Me.cboDiscounts= ""     EndSub 

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.

 PrivateSubcmdFilter_Click() Dimstr1AsString     'Setfilterbasedoncomboboxsettings IfIsNull(Me.cboLastNames)And_ IsNull(Me.cboDiscounts)=FalseThen Me.ServerFilter= "Discount= " &cboDiscounts ElseIfIsNull(Me.cboLastNames)=FalseAnd_ IsNull(Me.cboDiscounts)Then Me.ServerFilter= "LastName='" &cboLastNames& "'" ElseIfIsNull(Me.cboLastNames)=FalseAnd_ IsNull(Me.cboDiscounts)=FalseThen str1= "LastName='" &cboLastNames& "'" str1=str1& " ANDDiscount= " &cboDiscounts Me.ServerFilter=str1 ElseIfIsNull(Me.cboLastNames)And_ IsNull(Me.cboDiscounts)Then Me.ServerFilter= "" EndIf     'Applythefilter Me.Refresh     EndSub 

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.

 PrivateSubcmdRemoveFilter_Click() 'Clearfilterandcomboboxes Me.ServerFilter= "" Me.Refresh     'Clearbothcomboboxes Me.cboLastNames=Null Me.cboDiscounts=Null     'RestoreinitialrowsourceforcboDiscounts str1= "SELECTDISTINCTDiscount " &_  "FROMvwEmployeesOrderDetails " Me.cboDiscounts.RowSource=str1     EndSub 



Programming Microsoft Office Access 2003
Programming MicrosoftВ® Office Access 2003 (Core Reference) (Pro-Developer)
ISBN: 0735619425
EAN: 2147483647
Year: 2006
Pages: 144
Authors: Rick Dobson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net