I need to lock fields in a form by user

[GoAutoWay]

thread702-1609388

I'm trying to lock certain fields in a form based on user login and a table created withh user and security levels.

I think the above thread would work but I am new to VB and Access and I do not have the GetRole() function. I am assuming this is user defined?? Thanks,
Go

 

[dhookom]

Do you have a function in your modules that pulls the network login?

Do you have a table in your application that stores the network login as well as security information?



Duane
Hook'D on Access
MS Access MVP

 

[GoAutoWay]

Yes, I used the code listed in the previous threads link to grab the login. I also have a table called employees with the logins and security from A Admin B Manager C User D Read only. Obviously I'm willing to change the table around if I can just get this last part going. I know I'm so close, but I'm also so new to this! Thanks, Toby

 

[dhookom]

Can you share actual significant field names from your employees table?

Have you given some thought to each control on each form and how these should be set based on the security roles?

Duane
Hook'D on Access
MS Access MVP

 

[GoAutoWay]

Yes, The table name is employee

The fields are: UID, First Name, Middle Name, Last Name and Security Level. Security levels are A,B,C and D.

There are many fields in the form that will be locked based on UID and Security Level, at least that is the goal.

I do know which fields should be at which levels.

Thanks,
Toby

 

[dhookom]

Apparently A level has the most rights and D the least. Can we assume that you want to lock any controls with a level letter where the letter is less than the user security level?

Of instance you have a control txtHireDate with C in the tag property. If the user has security of A, B, or C he/she should have the text box unlocked. If the user is security level D then the text box will be locked.

If the previous paragraph is acceptable, you need to enter A, B, C, or D in the Tag property of every control on every form that you want to manage.



Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya GoAutoWay . . .

Apparently your missing the same function [blue]GetRole()[/blue] as asked to be identified in the last post of the link you provided in your post origination ... How to lock fields from a user

Since you already have a piece of code missing ... don't you think you should find it! ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[GoAutoWay]

Well, Ace, yes, that would make sense! It's the only missing link here...

Duane, that is acceptable. It is exactly what I'm trying to accomplish. I need the Getrole() function though...

Thanks,
Toby

 

[dhookom]

There is no GetRole() function until you create it. Consider:

Public Function GetRole() As String
    GetRole = DLookup([Security Level] FROM Employee " & _
        "WHERE UID = '" & fOSUserName() & "'")
End Function

This assumes the UID field is the network login. If it's not then you need to create a field to store the login.

Duane
Hook'D on Access
MS Access MVP

 

[GoAutoWay]

I know this is something I SHOULD be able to fgure out, but the previous code is blowing chunks @ ([Security Level] FROM

It says compile error: List separator or ) expected.


Thanks, GoAutoWay

 

[dhookom]

My bad, mixing SQL statements in DLookup().

Public Function GetRole() As String
    GetRole = DLookup("[Security Level]", "Employee" & _
        "UID = '" & fOSUserName() & "'")
End Function

No comment (positive or negative) regarding

This assumes the UID field is the network login. If it's not then you need to create a field to store the login.

Duane
Hook'D on Access
MS Access MVP

 

[Gammachaser]

Try this:

Dlookup("SecurityLevel","Employee","UID = '" & fOSUserName() & "'")

Duane mixed his metaphors... er syntaxes. His assumption still applies.

 

[GoAutoWay]

I know we jumped to the GetRole function, but I was actually learning and understanding you based on the baby steps you were doing (thanks). You left off with my security levels being A,B,C and D. This is correct.

I realize the on open code will use the getrole function to return the users security based on the level granted in my table and the tag on the control for the form.

What code decides that, because a user is at 'B' level, anything B or greater is unlocked and anything C or less is locked? Obviously at some point this becomes 0 or 1, true or false, locked or unlocked and I'm not seeing where that happens.

