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

ID: Q208532


The information in this article applies to:

This article applies to a Microsoft Access database (.mdb) and 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. 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/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

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:


  4. 
       CompanyName
       ContactName
       City
       Region
       Country 
  5. Click Finish to display the new report in Print Preview.


  6. On the File menu, click Save As. Enter Sort Report as the report name and click OK.


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


  2. 
       Form: Sort Form
       ---------------------
       ScrollBars: Neither
       RecordSelectors: No
       NavigationButtons: No
       PopUp: Yes
       BorderStyle: Thin
       MinMaxButtons: None 
  3. Set the OnOpen property of the form to the following event procedure:


  4. 
    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 
  5. Set the OnClose property of the form to the following event procedure:


  6. 
    Private Sub Form_Close()
       ' Closes the report when the form closes.
       DoCmd.Close acReport, "Sort Report"
       DoCmd.Restore
    End Sub
     
  7. Add the following five combo boxes:


  8. 
       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 
  9. 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:


  10. 
       Check box
       ------------------------------
       Name: Check1
    
       Check box
       ------------------------------
       Name: Check2
    
       Check box
       ------------------------------
       Name: Check3
    
       Check box
       ------------------------------
       Name: Check4
    
       Check box
       ------------------------------
       Name: Check5 
  11. Add the following command button to the form, which enables you to reset the values in the form's combo boxes and check boxes:


  12. 
       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
     
  13. Add the following command button to the form:


  14. 
       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
     
  15. 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