How to lock fields from a user

[moes123]

Hello

I have created a database that is used by multiple users, two of whom does day to day data entry of all records, some users only uses this database to enter information in one field "Invoice #" of each record, some users only filters data from the db to get info. to cross reference other work.
I simply want to lock these fields for:
1. Users only entering data in the field Invoice #, they must not be able to change any data of the other fields of the records
2. To only allow the users viewing data, but not to be able to make any changes whatsoever.

I appreciate any help and thanks in advance,

Moes.

 

[dhookom]

I hope you have users log into a network witht their own credentials so you can use code like Get Login Name to compare a user with a table with permissions/roles.

You can then use code in the On Open event of your forms to loop through your controls and enable/lock controls based on the user's role.

Duane
Hook'D on Access
MS Access MVP

 

[moes123]

Duane what's up,

Currently this database resides in a shared drive in the server, users simply log on to their pc's and open the db from the mapped drive, i am willing to be guided by your advice on this problem, i however don't know vb therefore "Get logon name" and setting up these codes in the On Open event will be difficult, any more info will be greatly appreciated.

Moes.

 

[dhookom]

Baby steps
Copy the code from the previous link into a new, blank module and save the module with the name "modAPIs". Then open the debug window (press Ctrl+G) and enter:

? fOSUserName()

After pressing enter, you should see your network login on the second line.

The next step would be to create a table that stores the logins with a field for the role the user has in the application.

Once the table and records are updated, you can open each data form(s) and enter something into the Tag property of all controls that you want to lock. Assuming your roles are something like "E" for editing and "A" for administering, I would enter something in the Tag like:
#EA <-for controls that are locked to everyone except editing and admin
#A <- for controls that are locked to everyone except admin

Then you can write some code that:
- runs on form open,
- finds out the user role,
- loops through controls looking at the tag for #
- set the Locked/Enabled property


Duane
Hook'D on Access
MS Access MVP

 

[moes123]

Duane,
I was able to see my logon id and created the table with the roles for each user: I used #A for Administering, #ED for Editing/admin and #V for users only viewing. The info. was filled out in the table directly, As for the data form and entering the tag property, do i have to create a form next.
Also, i guess the next step will be to write the codes for the last four options, your help again needed

thanks bro for assisting,

moes.

 

[missinglinq]

Currently this database resides in a shared drive in the server, users simply log on to their pc's and open the db from the mapped drive...

If this means that this is a non-split database multiple users are utilizing, as it sounds, the first ordere of business has to be to split the app into a front end/back end configuration with the back end, containing the tables, on the shared drive and a copy of the front end, with everything else, on each user's machine.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[moes123]

Hi there,
Yes, i have not split this db, i guess this is because i don't know much about it and how it works when it comes to making changes to the form and tables later on. I've read posts many times that users whom split their db ends up with it much slower than if it was not split, in my case we have a bandwidth problem and i was wondering if it would be the right way to go.
If this is how it should be done then i will split the db, am just wondering when problems occue e.g error messages, i would normally take a copy of the db, fix the problem, copy newly entered records in it and put in the server for users to continue working, i would then remove the error db.
Your advice on this situation is needed,

Thanks in advance - Moes.

 

[dhookom]

Splitting is a good idea but it's not the primary question in this thread.

Private Sub Form_Current()
    LockIt
End Sub

Sub LockIt()
    Dim ctl As Control
    Dim strRole As String
   [green] ' GetRole() is a function that looks up a _
      user's role based on their login   [/green]
    strRole = GetRole()  
    For Each ctl In Me.Controls
        If Left(ctl.Tag, 1) = "#" Then
            If InStr(ctl.Tag, strRole) = 0 Then
                ctl.Locked = True
                ctl.ForeColor = vbRed
             Else
                ctl.Locked = False
                ctl.ForeColor = vbBlack
            End If
        End If
    Next
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[moes123]

Duane, i copied and paste the codes you posted in a new module, so what's the next step.

Moes.

 

[dhookom]

Tell us what if anything is happening. Did you debug? Do you have a function GetRole() that returns the role of the user?

Duane
Hook'D on Access
MS Access MVP