Again, I appreciate the baby steps! This is my first go at this so I guess I'm an access/VB virgin and have a long way to go! I need this project done but I also want to know what it is I'm doing instead of copying your code and it magically working!

Thanks,
Toby

 

[dhookom]

You would use code in each form like:

Private Sub Form_Open(Cancel As Integer)
    Dim ctl As Control
    Dim strLevel As String
    strLevel = GetRole()
    For Each ctl In Me.Controls
        If Len(ctl.Tag & "") <> 0 Then 'check for ABCD
            ctl.Enabled = strLevel <= ctl.Tag
        End If
    Next
End Sub

You could also try use a public function in a standard module like:

Public Function LockControls(frm As Form)
    Dim ctl As Control
    Dim strLevel As String
    strLevel = GetRole()
    For Each ctl In frm.Controls
        If Len(ctl.Tag & "") <> 0 Then 'check for ABCD
            ctl.Enabled = strLevel <= ctl.Tag
        End If
    Next
End Function

then call this function from the On Open event of each form:

Private Sub Form_Open(Cancel As Integer)
    LockControls(Me)
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[GoAutoWay]

I have one more twist that has been added.

For some fields in the form, that are available for A,B,and C levels, I need to check to see if the field has a value, if so, lock it except for Admins (A).

I have poked around with this code today, but just haven't put it together correctly. I'm using the code above and it works marvelously. I just need to add the check for some fields and if they're not null lock them from all but A for admins.

Thanks,
Toby

 

[dhookom]

Can you explain "For some fields in the form"?

Can you see yourself adding another letter to the tag property of "some fields in the form"? Perhaps add "N" after the ABCD?

Then you would need to use the Left(ctl.Tag,1) to get the ABCD and Right(ctl.tag, 1) to see if it is N.

Give it a try and come back with your code if you have trouble.

Duane
Hook'D on Access
MS Access MVP

 

[GoAutoWay]

First I will answer your questions. By some fields in the form I mean there are a few fields, that should allow C level users to input the initial data, but not change it. Admins sould bbe the only one that can change it. This does not apply to all of the field I am restricting so your left and right tag solution should work well. I have beat this code up today to the point of confusing myself with both logic and syntax. Here is where I am now, obviously it's not working and some of it is commented trying to troubleshoot.

Also, I have no idea how to check for null values in fields that have the N tagged, hence the me.confused in the code.

For Each ctl In Me.Controls
If Left(ctl.Tag, 1) <> 0 Then 'check for ABCD user role
ctl.Enabled = strLevel <= ctl.Tag 'Enable based on user role
End If
If Right(ctl.Tag, 1) = "N" Then 'Check to see if this section applies
If Left(ctl.Tag, 1) > "A" Then 'Ensure user is not an Admin
MsgBox "made it this far"
'If IsNull(Me.confused) Then 'check to see if field is null
'ctl.Enabled

End If
End If
' End If

 

[GoAutoWay]

I also realize the last line should be If Not IsNull ctl.locked

Again, I have killed my brain today and the logic and syntax needs help!

thanks!

 

[dhookom]

Write your pseudo code on a piece of paper and then write your code to match with If...EndIf and boolean (yes/No) expressions.

You should also change the code back to match mine

   If Len(ctl.Tag) <> 0 Then

If you can't figure this out, come back with your pseudo code or the logic you would use if you were the program.

Duane
Hook'D on Access
MS Access MVP

 

[GoAutoWay]

OK, this thing is still not working. This is where the code stands right now...

For Each ctl In Me.Controls
If Len(ctl.Tag & "") <> 0 Then 'check for ABCD user role
ctl.Enabled = strLevel <= ctl.Tag 'Enable based on user role

If Right(ctl.Tag, 1) = "N" And Left(ctl.Tag, 1) >= "B" And Not IsNull(Me.PONo) Then ctl.Enabled = False

End If

Next

End Sub


This is diasbling PONo regardless if it's populated or logeed in with A for admin.

Thanks