PRA: Quick Sort Incorrect If Code Changes RecordSource Setting

Article ID: Q141608
Creation Date: 20-DEC-1995
Revision Date: 20-SEP-1996

The information in this article applies to:

Microsoft Access version 2.0

SYMPTOMS

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

When you use the Quick Sort toolbar button to sort a form's records, and then change the form's record source to another table using Access Basic, records from the original record source will appear if you attempt to sort the new record source's records with the Quick Sort toolbar button. You will also receive a prompt to provide a parameter value when you try to perform the second Quick Sort. This will not occur if the form's RecordSource property is set to a SQL Select statement instead of a table name.

RESOLUTION

To work around this problem, click Show All Records on the Record menu after changing the record source and before clicking the Quick Sort button on the toolbar.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This behavior no longer occurs in Microsoft Access for Windows 95 version 7.0.

MORE INFORMATION

Steps to Reproduce Problem

Create two identical tables, Table 1 and Table 2, and add the records indicated:

      Table: Table1
      ------------------
      Field Name: Field1
      Data Type : Text

      Add records c, a, b.

      Table: Table2
      -----------------
      Field Name: Field1
      Data Type : Text

      Add records f, e, d.

Create the following form based on Table1:

      Form: Form1
      -----------------------------
      RecordSource: Table1
      Caption: Form1
      Default View: Single Form

      Text box:
         Name: Text1
         ControlSource: Field1
      Command button:
         Name: Button1
         Caption: My Button
         OnClick: [Event Procedure]

Set the command button's OnClick property to the following event procedure:

      Sub Button1_Click()
         Me.RecordSource = "Table2"
      End Sub

Open the Form1 form in Form view. Click the ascending (A-Z) Quick Sort button. Click the command button on the form. This switches the record source to Table2.
Click the ascending (A-Z) Quick Sort button again. Click OK through the parameter dialog box. Note that the Table1 table records (c, a, b) appear in the form.

NOTE: You can eliminate the parameter dialog box by changing the RecordSource property to "Select * from Table1" and "Select * from Table2." This will not affect the Quick Sort problem.

You can verify this in the Immediate window by typing the following line and pressing ENTER:

    ? Forms!Form1.RecordSource

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.