Linking Forms to a Single Record Source

Access has always let you bind forms to data simply and easily. This is one major reason why it is a rapid application development environment. In this section, we'll look at several ways to bind forms to data. I'll start with creating a simple bound form and then move on to dynamically assigning ADO recordsets and SQL statements as form sources. Next , I'll show you how to use an ADO recordset assignment to update form data. And finally, I'll cover using an ADO recordset to insert data via a form.

Creating a Simple Bound Form

To bind a form to data, you can use the AutoForm Wizard. Select a table or query in the database window and click the New Object: AutoForm button on the Database toolbar. The wizard opens a new form that binds directly to the selected data source. Figure 5-8 shows a sample form based on the Order Details table, which is a linked table in this chapter's sample database that points to the table of the same name in the Northwind database. You can use this form for browsing, editing, adding, and deleting records in the Order Details table. The form is available in the Chapter05.mdb file as frmOrderDetailsAutoForm .

Figure 5.8: A form based on a single record source (the Order Details table) created by the AutoForm Wizard.

Because the data source for the form's Product field is a lookup field in the underlying Order Details table, the Product field automatically appears as a combo box that displays product names instead of the underlying ProductID values. All of this functionality is provided automatically by the wizard.

If the table or query that you select as the record source for a form has a one-to-many relationship with another record source, Access 2003 automatically creates a main/subform. If your application calls for a sole main form bound to a table or query, you can open the form in Design view and remove the subform control. The section "Processing Main/Subforms" in this chapter takes a closer look at subform controls.

Dynamically Assigning Record Sources to Forms

The ease of applying the AutoForm Wizard and the flexibility of its forms are two of the reasons Access is such a popular database development package. Another reason for Access's popularity is that you can readily adapt forms created by the AutoForm Wizard to any custom data source created on the fly.

Access offers two complementary techniques for adapting a wizard-generated form for use with a dynamically assigned source. First, you can set the form's Recordset property. (Access 2000 was the first version to enable developers to assign the Recordset property of its forms.) Because this book emphasizes ActiveX Data Objects (ADO), I will explain how to use an ADO recordset to create a record source behind a form. However, you can also use a traditional Data Access Objects (DAO) recordset to do this. Second, you can also use a SQL statement, which is a traditional way to dynamically specify a form's record source.

Assigning an ADO Recordset as the Source

After creating a bound form with the AutoForm Wizard, you can use the form for dynamic record source assignment. Dynamically assigning record sources to forms greatly increases their flexibility. Taking advantage of this capability allows you to reduce the size of a database behind a form or take advantage of a record source that is already open, thus improving the form's load time.

There are two techniques for converting a form created by the AutoForm Wizard to one that is suitable for dynamic record source assignment. First, you can clear the RecordSource property setting from the form's property sheet. (When you view such a form without first dynamically assigning a record source to it, its text boxes initially open with the message "#Name?.) Second, you can leave the Record Source setting created by the AutoForm Wizard. This approach uses the AutoForm RecordSource property setting as a default record source. When your application programmatically assigns a record source, it overrides the default setting.

Each of the two approaches has its advantages. Leaving the form's settings undisturbed avoids a form opening with #Name? in its text boxes when no dynamic record source assignment exists. If your application does not logically have a default record source for a form, clearing the Record Source setting ensures that the form will never display data unless the application specifically assigns it to the form. The samples in this section use a wizard-generated form that has its Record Source setting cleared. The form is otherwise identical to the one shown in Figure 5-8. When you dynamically assign a record source to a form, the source lasts as long as the form is open. If a user opens the form without using the application, the form will not have a dynamic record source assignment, even if it was previously opened through the application.

By dynamically assigning a record source to a form, you can use the same form for different needs within a single application. For example, a single form can show sales for different employees , customers, date ranges, departments, and products. Your application simply needs to create a recordset with the proper SQL statement for the Open method of an ADO recordset. Then, just assign the ADO recordset to the form's Recordset property, and your application can display that data in the form. This assignment is only possible programmatically.

