Linking Forms to a Single Record Source

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.

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.

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.

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.

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