This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
This article was previously published under Q146310
Moderate: Requires basic macro, coding, and interoperability skills.
On This Page
SUMMARY
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:
145777 (http://support.microsoft.com/kb/145777/EN-US/) ACC95: Microsoft Access Sample Reports Available in Download Center
175072 (http://support.microsoft.com/kb/175072/EN-US/) ACC97: Microsoft Access 97 Sample Reports Available in Download Center
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.
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:
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
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.
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.
Need More Help? Contact a Support professional by Email, Online or Phone.
Customer Service For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
Newsgroups Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Help and Support
Back to the top
