ACC2: How to Simulate Multiple Record Selections in a Form

• Microsoft Access version 2.0

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

In Microsoft Access, you can select multiple records in a form by using the record selector at the left side of the form. However, there is no built-in method to determine from a macro or Access Basic code which records have been selected.

This article describes how to simulate the record selector using a Yes/No field, so that you can make multiple, noncontiguous selections, which you cannot do using the standard record selector. This article also describes how to create buttons to select all the records, unselect the records, and delete the selected records. This technique will work in either a single- user or a multiuser environment.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

This method of simulating the record selector uses a Yes/No field in the form's underlying table. You can place the Yes/No field on the form bound to either a toggle button (to look like the built-in record selector) or a check box or option button.

You can determine which records are selected using a select query to find all the records where the Yes/No field is set to Yes, or by checking the form's Recordset to see which records have the Yes/No field set to Yes.

This method is simple and works well in a single-user environment where the table will not be shared. In a multiuser environment, the Yes/No field must be placed in a separate selection table unique to each user. The selection table should include the key fields from the original table in addition to the Yes/No field. The form can then be based on a query that joins the original table with the selection table on the key fields. There must be one record in the selection table for each record in the original table in order to have a one-to-many relationship.

The selection table must be unique for each user on the system, and cannot by shared with other users on the system. In multiuser environments, you should consider dividing the database into two databases, with one database (the application database) containing all the forms, reports, macros, and modules, and the other database (the data database) containing all the tables. You can then attach the tables in the data database to the application database. In such a two-database system, you can put the selection table in the local application database to be used only by the individual user.

How to Create a Multiple Selection, Multiuser Customers Form

The following example demonstrates how to create a toggle selection button on the Customers form in the sample database NWIND.MDB, using a separate selection table to maintain a list of selected records for use in a multiuser environment.

Note that to run best in a multiuser environment, the Customers table should be stored in a different, shared database attached to the NWIND database. For this example, assume that the table is an attached, shared, Microsoft Access table.

CAUTION: Following the steps in this example will modify the sample database NWIND.MDB. You may want to back up the NWIND.MDB file, or perform these steps on a copy of the NWIND database.

1. Open the sample database NWIND.MDB.

2. Create the following new table and then save it as Customers Selected:

         Table: Customers Selected
         -------------------------
         Field Name: Customer ID
            Data Type: Text
            Field Size: 5
         Field Name: Selected
            Data Type: Yes/No
   
         Indexes: Customers Selected
         ---------------------------
         Index Name: PrimaryKey
            Field Name: Customer ID
   
   

3. Create a new select query based on the Customers and Customers Selected tables. Join the tables on the Customer ID field and include all the fields from the Customers table and the Selected field from the Customers Selected table in the query grid. Save the query as Customer Query.

4. Open the Customers form in Design view and set the form's RecordSource property to the Customer Query query.

5. Create a toggle button with the following properties on the form:

         ControlSource: Selected (the name of the Yes/No field)
         DefaultValue: False
   

6. Set the form's OnOpen property to the following event procedure.

   NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

         Sub Form_Open (Cancel As Integer)
            Dim DB As Database
            Set DB = CurrentDB()
   
            ' Clean out the Customers Selected table.
            DB.Execute "DELETE * FROM [Customers Selected]"
   
            ' Create a matching record in the Customers Selected table
            ' for each record in the Customers table.
            DB.Execute "INSERT INTO [Customers Selected] ([Customer ID]) _
               SELECT [Customer ID] FROM [Customers]"
   
            ' Requery the form to make sure it sees the new Customers
            ' Selected records.
            Me.Requery
   
         End Sub
   
   

7. View the Customers form in Form view. When you open the form, the event procedure specified in the OnOpen property setting will run, ensuring that the Customers Selected table contains a record with the Selected field set to No for each record in the Customers table. You can move among the records, using the toggle button to mark records for selection. If you create a new record, the DefaultValue property setting for the toggle button will cause a new record to be created in the Customers Selected table.

How to Create Buttons to Select All the Records, Clear the Selection, and Delete the Selected Records

You can create buttons on the form so that the form's users can easily select all the records in the form or clear the current selection. You can also create a button to delete all the selected records. To create these buttons, add three command buttons with the following properties to the Customers form:

   Command Button 1
   ------------------------------------------------------------------
   Name: btnSelectAll
   Caption: Select All
   OnClick: [Event Procedure]

      Sub btnSelectAll_Click ()
         Dim DB As Database
         Set DB = CurrentDB()

      On Error Goto Err_btnSelectAll_Click

         ' Update the Selected field for all customers to Yes (True).
         DB.Execute "UPDATE [Customers Query] SET [Selected] = True;"

         ' Refresh the form to display the changes.
         DoCmd DoMenuItem A_FORMBAR, A_RECORDSMENU, A_REFRESH

      Bye_btnSelectAll_Click:
         Exit Sub
      Err_btnSelectAll_Click:
         Beep: MsgBox Error$, 48
         Resume Bye_btnSelectAll_Click:
      End Sub

   Command Button 2
   -------------------------------------------------------------------
   Name: btnUnSelectAll
   Caption: Unselect All
   OnClick: [Event Procedure]

      Sub btnUnSelectAll_Click ()
         Dim DB As Database
         Set DB = CurrentDB()

      On Error Goto Err_btnUnSelectAll_Click

         ' Update the Selected field for all customers to No (False).
         DB.Execute "UPDATE [Customers Query] SET [Selected] = False;"

         ' Refresh the form to display the changes.
         DoCmd DoMenuItem A_FORMBAR, A_RECORDSMENU, A_REFRESH

      Bye_btnUnSelectAll_Click:
         Exit Sub
      Err_btnUnSelectAll_Click:
         Beep: MsgBox Error$, 48
         Resume Bye_btnUnSelectAll_Click:
      End Sub

   Command Button 3
   -----------------------------------------------------------------------
   Name: btnDeleteSelected
   Caption: Delete Selected
   OnClick: [Event Procedure]

   NOTE: In the following sample code, an underscore (_) at the end of
   a line is used as a line-continuation character. Remove the under-
   score from the end of the line when re-creating this code in Access
   Basic.

      Sub btnDeleteSelected_Click ()
         Dim DB As Database
         Set DB = CurrentDB()

      On Error Goto Err_btnDeleteSelected_Click

         ' If the current record has been edited and is selected, save it.
         If Me.Dirty And Me![Selected] Then
            DoCmd DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD
         End If

         ' Delete all customers where the Selected field is marked.
         DB.Execute "DELETE Customers.*, [Customers Selected].Selected _
            FROM Customers INNER JOIN [Customers Selected] ON _
            Customers.[Customer ID] = [Customers Selected].[Customer ID] _
            WHERE [Customers Selected].Selected=True"

         ' Refresh the form to display the changes.
         DoCmd DoMenuItem A_FORMBAR, A_RECORDSMENU, A_REFRESH

      Bye_btnDeleteSelected_Click:
         Exit Sub
      Err_btnDeleteSelected_Click:
         Beep: MsgBox Error$, 48
         Resume Bye_btnDeleteSelected_Click:
      End Sub