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

MORE INFORMATION

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.