Filtering records in Data Access Pages

[Iashia06]

How can I limit the recordset based on selection criteria in a DAP?

 

[fneily]

From the Support.microsoft.com site (I've used this and it works)

To create the data access page and the appropriate script to find a selected record, follow these steps:
1. Open the sample database Northwind.mdb.
2. In the Database window, click Pages under Objects, and then double-click Create data access page by using wizard.
3. Under Tables/Queries, click the arrow, and then click Table: Customers in the list.
4. Click >> to move all the fields from the Available Fields list to the Selected Fields list.
5. Click Finish.
6. In the toolbox, click Dropdown List, and then click anywhere on the page to add the Dropdown List control.
7. Set the following properties for the Dropdown List control:
ID: CustID
ListRowSource: Table: Customers
ListBoundField: CustomerID
8. Right-click anywhere on the page, and then click Microsoft Script Editor on the shortcut menu to open the HTML source.
9. On the View menu, point to Other Windows, and then click Script Outline. This places the focus in the Script Outline window pane, at the top-most node that is labeled Client Objects & Events. Expand Client Objects & Events, expand CustID, and then double-click the onchange event.
10. Insert the following script:<SCRIPT LANGUAGE=vbscript FOR=CustID EVENT=onchange>
<!--
Dim rs
Set rs = MSODSC.DefaultRecordset
rs.Find "[CustomerID] = '" & document.all.item("CustID").value & "'", 0, 1, 1

'0 = Skip zero records before starting the search.
'1 = Search in a forward direction.
'1 = Always begin the search with the first record in the recordset.
-->
</SCRIPT>

NOTE: To search for numeric data, change the command line in the script as follows:
rs.Find "[CustomerID] = " & document.all.item
"CustID").value


11. Save the page as Page1.htm, and then click Page View on the View menu.
12. Click a value in the CustID drop-down list to move the data access page to that record.

Me again, next to the rs.Find is where you put in your selection criteria. SQL!

 

[Iashia06]

I have added the script you indicated, but it still doesn't work! What I want to do is be able to filter the recordset based on the user logged in. I have set security on the database, and I am still able to view ALL records, not just the ones that are pertinent to my username/department. I have three data access pages. The first records projects, and the second records tracking information about the projects. The third page is based on a report I created, and I can't seem to view one record at a time here either. For the reporting page, it shows a continuous page with all the records on file. I would like to be able to filter the records based on the department the user belongs to. This is the only setback to my project being complete.

Thanks in advance,

Iashia

 

[fneily]

I don't know how your logic flows, but I would have an entry DAP that asks for a LoginID and a password. Based on that, the other DAP's display the appropriate data. Now to do this trick, first you have to write vbscript code for the authentication. You then write code to create a local COOKIE on their computer to hold this info. Then you write code for each DAP to read the cookie for the info. Then you can use the code already given to filter. It's quite tedious. Look through the following web site for clues. The microsoft support site also has information.

http://www.kayodeok.btinternet.co.uk/favorites/kbofficeaccesshowto.htm