Simple Database question

[lotaguts]

I have designed a simple HTML Data access page using the wizard in Access 2003. This is the frontend to a simple table I have at the backend. One of the fields in the page is "Salesman". My question is I want to restrict salesman from seeing other salesman records. So when they access the page they can only see there records. I will restrict the backend to only managers though the network permissions so they wont be able to open the .mdb but, its at the access page that my problem lies. Now when they login into it they can scroll through all the records in the database. Is there a simple way to restrict this. thanks for any info.

 

[willir]

A simple approach would to include the login name of the SalesRep in the table design for the records.

tblOrder
OrderID - pk
OrderDate
SalesRep
UserName
...etc

The UserName field is hidden on the form - never seen by the end user.

When an order is placed by a SalesRep, capture their login name.

UserName = environ("username")

Then, when displaying orders, the RecordSource for the form will always include username...

SELECT * from tblOrder WHERE UserName = environ("username")

With VBA code, it would be some like the following with the OnOpen event...

Dim strSQL as String, strUser as String, strQ as String

strQ = Chr$(34)
strUser = environ("username")

strSQL = "SELECT * FROM tblOrder WHERE UserName = " strQ & strUser & strQ

Me.RecordSource = strSQL
Me.Requery

Richard

 

[lotaguts]

Can you elaborate on this ? I am using an access page not a form. Where can I add this script in the access page ? Any details would be great ? thanks