ACC: How To Enumerate Selected Form Records

Last reviewed: July 18, 1997
Article ID: Q148393
The information in this article applies to:
  • Microsoft Access versions 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

In Microsoft Access, you can select multiple records in a form using the record selector at the left side of the form. In Microsoft Access 7.0 and 97, you can use the new SelTop and SelHeight form properties to specify or determine the number of selected records in a continuous or Datasheet view form.

The following information describes how you can use these new properties to enumerate through the list of selected records in a form.

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

MORE INFORMATION

Microsoft Access 7.0 and 97 provide the SelTop and SelHeight properties to 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

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

The latter case presents some challenges and is a bit more difficult to accomplish. Suppose you want to place a command button in the header or footer section of a continuous form to enable your users to copy, move, or print a report against the selected set of records. When your user presses this button, the current selection of records disappears when the command button receives focus. Information later in this article demonstrates how to circumvent this behavior.

Enumerating Selected Records Using a Toolbar or AutoKeys Macro

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, simplifying the procedure:

  1. Open the sample database Northwind.mdb and create a new module with the following procedure:

           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
    
    

  2. Save the module with a unique name and close the module.

  3. Create a new macro with the name AutoKeys as follows:

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

  4. On the Insert menu, click Form to open the New Form dialog box.

  5. Select AutoForm: Tabular from the list of available wizards, and Customers from the "Choose the table or query where the object's data comes from" box. Click OK.

  6. On the File menu, click Save and save the form with the default name, Customers1.

  7. Using the right mouse button, click the toolbar and click Customize.

  8. Do one of the following for your version of Microsoft Access:

    In Microsoft Access 97:

          In the Customize dialog box, click the Commands tab and select
          "All Macros." Under Commands, drag "AutoKeys.{F5}" from the Commands
          list to an empty space on the toolbar, and then click Close.
    

    In Microsoft Access 7.0:

          In the Customize Toolbars dialog box, under Categories, select "All
          Macros." Under Objects, drag "AutoKeys.{F5}" from the Objects list to
          an empty space on the toolbar, and then click Close.
    

  9. Select a record or a set of records from the form 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 CompanyName field.

Enumerating Selected Records Using a Command Button

  1. Follow steps 1 and 2 in the "How to Enumerate Selected Records Using a Toolbar or AutoKeys Macro" section above 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. Create 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. Follow steps 4 - 6 in the "How to Enumerate Selected Records Using a Toolbar or AutoKeys Macro" section above to create the Customers1 form.

  6. Open the Customers1 form in Design view. Double-click the Form Footer section bar to bring up the Properties window (if it isn't already up), and change the Height property to .5 inches.

  7. Add a command button to the Form Footer section with 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")
    
    

  8. Add the following code to the new cmdSelectedCompanyNames command button Click event:

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

  9. View the form and select a record or a set of records 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.

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 command button's Mouse events. For example, suppose your subform is called Orders Subform; the command button mouse move properties would be set to the following:

   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, search the Help Index for the term "Selecting," and then view "Selecting fields and records."


Additional query words: multi-selection multiple
Keywords : FmsHowto kbusage
Version : 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: July 18, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.