Microsoft KB Archive/120275

= ACC2: How to Simulate Multiple Record Selections in a Form =

Article ID: 120275

Article Last Modified on 11/6/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q120275



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.

 Open the sample database NWIND.MDB.  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                        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. Open the Customers form in Design view and set the form's RecordSource property to the Customer Query query.  Create a toggle button with the following properties on the form:

     ControlSource: Selected (the name of the Yes/No field) DefaultValue: False   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 </li> 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.</li></ol>

To determine which records are selected, you can create a simple select query to find all the records in the Customers Selected table with the Selected field set to Yes.

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:

<pre class="fixed_text">  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

NOTE: When you use the Delete Selected button, customers who currently have orders in the NWIND database will not be deleted, due to referential integrity. You can modify the relationship between the Customers and the Orders tables to include cascading deletes, so that the removal of a customer will cause the removal of all of that customer's orders. To set up cascading deletes for the Customers and the Orders tables, follow these steps:


 * 1) Select the Database window and then choose Relationships from the Edit menu.
 * 2) Double-click the join line between the Customers and the Orders tables.
 * 3) Select the Cascade Delete Related Records check box.
 * 4) Choose OK.

Keywords: kbhowto kbusage KB120275

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.