When you assign an ADO recordset with the default CursorLocation property setting, you create a read-only form. The form supports browsing, but users cannot add, update, or delete records through it. It doesn't matter if your application specifies a keyset cursor. Your application is still read-only with the default CursorLocation property setting. To enable read/write access, you must explicitly assign the adUseClient intrinsic constant to the CursorLocation property setting of a recordset for a form. The next sample we'll examine shows the syntax and behavior of a form based on the default CursorLocation property setting.

Note  

If you assign an ADO recordset based on a single table to a form in an Access project (as opposed to an Access database file), your form will be read/write by default. Chapter 12 extends this chapter's coverage of forms and explores topics that pertain particularly to the use of forms in Access projects (.adp files).

The following code sample demonstrates the syntax for dynamically assigning a recordset to a form. After assigning the form's Recordset property, the code browses the form and attempts to update a field value through the form. Because the form uses the default CursorLocation property, the attempt to revise a field value generates a run-time error that the procedure traps.

The procedure begins by instantiating a Connection object, cnn1 , and pointing it at the Northwind database. Next, the procedure instantiates a recordset, rst1 . The form's CursorType and LockType properties permit updating. (But the sample demonstrates that these settings are not sufficient to enable updating.) Then, the procedure uses cnn1 to open the rst1 recordset based on an inner join of the Orders and Order Details tables. The recordset returns the five fields from the Order Details tables for all records whose OrderDate field value is in 1998.

After creating the recordset for the form, the focus shifts to the form. The code uses the DoCmd object's OpenForm method to open a form named frmSimpleUnboundForm . This form has the same layout as the one in Figure 5-8. In addition, this form has no RecordSource property setting. The line after the OpenForm method dynamically assigns the rst1 recordset to the form's Recordset property.

After executing the line that makes the Recordset property assignment, you have a form with a recordset that is open in Access. If you place a breakpoint at the next line, you can click the View Microsoft Access control on the Standard toolbar in the Visual Basic Editor (VBE) window and see the open form.

The procedure goes on to use the form programmatically. First, it invokes the DoCmd object's GoToRecord method with an argument of acNext to move from the first to the second record. Then, the procedure returns to the initial record in the record source behind the form. This confirms the ability to browse using the form. Next, the procedure modifies the value in the Quantity text box control on the form and attempts to revise the database through the form's Refresh method. Because the form is read-only, invoking the Refresh method generates a run-time error that the program traps. After presenting a message box about the form's read-only status, the code exits the procedure by passing control to BindRst_Exit . Let's take a look at the syntax now.

 SubBindRstToSimpleUnboundFormRO()  OnErrorGoToBindRst_Trap Dimcnn1AsADODB.Connection Dimrst1AsRecordset Dimstr1AsString     'Createtheconnection Setcnn1=NewADODB.Connection cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_ "DataSource=C:\ProgramFiles\MicrosoftOffice\" &_  "Office11\Samples\Northwind.mdb;"     'Createrecordsetreferenceandsetitsproperties Setrst1=NewADODB.Recordset rst1.CursorType=adOpenKeyset rst1.LockType=adLockOptimistic     'Openrecordsetbasedonordersin1998for 'frmSimpleUnboundForm rst1.Open "SELECTod.OrderID,od.ProductID,od.UnitPrice, " &_  "od.Quantity,od.Discount " &_  "FROM[OrderDetails]ASod " &_  "INNERJOINOrdersASo " &_  "ONod.OrderID=o.OrderID " &_  "WHEREYear(o.OrderDate)=1998",cnn1     'AssignADOrecordsettoRecordsetpropertyof 'Forms("frmSimpleUnboundForm") str1= "frmSimpleUnboundForm" DoCmd.OpenFormstr1 SetApplication.Forms(str1).Recordset=rst1     'Browsetheformbymovingtothenextrecord 'andthenbackagain DoCmd.GoToRecord,,acNext DoCmd.GoToRecord,,acPrevious     'Attempttomodifyafieldintheform Forms(str1).Quantity=2000 Forms(str1).Refresh     BindRst_Exit: rst1.Close cnn1.Close Setrst1=Nothing Setcnn1=Nothing DoCmd.CloseacForm,str1 ExitSub     BindRst_Trap: IfErr.Number=-2147352567Then MsgBox "Theformisopeninread-onlymode. ",_ vbCritical, "ProgrammingMicrosoftAccess2003" ResumeBindRst_Exit Else Debug.PrintErr.Number,Err.Description MsgBox "Programabortedforunanticipatedreasons.",_ vbCritical, "ProgrammingMicrosoftAccess2003" EndIf     EndSub 

