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

ID: Q146310


The information in this article applies to:
  • Microsoft Access versions 7.0, 97


SUMMARY

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

This article describes 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 files, RptSampl.exe (for Microsoft Access for Windows 95 version 7.0) and RptSmp97.exe (for Microsoft Access 97). For information about how to obtain these sample files, please see the following articles in the Microsoft Knowledge Base:

Q145777 ACC95: Microsoft Access Sample Reports Available in Download Center

Q175072 ACC97: Microsoft Access 97 Sample Reports Available in Download Center


MORE INFORMATION

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

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/Export. 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: Sort Form
          ---------------------
          ScrollBars: Neither
          RecordSelectors: No
          NavigationButtons: No
          PopUp: Yes
          BorderStyle: Thin
          MinMaxButtons: None 


  2. Set the form's OnOpen property 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
             ' ("acDesign" in Microsoft Access version 7.0)
             DoCmd.Maximize
          End Sub 


  3. Set the form's OnClose property 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:

    NOTE: In the SQL expressions below, 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 expression.
    
          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, which you can use 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
             End Sub 


  8. Close and save the form as XXXXX.


Sorting the Report


  1. Open XXXXX 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 should appear sorted by the field 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 you selected in the combo box.



REFERENCES

For more information about the filter property, search the Help Index for "Filter Property," or ask the Microsoft Access 97 Office Assistant.

For more information about filter by form or filter by selection, search the Help Index for "Filter By Form" or "Filter By Selection," or ask the Microsoft Access 97 Office Assistant.

Additional query words: dynamically

Keywords : FmrCdbeh
Version : 7.0 97
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: November 9, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.