ACC2000: How to Sort a Report from a Pop-Up Form

This article shows you how to create a pop-up form for setting the sort order of data in a report.

NOTE: This article explains a technique demonstrated in the sample file, RptSmp00.mdb. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base: Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

This technique involves creating a pop-up form and a report in the sample database Northwind.mdb. The form enables you to choose which report fields to sort on and in which order: ascending or descending.

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.

Creating the Report

1. Open the sample database Northwind.mdb.
2. Start the Report Wizard and create a report based on the Customers table.
3. In the Which fields do you want on your report box, select the following fields:

      CompanyName
      ContactName
      City
      Region
      Country
   					

4. Click Finish to display the new report in Print Preview.
5. On the File menu, click Save As. Enter Sort Report as the report name and click OK.
6. Close the report.

Creating the Pop-up Form

1. Create a new form not based on any table or query in Design view with the following form properties:

      Form: frmSortReport
      ---------------------
      ScrollBars: Neither
      RecordSelectors: No
      NavigationButtons: No
      PopUp: Yes
      BorderStyle: Thin
      MinMaxButtons: None
   					

2. Set the OnOpen property of the form to the following event procedure:

   Private Sub Form_Open(Cancel As Integer)
      ' Opens the report in Design view when the form opens.
      DoCmd.OpenReport "Sort Report", acviewDesign
      DoCmd.Maximize
   End Sub
   					

3. Set the OnClose property of the form to the following event procedure:

   Private Sub Form_Close()
      ' Closes the report when the form closes.
      DoCmd.Close acReport, "Sort Report"
      DoCmd.Restore
   End Sub
   
   					

4. Add the following five combo boxes:

      Combo box
      ------------------------------
      Name: Sort1
      RowSourceType: Field List
      RowSource: Select [CompanyName], [ContactName], [City], [Region],
                [Country] from Customers
      
      Combo box
      ------------------------------
      Name: Sort2
      RowSourceType: Field List
      RowSource: Select [CompanyName], [ContactName], [City], [Region],
                [Country] from Customers
      
      Combo box
      ------------------------------
      Name: Sort3
      RowSourceType: Field List
      RowSource: Select [CompanyName], [ContactName], [City], [Region],
                 [Country] from Customers
      
      Combo box
      ------------------------------
      Name: Sort4
      RowSourceType: Field List
      RowSource: Select [CompanyName], [ContactName], [City], [Region],
                 [Country] from Customers
      
      Combo box
      ------------------------------
      Name: Sort5
      RowSourceType: Field List
      RowSource: Select [CompanyName], [ContactName], [City], [Region],
                 [Country] from Customers
   					

5. Add the following five check boxes next to the combo boxes on the form. You can use these check boxes later for selecting ascending or descending order for your report:

      Check box
      ------------------------------
      Name: Check1
   
      Check box
      ------------------------------
      Name: Check2
   
      Check box
      ------------------------------
      Name: Check3
   
      Check box
      ------------------------------
      Name: Check4
   
      Check box
      ------------------------------
      Name: Check5
   					

6. Add the following command button to the form, which enables you to reset the values in the form's combo boxes and check boxes:

      Command button
      ------------------------------
      Name:Clear
      Caption:Clear
      OnClick: [Event procedure]
   
      Set the OnClick [Event procedure] to the following:
   					

   Private Sub Clear_Click()
     Dim intCounter as Integer
       For intCounter = 1 To 5
         Me("Sort" & intCounter) = ""
         Me("Check" & intCounter) = ""
       Next
   End Sub
   					

7. Add the following command button to the form:

      Command button
      ------------------------------
      Name SetOrderBy
      Caption SetOrderBy
      OnClick: [Event procedure]
   
      Set the OnClick [Event procedure] to the following:
   					

   Private Sub SetOrderBy_Click()
      Dim strSQL as String, intCounter as Integer
      ' Build strSQL String.
      For intCounter = 1 To 5
         If Me("Sort" & intCounter) <> "" Then
            strSQL = strSQL & "[" & Me("Sort" & intCounter) & "]"
            If Me("Check" & intCounter) = True Then
               strSQL = strSQL & " DESC"
            End IF
          strSQL = strSQL & ", "
         End If
      Next
   
      If strSQL <> "" Then
         ' Strip Last Comma & Space.
         strSQL = Left(strSQL, (Len(strSQL) - 2))
         ' Set the OrderBy property.
         Reports![Sort Report].OrderBy = strSQL
         Reports![Sort Report].OrderByOn = True
      End If
   
      DoCmd.OpenReport "Sort Report", acViewPreview
      
   End Sub
   					

8. Close and save the form as frmSortReport.

Sorting the Report

1. Open frmSortReport in Form view. Note that the report opens in Design view behind the form.
2. Select a value in the first combo box, and then click the SetOrderBy button. The report then appears sorted by the field that you selected in the combo box.
3. Click to select the first check box, and then click the SetOrderby button. You should see the report sorted in descending order by the field that you selected in the combo box.

For more information about the Filter property, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type filter property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about Filter by Form, click Microsoft Access Help on the Help menu, type filter by form in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about Filter by Selection, click Microsoft Access Help on the Help menu, type filter by selection in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.