Assigning a SQL Statement as the Source

You can use VBA to assign a SQL string as a record source for a form through its RecordSource property. The form's RecordSource property corresponds to the Record Source setting in the form's property sheet. Therefore, you can set the RecordSource property programmatically or manually. When you set the property programmatically, it overrides any manual setting. When you make the assignment via VBA as described, the setting lasts only until you close the form.

Note  

VBA allows you to set a form's RecordSource property in Design view. When you do this, the assignment persists beyond the current form session. You cannot assign an ADO recordset to a form in Design view. Any setting to a form's Recordset property will always be transient.

Using a SQL string requires that you get the SQL syntax right for the database server that you are using. This approach does not allow you to take advantage of saved queries in other Access database files or of stored procedures in Microsoft SQL Server and other client/server databases. However, using the Recordset property to assign the record source for a form does. When you use a SQL string to designate a record source for a form, the database server must always compile the SQL string. When you set a form's Recordset property to an ADO recordset, you automatically gain access to the result set from a previously compiled SQL string ”namely, the SQL statement corresponding to the Source property for the recordset.

The following sample demonstrates the syntax for assigning a SQL string to a form. It uses the same SQL string from the preceding sample. After assigning a SQL string to the form's RecordSource property, the sample reopens the form in Form view and browses the record source through the form. In addition, it updates the record source via the form, and closes the form without saving the assignment of the SQL string to the form. The remainder of the sample confirms the modification of the record source using an ADO recordset. Then, it restores the updated field value to its original value.

This sample starts by immediately opening the form with the DoCmd object's OpenForm method. This method opens a form in Normal view unless you use an intrinsic constant to specify a different view. For example, to open a form in Design view, you use this syntax:

 DoCmd.OpenForm "frmSimpleUnboundForm",acDesign 

Using Design view enables an application to persist changes to the RecordSource property beyond the current form session.

The sample's next statement shows the syntax for assigning a SQL string to a form's RecordSource property. This approach requires just two lines of code to open a form with a dynamically assigned record source.

After assigning the record source using a SQL string, you can browse records and add, update, and delete them through the form. The following two lines of code demonstrate browsing by moving forward and backward one record. Next, the sample revises the value in the Quantity text box to 2000 for the form's first record and invokes the form's Refresh method. This does not generate an error because the form's record source is set to a SQL string. Before updating the value in the Quantity text box, the sample saves the original Quantity value as well as the values in the OrderID and ProductID text boxes. These values are necessary for restoring the first record to its original state.

Note  

When Access generates the source for a form from an assignment to the RecordSource property setting at design time or at run time, the corresponding recordset for the form is a DAO (as opposed to an ADO) recordset. This feature automatically enables the updating of record field values.

The remainder of the sample uses ADO to confirm that an updated Quantity value exists and restores the initial value. First, the sample instantiates a connection and points it at the Northwind database. Next, it instantiates rst1 , a recordset that is suitable for seeking the updated record. The recordset explicitly sets its Index property to the index named PrimaryKey for the Order Details table. This index has two fields: OrderID and ProductID . The code explicitly sets CursorLocation to adUseServer for the Seek method. In addition, the recordset's Open method specifies adCmdTableDirection for its Options argument. After taking these steps, the procedure invokes the Seek method for rst1 with the OrderID and ProductID values previously saved in the int2 and int3 variables .

