Forms based on user permissions

[schltzy99]

Is there anyway you could pull up specific records on a form based on that persons ID and permissions. The database would pull this persons ID based on their network ID that they use to log into their computer.

This would allow "Owners" to have access to the specific records that they have created, while not being able to view the records of other "Owners". Any suggestions? Thanks. RSchultz
rschultz@insolsys.com
**Access 2000**

 

[mflancour]

My 2 cents....novel userID (in different environments this could be "s_login=" or maybe "userid="....if you have trouble post again and I'll give ya a code for findin out what to use here). As far as limiting records just use a query that checks who added records and if the person logg in to the database matches then show them (I can elaborate if you need).

The commented out portion is also usful if you want to keep track of who has been in the database and when in a User Log table...This concept could be expanded to include recording any actions you want in your log.

Just call this function upon startup and

Public Function GetUserId()
On Error Resume Next
Dim EnvString, Indx, msg, PathLen, UserID
Indx = 1
Do

EnvString = Environ(Indx)
If Left(EnvString, 8) = "s_login=" Then
PathLen = Len(Environ("s_login&quot)
UserID = Right(EnvString, PathLen)

Dim MyDB As Database, Mytable As Recordset, MyTable2 As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set Mytable = MyDB.OpenRecordset("Users", DB_OPEN_TABLE)
Mytable.Index = "UserID"
Mytable.Seek "=", UserID
If Not Mytable.NoMatch Then
'if user id was found then put actions here.
'Set MyTable2 = MyDB.OpenRecordset("Use Log", DB_OPEN_TABLE)
'MyTable2.Index = "PrimaryKey"
'MyTable2.AddNew
'MyTable2("UserId&quot = userid
'MyTable2("Date&quot = Date
'MyTable2("Time&quot = Time
'MyTable2("Action&quot = "In"
'MyTable2.Update
'MyTable2.Close
Else
msgbox "You do not have access to this database."
'whatever action you want to take if not a
'user here, like exit database
End If

Exit Do
Else
Indx = Indx + 1
End If
Loop Until EnvString = ""

End Function

Again, some of this I really skipped over, but if you need more explenation let me know

 

[schltzy99]

There are multiple individuals throughout various stages of this process, who all will have different levels of access based on their permissions. Will it be necessary to create a query for each specific person? If so, a different solution may be best.

Could you elaborate on how to run a specific query upon opening the db? Thanks much. RSchultz
rschultz@insolsys.com
**Access 2000**

 

[LittleSmudge]

Could you add a field to the table that stores the LogOnId of the user that creates the Record. They are then the 'Owner'.

Then when viewing records in a form the filter just displays records where the Owner field matches the current loged on user.

Does this solve your problem - or what have I missed?


G LS

 

[schltzy99]

There currently is a field that will track which owner logs into and creates a position. Could you give more detail about filtering? (How would I use this?) It's an aspect of access that I haven't used much in the past. Thanks. RSchultz
rschultz@insolsys.com
**Access 2000**

 

[LittleSmudge]

The way I'd do it would be in the Form_Open event:-

Private Sub Form_Open()
Me!RecordSource = "SELECT * FROM TableName WHERE OwnerField = '" & NetWorkUser() & "'"
End Sub

Where NetWorkUser() is a function that returns the current user's Netword Id
& TableName is the name of the table that the form is bound to.

( " * FROM TableName " can be replaced with whatever complex SQL code you have in the form's RecordSource at the moment if it is more complex than a single table. )



'ope-that-'elps.

G LS

 

[mflancour]

Yeper...That's pretty much what i was trying to say (though I don't know that you'd have to through the record souce in OnOpen...could just put it static into the recordsource). Were u able to get any of this to work?

 

[LittleSmudge]

Yes mflancour it COULD be done static in the RecordSource if the NetWorkUser() function is simple enough. But I'm not sure how FSDInc is going to go about obtaining the LogInId - so I put the thing in code to provide some extra flexibility.


G LS

 

[oharab]

FWIW: have a look at

http://www.mvps.org/access/api/api0008.htm

for a way of collecting the users NT login ID.

Ben