Using the Recordset Property

 < 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.

CASE STUDY: Who's Connected to the Database?

Let's assume TimeTrack.mdb is a shared database. That means the database administrator (or developer) needs to open the database exclusively to make changes and perform general maintenance. Before opening the database exclusively, the administrator must be able to determine who is currently working in the database and ask them to close their connection.

In a small company, you can simply call around, but that won't always be feasible. What happens if someone doesn't answer the phone, or if someone's out for lunch, having left the database open on his or her system? A simple solution is to use ADO to create a list of users currently connected to the database.

To simplify the whole process, you add a command button to the Switchboard form that runs the show. Clicking the new button then displays a simple form with a listbox that contains the names of all the currently connected users. For the sake of simplicity, that's all the form will do. However, when applying this technique to your own database, you can easily create a more functional form. Perhaps you might use a Click event to send email to the connected users. Or, you might list extension numbers so you can quickly call each user.

This section introduces you to schema recordsets. Normally, a Recordset object contains data from a table. A schema Recordset contains information about the table or database.

The first step is to add that command button, so open Switchboard and add a command button. Name the new button cmdCurrentUsers and add an appropriate caption. Enter the following event procedure in the Switchboard form's module:


 Private Sub cmdCurrentUsers_Click()   On Error GoTo HandleErr   DoCmd.OpenForm "CurrentUsers" ExitHere:   Exit Sub HandleErr:   MsgBox "Error " & Err.Number & ": " & _    Err.Description   Resume ExitHere End Sub 

Don't worry that the CurrentUsers form doesn't exist yet; you build that next. For now, save and close the Switchboard form.

Open a new blank form and add a listbox. Name the listbox lstCurrentUsers and set the Row Source Type property to Value List. Set the form properties listed in Table 16.7. Then, enter the following procedure in the form's module:


 Private Sub Form_Load()   'populate list box with current users   Dim strUsers As String   On Error GoTo HandleErr   strUsers = ReturnUsers   lstCurrentUsers.RowSource = strUsers ExitHere:   Exit Sub HandleErr:   MsgBox "Error " & Err.Number & ": " & _    Err.Description   Resume ExitHere End Sub 

Table 16.7. CurrentUsers Properties



Scroll Bars


Record Selectors


Navigation Buttons


Save the form using the name CurrentUsers, and then close the form.

Next, open a new standard module (or use Chapter 16's example module) and enter the following code:


 Public Const JET_SCHEMA_USERROSTER = _  "{947bb102-5d43-11d1-bdbf-00c04fb92675}" Public Function ReturnUsers() As String   Dim cnn As ADODB.Connection   Dim rst As ADODB.Recordset   Set cnn = New ADODB.Connection   On Error GoTo HandleErr   'Open connection to database   Set cnn = CurrentProject.Connection   'Open schema recordset to grab user metadata   Set rst = cnn.OpenSchema(adSchemaProviderSpecific, , _    JET_SCHEMA_USERROSTER)   'return current users   rst.MoveFirst   Do Until rst.EOF     ReturnUsers = rst(0) & ";" & ReturnUsers     rst.MoveNext   Loop ExitHere:   rst.Close   Set rst = Nothing   cnn.Close   Set cnn = Nothing   Exit Function HandleErr:   MsgBox "Error " & Err.Number & ": " & _    Err.Description   Resume ExitHere End Function 

Be sure that you position the Public Const statement in the General Declarations section. Save the module and close it.

To see how it works, open the Switchboard form and click the Current Users button to open the new form shown in Figure 16.5. As you can see, the listbox displays the current user, SUSANONE. Actually, SUSANONE is the name of the computer that's currently connected, not the actual user.

Figure 16.5. This form lists the users currently connected to the database.


You don't really need the switchboard to use this form, but maintenance tasks can just as easily be initiated from the switchboard as any other. Clicking the Current Users button opens the new CurrentUsers form. That form's Open event calls the ReturnUsers procedure.

The OpenSchema method tells ADO what kind of information to retrieve. In this case, you used the JET_SCHEMA_USERROSTER constant. That long string in the Public Const statement is a GUID that identifies the current users schema.

After the ReturnUsers procedure returns the list of current users as a single string, the listbox uses that string as its Row Source property.

     < Day Day Up > 

    Automating Microsoft Access with VBA
    Automating Microsoft Access with VBA
    ISBN: 0789732440
    EAN: 2147483647
    Year: 2003
    Pages: 186 © 2008-2017.
    If you may any questions please contact us: