Users can edit only their records???

[ekta]

Hi:

I want to create a username and password for my form. There will be 6 users who will use this database. Every user has records related to him on the form. Out of these 6 users, 5 will be able to see all the records on the form but they can edit only their records and not other users records. The 6th user can edit any record. I have never done this before so I would appreciate it if you would tell me step by step how to do it.

Thanx
Ekta

 

[ekta]

Hi Pat:

Thanx for your reply. That is exactly what I am looking for. Can you please tell me how to set up UserID as a global variable and then compare the logged in UserID with the userID in the record. I get the idea but don't know how to implement it. I am doing this for the first time so please bear with me.

Thanx again
Ekta

 

[ekta]

Hi Pat:

Thanx for your help but I think I'll need some more help.
You told me that I'll compare the gUserID with Me.txtUserID and this Me.txtUserID will be the field on my record. So do I need to add this field to the table on which my form is based. And how will this field be populated. I am little confused. And what about the password..I don't need to add password on the login form.

Thanx again
Ekta

 

[ekta]

Hi Pat:

Yea, I understand that. I din't mention it b4.. the users can view all the records and edit their own records but they can't add a new record.. the new record would be added by that 1 user who will have access to all the records.

 

[mission2java_78]

Don't mean to interfere here but wouldnt it be better to create a table of privelages...what if later on down the road two people need to be able to add-in records. You have to expand your thinking for the future.

Table of users: tblUsers

Fields:
UserID
User
Password
Read
Write
Execute

Probably a few more fields but this is the general idea..read/write/execute are bit fields (vbYes/vbNo).

You can then create your form with two text boxes txtUserName, txtPassword. Have the user enter these in and then either using a recordset to loop through the users or DLookup check if this is a user that should be able to access the system. As pat had suggested you probably want a global to set the name of the user once logged in...so right after the user was allowed to enter call the function in your module to set the user name variable.

public function setName(someStr as String)
glbStrUser = someStr
end function

public function getName() as String
getName = glbStrUser
end function

Jon

 

[mission2java_78]

Each record once created should have the name of the user who created it. So couldnt you compare this value with that of the global variable?

Is it only the person who created the record could modify it ?

Jon

 

[mission2java_78]

At the moment I have no idea how ekta intends to identify which record belongs to which person.

Ekta,

Why not have a field to store the userID of the person whom created the record?

Jon

 

[mission2java_78]

Am I missing something here?

etka says 5 of the users can edit any of their records while the "Super" user can edit any and all of the records. As pat said the 6th user would have to store the user name of that persons record on each record when creating it. Do we agree here?

If so I dont see a problem with any of the 5 users logging in and checking there login against a record with their name on it (in which the super user had entered) if the names match allowedits= true else allowedits = false.

Jon

 

[mission2java_78]

Maybe ekta doesnt care anymore and we are just sitting here being rowdy over nuttin

Have a good weekend pat.

Jon

 

[ekta]

Hi Pat & Jon:

I am sorry for not replying. I juss saw your replies today. I was out of town.
I appreciate your help.
Like Jon said..I think this can be done.. the 6th user can add the user name when adding a new record. Right now I have this field "CaptureLead" in which each users lastname is entered to identify which user is responsible for which record. Should I add another field UserID on this form or can I compare it with CaptureLead?

Ekta

 

[ekta]

ok I'll do that

So, now what method would be good for this. The one you told me or the one Jon told me. How should I go abt it?

 

[Autoeng]

See here for a sample. http://www.rogersaccesslibrary.com under samples for 'UserSeesOnlyHisRecords2k.mdb'

Autoeng

 

[mission2java_78]

Ahh erased everything I had typed!!!!

What I had said originally is to store these
names in some sort of table so that its consistant.

Having the user type in :

Jean Claude Alexandris III on monday
may become

Jean Claud Alexandria III on tuesday

on accident of course so if these names are stored in
a table you can create a combo box that basically uses this
table...that way your data is consistant from record to record.

Then you can do something like this:

If GetOSUSerName = Me.cboNames.Column(1) then
'then correct user
else
'incorrect user
end if

Jon]

 

[ekta]

Jon:

I am not sure what you want me to do here.

 

[mission2java_78]

You said one user would enter the name of the employee right?

Dont you think entering names everytime could lead to error in spelling or at least could lead into some type of mistake?

I want you to first create a table : tblUsers

UserID: Autonumber
Field: User (text)

Then you can create a control on your record a combo box of say UserID which has a listing of these users from this table.

Do this and get back to me.

Jon

 

[ekta]

Jon:

I created a table and added the two fields: UserID & User.
I then created a combo box on my formvwhich displays the users.

Ekta

 

[mission2java_78]

Good so is that control bound to some field on your table?

Meaning you have a tblOfUsers and a tblOfRecords.

Your form is based on tblOfRecords / some query. You need to make sure in your tblOfRecords there is a field called UserID...
which is the field from tblOfUsers.


You have to make sure this control (the drop down) is bound to the UserID field in your tblOfRecords.

Jon

 

[ekta]

Yes, the combo box is bound to the USERID field in my tblOfRecords. The values in the combo box come from tblOfUsers.

 

[mission2java_78]

ok...
than you're all set.

Now in the on-current event you can check if the current user is equal to the user specified on that combo box using code.

Use the function that returns the current users name

Module which returns the NT Login Name

Make these Global Modules:

Option Compare Database
Option Explicit

Private Declare Function apigetusername Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apigetusername(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function

Then you could do the following:

if fOSUserName = Me.cboUser.Column(1) then 'Column 1 refers to the actual name and not the ID
'do something
else
'do something else
end if

Jon