|< Day Day Up >
Using the Recordset Property
Several Access objects the listbox and combo box controls and the form and report object support a Recordset property. The result is a control, form, or report that's bound to a Recordset, just as you might bind that same object to a table or query.
The process is simple create the Recordset as you normally do. Then, use the following syntax to set the object's Recordset property as follows:
Set object.Recordset = recordset
A few chapters back, you worked through an exercise that added a combo box to the Employees form that filtered the form to a specific employee. If you recall, that form is bound to the Employees table and as such, displays the data for all the employees. The combo box you added temporarily filters the form to the selected employee.
There's always more than one way to accomplish something in Access. You can use this form's Recordset property to limit the form's records in the same say. The difference is that the form, within this context, is bound to a Recordset and not the underlying table. You can still update the data, if you specify the appropriate Recordset type. In addition, the form displays only the record for the specified employee at no time does this form's recordset contain more than one record.
Open a standard module (or use Chapter 16's example module) and add the following procedure:
Sub FilterEmployeeForm(val As Integer) Dim rstEmployees As ADODB.Recordset DoCmd.OpenForm "Employees" Set rstEmployees = New ADODB.Recordset With rstEmployees .CursorLocation = adUseClient .Open "SELECT * " _ & "FROM Employees " _ & "WHERE EmployeeID = " & val, _ CurrentProject.Connection, adOpenStatic, adLockOptimistic End With Set Forms("Employees").Recordset = rstEmployees End Sub
Then, in the Immediate window, enter the following statement:
Next, access the newly opened Employees form, which then displays the employee record for Clint Cooper. Notice that the navigation bar displays the value 1 to let you know the form's recordset contains only one record.
Try selecting another employee from the combo box in the header section. Because the Recordset doesn't contain the corresponding record, Access returns an error, as shown in Figure 16.4. Click End to clear the message. If you were to use this form in this fashion, you have to inhibit the combo box control's search capabilities when opened in this manner.
Figure 16.4. The combo box doesn't work when opened from the Recordset property procedure.
This example doesn't close or destroy the Recordset object because, as is, you're still using it. In a working example, you need to both close and destroy the Recordset object when you're done with it most likely when you close the form.
This example works because the form was originally bound to the underlying data source and you didn't change the control names. That way, the control names still match the Recordset object's field names. If you change the control names, this example won't work until you add code that matches each control to the appropriate Recordset field.
|< Day Day Up >