How to enumerate selected form records in Access 2000

• A macro run from a toolbar button or an AutoKeys macro.
• Code run from a command button in the header or footer section of the form.

Using a Toolbar Button or an AutoKeys Macro to Enumerate Selected Records

1. CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.
   
   
2. Open the sample database Northwind.mdb and create a new module.
3. Type or paste the following procedure in the new module:

   Function DisplaySelectedCompanyNames()
      Dim i As Long
      Dim F As Form
      Dim RS As Recordset
   
      ' Get the form and its recordset.
      Set F = Forms![Customers1]
      Set RS = F.RecordsetClone
   
      ' Move to the first record in the recordset.
      RS.MoveFirst
   
      ' Move to the first selected record.
      RS.Move F.SelTop - 1
   
      ' Enumerate the list of selected records presenting
      ' the CompanyName field in a message box.
      For i = 1 To F.SelHeight
        MsgBox RS![CompanyName]
        RS.MoveNext
      Next i
   
   End Function
   
   					

4. Save the module with a unique name and close the module.
5. Create a new macro named AutoKeys as follows:

     Macro Name     Action
     ----------------------
     {F5}           RunCode
   
     AutoKeys Actions
     ------------------------------------------------
     RunCode
        Function Name: =DisplaySelectedCompanyNames()
   					

6. On the Insert menu, click Form to open the New Form dialog box.
7. Click AutoForm: Tabular in the list of available wizards, select the Customers table in the Choose the table or query where the object's data comes from box, and then click OK.
8. On the File menu, click Save and save the form with the default name, Customers1.
9. Right-click the toolbar and click Customize.
10. In the Customize dialog box, click the Commands tab and under Categories, click All Macros. Under Commands, drag "AutoKeys.{F5}" from the Commands list to an empty space on the toolbar, and then click Close.
11. Select a record or a set of records on the form by using the record selector buttons on the left side of the continuous form. Press the F5 key or click the new toolbar button.
    
    Note that a message box appears for each selected record displaying the contents of the selected record's Company Name field.

Using a Command Button to Enumerate Selected Records

1. Follow steps 1 through 3 in the "Using a Toolbar Button or an AutoKeys Macro to Enumerate Selected Records" section to create the module with the DisplaySelectedCompanyNames() procedure.
2. Open the module in Design view and add the following to the Declaration section:

   Dim MySelTop As Long
   Dim MySelHeight As Long
   
   Dim MySelForm As Form
   Dim fMouseDown As Integer
   					

3. Type or paste the following two procedures:

   Function SelRecord(F As Form, MouseEvent As String)
      Select Case MouseEvent
         Case "Move"
            ' Store the form and the form's Sel property settings
            ' in the MySel variables ONLY if mouse down has not
            ' occurred.
            If fMouseDown = True Then Exit Function
            Set MySelForm = F
            MySelTop = F.SelTop
            MySelHeight = F.SelHeight
   
         Case "Down"
            ' Set flag indicating the mouse button has been pushed.
            fMouseDown = True
         Case "Up"
            ' Reset the flag for the next time around.
            fMouseDown = False
      End Select
   End Function
   
   Public Sub SelRestore()
   Debug.Print "got into Restore"
      ' Restore the form's Sel property settings with the values
      ' stored in the MySel variables.
      MySelForm.SelTop = MySelTop
      MySelForm.SelHeight = MySelHeight
   
   End Sub
   
   					

4. Save and close the module.
5. On the Insert menu, click Form to open the New Form dialog box.
6. Click AutoForm: Tabular in the list of available wizards, select the Customers table in the Choose the table or query where the object's data comes from box, and then click OK.
7. On the File menu, click Save and save the form with the default name, Customers1.
8. Open the Customers1 form in Design view. Double-click the form footer section bar to open the properties sheet (if it isn't already open), and change the Height property to .5 inches.
9. Add a command button to the form footer section and set the following properties as indicated:
   Name: cmdSelectedCompanyNames
   Caption: Display Selected Company Names
   Width: 2"
   OnClick: [Event Procedure]
   OnMouseDown: =SelRecord([Form],"Down")
   OnMouseMove: =SelRecord([Form],"Move")
   OnMouseUp: =SelRecord([Form],"Up")
10. Add the following code to the Click event of the new cmdSelectedCompanyNames command button:

    Private Sub cmdSelectedCompanyNames_Click()
       Dim X
       ' Restore the lost selection.
       SelRestore
    
       ' Enumerate the list of selected company names.
       X = DisplaySelectedCompanyNames()
    End Sub
    
    					

11. View the form in Form view and select a record or a set of records by using the record selector buttons on the left side of the continuous form. Click the new "Display Selected Company Names" command button.
    
    Note that a message box appears for each selected record displaying the contents of the selected record's Company Name field.

   OnMouseDown: =SelRecord([Orders Subform].[Form],"Down")
   OnMouseMove: =SelRecord([Orders Subform].[Form],"Move")
   OnMouseUp:   =SelRecord([Orders Subform].[Form],"Up")