Once the procedure finds the altered record, it prints the current record to the Immediate window. The fourth recordset field will be 2000. This is the record's Quantity field. Next, the procedure updates the altered Quantity field by assigning to it the saved original value ( int1 ). This restores the field to the value it had before the sample updated the field through the form. After restoring the value, the procedure again prints the record to the Immediate window. The fourth field now shows its initial value of 20.

 SubBindSQLToSimpleUnboundForm()  Dimint1AsInteger Dimint2AsLong Dimint3AsLong Dimcnn1AsADODB.Connection Dimrst1AsRecordset Dimstr1AsString     'AssignSQLtoRecordsourcepropertyof 'Forms("frmSimpleUnboundForm"). 'Formmustbeclosedbeforerunningthisroutine. DoCmd.OpenForm "frmSimpleUnboundForm" Forms("frmSimpleUnboundForm").RecordSource=_  "SELECTod.OrderID,od.ProductID, " &_  "od.UnitPrice,od.Quantity,od.Discount " &_  "FROM[OrderDetails]ASod " &_  "INNERJOINOrdersASo " &_  "ONod.OrderID=o.OrderID " &_  "WHEREYear(o.OrderDate)=1998" 'Browsetheformbymovingtothenextrecord 'andthenbackagain DoCmd.OpenForm "frmSimpleUnboundForm",acNormal DoCmd.GoToRecord,,acNext DoCmd.GoToRecord,,acPrevious     'SaveoriginalQuantityfieldvaluealongwithprimary 'keyvalues(OrderIDandProductID)beforeupdating 'theQuantityfieldthroughtheform. 'Closeformwhendone. int1=Forms("frmSimpleUnboundForm").Quantity int2=Forms("frmSimpleUnboundForm").OrderID int3=Forms("frmSimpleUnboundForm").ProductID Forms("frmSimpleUnboundForm").Quantity=2000 Forms("frmSimpleUnboundForm").Refresh DoCmd.CloseacForm, "frmSimpleUnboundForm"     'Createtheconnection Setcnn1=NewADODB.Connection cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_ "DataSource=C:\ProgramFiles\MicrosoftOffice\" &_  "Office11\Samples\Northwind.mdb;" 'Setcnn1=CurrentProject.Connection     'Createrecordsetreferenceandsetitsproperties Setrst1=NewADODB.Recordset Withrst1 .Index= "PrimaryKey" .CursorType=adOpenKeyset .LockType=adLockOptimistic .CursorLocation=adUseServer .Open "OrderDetails",cnn1,,,adCmdTableDirect .SeekArray(int2,int3) EndWith     'PrintrecordwithrevisedQuantityfield Debug.Printrst1(0),rst1(1),rst1(2),rst1(3),rst1(4)     'RestoreQuantityfieldvalueandprintagain rst1(3)=int1 rst1.Update Debug.Printrst1(0),rst1(1),rst1(2),rst1(3),rst1(4)     'Cleanupobjects rst1.Close cnn1.Close Setrst1=Nothing Setcnn1=Nothing     EndSub 

The Chapter05.mdb file contains a sample, BindRstToSimpleUnboundFormRO2RW , that demonstrates how to extract the Source property from an ADO recordset and assign it to a form's RecordSource property at run time. This approach allows you to convert an ADO recordset to a DAO recordset through Access and thus make the record source for the form read/write. Since the next two sections demonstrate more direct approaches to updating from an ADO recordset source for a form, the procedure's listing does not appear in the book.

Updating Form Data with an ADO Recordset Assignment

As demonstrated, you can routinely update form data when you use a SQL string to designate the form's record source. When you assign an ADO recordset to a form's Recordset property with the default CursorLocation setting (adUseServer), users cannot update the data with the form. For some applications, the easy availability of read-only forms is an advantage. You can make forms read-only when you assign their record source with a SQL string, but this requires additional property settings. (For example, see the form's AllowEdits , AllowDeletions , AllowAdditions , and DataEntry property descriptions in Access Help.)

If you want to use ADO to create forms that permit users to edit data, you simply need to set the source recordset's CursorLocation property to adUseClient . Making this simple adjustment before assigning a recordset to a form will allow users to update the data in a form based on a single table. The next sample demonstrates this feature.

While the sample for this section contains only one new line for assigning the CursorLocation property, the listing is complete because this sample incorporates a mixture of code from the two preceding samples. The sample begins by creating a connection to the Northwind database. Then, it instantiates a recordset that uses the connection. However, before opening the recordset for the SQL string (the same one used in the preceding two samples), the code sets the CursorLocation property to adUseClient . This simple step makes the form's control values updateable.

The remainder of the sample assigns the recordset to a form's Recordset property and updates the Quantity text box value for the first record to 2000. After closing the form, the sample prints the updated record to the Immediate window before restoring the Quantity field in the recordset to its original value. Finally, the code prints the record again after restoring the field's value.

 SubBindRstToSimpleUnboundFormRWUpdate()  Dimcnn1AsADODB.Connection Dimrst1AsRecordset Dimint1AsInteger Dimstr1AsString     'Createtheconnection Setcnn1=NewADODB.Connection cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_ "DataSource=C:\ProgramFiles\MicrosoftOffice\" &_  "Office11\Samples\Northwind.mdb;"     'Createrecordsetreferenceandsetitsproperties 'topermitread/writeaccesstorecordset Setrst1=NewADODB.Recordset rst1.CursorType=adOpenKeyset rst1.LockType=adLockOptimistic rst1.CursorLocation=adUseClient     'Openrecordsetbasedonordersin1998for 'frmSimpleUnboundForm rst1.Open "SELECTod.OrderID,od.ProductID,od.UnitPrice, " &_  "od.Quantity,od.Discount " &_  "FROM[OrderDetails]ASod " &_  "INNERJOINOrdersASo " &_  "ONod.OrderID=o.OrderID " &_  "WHEREYear(o.OrderDate)=1998",cnn1     'AssignADOrecordsettoRecordsetpropertyof 'Forms("frmSimpleUnboundForm") str1= "frmSimpleUnboundForm" DoCmd.OpenFormstr1 SetApplication.Forms(str1).Recordset=rst1     'ModifyQuantityfieldthroughtheform,but 'savetheoriginalvaluefirst int1=Forms(str1).Quantity Forms(str1).Quantity=2000 Forms(str1).Refresh DoCmd.CloseacForm,str1     'PrintrecordwithrevisedQuantityfield Debug.Printrst1(0),rst1(1),rst1(2),rst1(3),rst1(4)     'RestoreQuantityfieldvalueandprintagain rst1(3)=int1 rst1.Update Debug.Printrst1(0),rst1(1),rst1(2),rst1(3),rst1(4)     'Cleanupobjects rst1.Close cnn1.Close Setrst1=Nothing Setcnn1=Nothing     EndSub 

Inserting Data Using a Form Based on an ADO Recordset

The CursorLocation setting that you assign to a form's Recordset property can determine whether the form is read-only or updateable. The same setting also determines whether users can insert new records with a form. The process of setting up a form so that users can read from it and write to it is the same whether you update existing values or add new ones programmatically. However, the process of actually inserting or deleting a record is different than that of updating a record.

The next sample sets up a form for read/write access and demonstrates how to insert and delete a record through the form. Even though users might manually perform some of these steps, it's important to know how to add a record programmatically through a form.

After creating the rst1 recordset for the form, the sample opens the form the same way the preceding sample opened a form. However, the code next assigns the open form, frmSimpleUnboundForm , to the frm1 object reference.

The code uses this reference as a pointer to the open form. For example, the line after the form reference assignment sets the rst1 recordset to the Recordset property of frm1 . Then, the procedure saves the OrderID field value for the first record to the int1 variable.

The procedure launches the process of adding a new record by invoking the DoCmd object's GoToRecord command with an acNewRec argument. This displays a blank form and prepares Access to insert a new record. Inserting a new record is a two-step process. First, the code assigns values to the form fields. The sample adds a new line item for the order using the first OrderID in the rst1 recordset, which is the value in int1 . As the procedure adds a new record, it saves the ProductID for the new record. (The sample will use these saved values to remove the new records later.) Then, the sample invokes the form's Requery method to insert the form's control values into the recordset behind the form. After inserting the record, the procedure closes the form.

Note  

After invoking the GoToRecord method with the acNewRec argument, you do not have to use the Requery method to enter a new record. Closing the record without invoking the Requery method, or invoking the Refresh method instead of the Requery method, will also add a new record.

The remainder of the sample finds the newly added record and removes it from the recordset. The code uses the Seek method to search for all records matching the OrderID to which the form added a record. Then, the code loops through the line items for the OrderID until it discovers the one with a ProductID equal to the ProductID on the new line item. When the sample discovers the line item with a ProductID that matches the newly added record, it deletes the record and exits the loop.

 SubBindRstToSimpleUnboundFormRWInsert()  Dimcnn1AsADODB.Connection Dimrst1AsRecordset Dimfrm1AsForm Dimint1AsLong Dimint2AsLong Dimstr1AsString     'Createtheconnection Setcnn1=NewADODB.Connection cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_  "DataSource=C:\ProgramFiles\MicrosoftOffice\" &_  "Office11\Samples\Northwind.mdb;"     'Createrecordsetreferenceandsetitsproperties 'topermitread/writeaccesstorecordset Setrst1=NewADODB.Recordset rst1.CursorType=adOpenKeyset rst1.LockType=adLockOptimistic rst1.CursorLocation=adUseClient     'Openrecordsetbasedonordersin1998for 'frmSimpleUnboundForm rst1.Open "SELECTod.OrderID,od.ProductID,od.UnitPrice, " &_  "od.Quantity,od.Discount " &_  "FROM[OrderDetails]ASod " &_  "INNERJOINOrdersASo " &_  "ONod.OrderID=o.OrderID " &_  "WHEREYear(o.OrderDate)=1998",cnn1     'AssignADOrecordsettoRecordsetpropertyof 'Forms("frmSimpleUnboundForm") str1= "frmSimpleUnboundForm" DoCmd.OpenFormstr1 Setfrm1=Forms(str1) Setfrm1.Recordset=rst1     'AddanewrecordthroughtheformforthecurrentOrderID int1=frm1.OrderID DoCmd.GoToRecord,,acNewRec Withfrm1 .OrderID=int1 .ProductID=1 int2=.ProductID .Quantity=1 .UnitPrice=1 .Discount=0 .Requery EndWith DoCmd.CloseacForm,frm1.Name     'Createrecordsetreferenceandsetitspropertiesso 'youcanseektherecordsfortheOrderIDtowhichyou 'addedarecord Setrst1=NewADODB.Recordset Withrst1 .Index= "OrderID" .CursorType=adOpenKeyset .LockType=adLockOptimistic .Open "OrderDetails",cnn1,,,adCmdTableDirect .Seekint1 EndWith     'LoopthroughlineitemsforOrderIDtowhichyou 'previouslyaddedarecord;deletetherecord 'whenyoufindit DoUntilrst1(0)<>int1 Ifrst1(1)=int2Then 'Deletetherecordbecausewedonotwanttochange 'theNorthwinddatabase,butonlyshowthatwecan rst1.Delete ExitDo EndIf rst1.MoveNext Loop     'Cleanupobjects rst1.Close cnn1.Close Setrst1=Nothing Setcnn1=Nothing     EndSub