Microsoft KB Archive/208532

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

Article ID: 208532

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q208532



This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

Advanced: Requires expert coding, interoperability, and multiuser skills.



SUMMARY
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:

231851 ACC2000: Microsoft Access 2000 Sample Reports Available in Download Center

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.



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: 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
 Open the sample database Northwind.mdb. Start the Report Wizard and create a report based on the Customers table.  In the Which fields do you want on your report box, select the following fields:   CompanyName ContactName City Region Country  Click Finish to display the new report in Print Preview. On the File menu, click Save As. Enter Sort Report as the report name and click OK. Close the report.</li></ol>

Creating the Pop-up Form
<ol>  Create a new form not based on any table or query in Design view with the following form properties: <pre class="fixed_text">  Form: frmSortReport -  ScrollBars: Neither RecordSelectors: No  NavigationButtons: No   PopUp: Yes BorderStyle: Thin MinMaxButtons: None </li>  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 </li>  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

</li>  Add the following five combo boxes: <pre class="fixed_text">  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 </li>  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: <pre class="fixed_text">  Check box --  Name: Check1

Check box --  Name: Check2

Check box --  Name: Check3

Check box --  Name: Check4

Check box --  Name: Check5 </li>  Add the following command button to the form, which enables you to reset the values in the form's combo boxes and check boxes: <pre class="fixed_text">  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 </li>  Add the following command button to the form: <pre class="fixed_text">  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 </li> Close and save the form as frmSortReport.</li></ol>

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.

<div class="references_section">