How to enumerate selected form records in Access 2003 and in Access 2002

Article translations Article translations
Article ID: 294202 - View products that this article applies to.
This article was previously published under Q294202
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

For a Access 97 version of this article, see 148393.
For a Access 2000 version of this article, see 208502.
Expand all | Collapse all

On This Page

SUMMARY

You can select multiple records in a form by using the record selector at the left side of the form. You can also use the SelTop and SelHeight form properties to specify or determine the number of selected records in a continuous or Datasheet view form.

This article shows you how to use the SelTop and SelHeight form properties to enumerate through the list of selected records in a form.

NOTE: SelLeft and SelWidth are form properties that you can use to determine the current columns selected in Datasheet view; however, this article does not discuss these form properties.

MORE INFORMATION

The SelTop and SelHeight properties enable you to determine which records are selected in a continuous or Datasheet view form. You can use the SelTop property to determine which row is the first in the selection. You can use the SelHeight property to determine the number of rows in the current selection.

The following information describes how to use these properties to enumerate the list of selected records from one of the following:
  • 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.
NOTE: The second method is a little more difficult. Suppose that you want to place a command button in the header or the footer section of a continuous form to enable your users to copy, move, or print a report against the selected set of records. When you press this button, the current selection of records disappears when the command button receives focus. Information later in this article shows you how to work around this behavior.

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

The advantage to using a toolbar button or keyboard key to run your code is that the current selection of records will not be lost; this simplifies the procedure:
  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 then 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 then close the module.
  5. Create a new macro. To do so, click Macro Names on the View menu to display the Macro Name column. Use the following action, and then save the macro as Autokeys.
      Macro Name     Action
      ------------------------------------
      {F5}           RunCode
    
      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 then save the form with the default name Customers1.
  9. Right-click the toolbar, and then click Customize.
  10. In the Customize dialog box, click the Commands tab, and then 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 F5 or click the new toolbar button.

    Note that a message box appears for each selected record. The message box displays the contents of the Company Name field of the selected record.

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 earlier in this article to create the module with the DisplaySelectedCompanyNames() procedure.
  2. Open the module in Design view, and then type or paste the following lines in 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 then 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 then 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 is not already displayed), and then change the Height property to .5 inches.
  9. Add a command button to the form footer section, and then set the following properties:
    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 then 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. The message box displays the contents of the Company Name field of the selected record.
NOTE: If you want to use the command button to enumerate the list of selected records in a subform, pass the subform as the argument to the SelRecord() function from the Mouse events of the command button. For example, suppose your subform is called Orders Subform. You would set the command button mouse-move properties as follows:
   OnMouseDown: =SelRecord([Orders Subform].[Form],"Down")
   OnMouseMove: =SelRecord([Orders Subform].[Form],"Move")
   OnMouseUp:   =SelRecord([Orders Subform].[Form],"Up")
				

REFERENCES

For more information about selected records in forms, click Microsoft Access Help on the Help menu, type select fields and records in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 294202 - Last Review: April 11, 2008 - Revision: 6.1
APPLIES TO
  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
Keywords: 
kbexpertiseinter kbhowto KB294202

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com