ACC2000: How to Set Multiple Sort Order Values on a Data Access Page (234986)

MORE INFORMATION

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. The following sections show you how to create an HTML page (Frameset Page) and two data access pages (Header Page and Detail Page) in the sample database Northwind.mdb.

Header Page

The Header page contains three dropdown list controls, six option buttons, and two command buttons that you use to control the sort order of the Detail page. The dropdown list controls allow you to choose which fields to sort by. The option buttons allow you to decide whether those fields are sorted in ascending or descending order. One command button sorts the Detail page and the other command button resets the dropdown lists, option buttons, and the sort order of the detail page.

1. Open the sample database Northwind.mdb.
2. Create the following table named tblSort:

      Table: tblSort
      -------------------
      Field Name: SortFld
      Data Type: Text
   
      Table Properties: tblSort
      -------------------------
      PrimaryKey: SortFld
   					

3. Add the following records to the table:

      City
      CompanyName
      ContactName
      Country
      CustomerID
      Region
   					

4. Create the following page named dapSortHeader:

      Command button
      --------------
      ID: cmdSort
      InnerText: Sort
   
      Command button
      --------------
      ID: cmdClear
      InnerText: Clear
   
      Dropdown list
      -----------------------------
      ID: SortList1
      ListRowSource: Table: tblSort
      ListDisplayField: SortFld
      ListBoundField: SortFld
   
      Dropdown list
      ------------------------------
      ID: SortList2
      ListRowSource: Table: tblSort
      ListDisplayField: SortFld
      ListBoundField: SortFld
   
      Dropdown list
      ------------------------------
      ID: SortList3
      ListRowSource: Table: tblSort
      ListDisplayField: SortFld
      ListBoundField: SortFld
   
      Option group 
      ------------------------------
      ID: Frame1
      BorderStyle: none
   
      Set the group legend's Visibility property to hidden.
   
      Option button (inside Frame1)
      ------------------------------
      ID: OptionAsc1
   
      Set the InnerText property of the button's Label to Ascending.
   
      Option button (inside Frame1)
      ------------------------------
      ID: OptionDsc1   
   
      Set the InnerText property of the button's Label to Descending.
   
      Option group 
      ------------------------------
      ID: Frame2
      BorderStyle: none
      
      Set the group legend's Visibility property to hidden.
   
      Option button (inside Frame2)
      ------------------------------
      ID: OptionAsc2
   
      Set the InnerText property of the button's Label to Ascending.
   
      Option button (inside Frame2)
      ------------------------------
      ID: OptionDsc2
   
      Set the InnerText property of the button's Label to Descending.
   
      Option group 
      ------------------------------
      ID: Frame3
      BorderStyle: none
   
      Set the group legend's Visibility property to hidden.
   
      Option button (inside Frame3)
      ------------------------------
      ID: OptionAsc3
   
      Set the InnerText property of the button's Label to Ascending.
   
      Option button (inside Frame3)
      ------------------------------
      ID: OptionDsc3
   
      Set the InnerText property of the button's Label to Descending.
   					

5. On the Tools menu, point to Macro, and then click Microsoft Script Editor.
6. On the HTML menu, point to Script Block, and then click Client.
7. Type the following script:

   <SCRIPT language=vbscript>
   <!--
   Public strSort
   -->
   </SCRIPT>
   					

8. Using the Script Outline, insert the following script for the onClick event of cmdSort:

   <SCRIPT event=onclick for=cmdSort language=vbscript>
   <!--
   Dim i
   
   strSort = ""
   
   For i = 1 to 3
      ' Determine which dropdown lists contain field names.
      If Document.All.Item("sortList" & i).Value <> "" Then
         ' Add the field names to the sort order sting.
         strSort = strSort & Document.All.Item("sortList" & i).Value 
         If Document.All.Item("OptionDsc" & i).Checked = True Then
            ' Add the word "Desc" (to sort the field in descending order)
            '   and a list separator to the sort string.
            strSort = strSort & " Desc, "
         Else
            ' Add a list separator to the sort string. It will automatically
            '   sort the field in ascending order, however, you could include
            '   then word "Asc".
            strSort = strSort & ", "
         End if
      End if
   Next
   
   ' Remove the extra characters from the end of the sort string
   If Right(strSort, 2) = ", " Then
      strSort=Left(strSort, Len(strSort)-2)
   End if
   
   ' Set the sort order for the Detail page.
   Window.Parent.Frames("dtl").MSODSC.DataPages(0).Recordset.Sort=strSort
   -->
   </SCRIPT>
   					

9. Using the Script Outline, insert the following script for the onClick event of cmdClear:

   <SCRIPT event=onclick for=cmdClear language=vbscript>
   <!--
   
   ' Reset all the dropdown lists and option buttons.
   For i = 1 to 3
      Document.All.Item("SortList" & i).Value = ""
      Document.All.Item("OptionAsc" & i).Checked = False
      Document.All.Item("OptionDsc" & i).Checked = False
   Next
   
   ' Clear the sort string
   strSort = ""
   
   ' Set the sort order for the Detail page back to the default.
   Window.Parent.Frames("dtl").MSODSC.DataPages(0).Recordset.Sort=strSort
   
   -->
   </SCRIPT>
   					

Detail Page

The Detail page is the page that shows the records in the sort order specified by the Header page.

1. Create the following page named dapSortDetail based on the Customers table:

      Text box
      -------------------------
      ID: CustomerID
      ControlSource: CustomerID
   
      Text box
      --------------------------
      ID: CompanyName
      ControlSource: CompanyName
   
      Text box
      --------------------------
      ID: ContactName
      ControlSource: ContactName
   
      Text box
      -------------------
      ID: City
      ControlSource: City
   
      Text box
      ----------------------
      ID: Country
      ControlSource: Country
   
      Text box
      ---------------------
      ID: Region
      ControlSource: Region
   					

2. On the Page Design toolbar, click Sorting and Grouping.
3. Under Group Record Source, click Customers, and then set the CaptionSection property to Yes. Set the DataPageSize property to 10.
4. Move the labels to the Caption section.
5. Arrange all the text boxes and labels in a line near the top of the appropriate section. Decrease the size of section to remove extra blank space.

Frameset Page

The Frameset page is the HTML page that loads the two data access pages.

1. Using an HTML or text editor, create the following HTML page and save it as SortFrame.htm in the same folder as the two data access pages:

   <HTML>
      <FRAMESET ROWS="18%, *">
         <FRAME ID="hdr" SRC="dapSortHeader.htm">
         <FRAME ID="dtl" SRC="dapSortDetail.htm">
      </FRAMESET>
   </HTML>
   					

2. Use Microsoft Internet Explorer to open the SortFrame.htm page.