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

• Microsoft Access 2000

SUMMARY

This article shows you how to create a pop-up form for setting the sort order of data in a report. Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

MORE INFORMATION

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: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform 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: Sort Form
   ---------------------
   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 

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" &amp;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 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.

REFERENCES

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.

Additional query words: dynamically AccCon

Keywords          : kbdta FmrCdbeh 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: July 6, 1999