question

JustinSwanson-4770 avatar image
0 Votes"
JustinSwanson-4770 asked BobLarson-6601 answered

How to use GetUserName to Limit User and Control order of Checkboxes MS Access VBA?

We are using check boxes as Quality Control Check points for various steps in a manufacturing process.
After each step a certain manager must check a box, before it goes on to the next step.
However, I have a manager checking their steps out of order, ahead of the previous step.
I currently have a GetUserName function I use in other aspects of the database.
How can I use this to allow just certain users to Check boxes and prevent others from checking their boxes out of order.
I have in mind to use an unbound text box to enter username on current to allow database who is viewing the record, but I am kind of stuck on where to go from there...
Any thoughts???

Thanks,

jswan1001

office-vba-dev
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I've decided that it would be best to Enable/Disable the control using the Enabled property based on the Username. Which works pretty good except when i have special case (Enabling a check box based on the checked/unchecked status of another) how can i adjust following code to override a Lock/Disable if the right conditions are met?

Private Sub Form_Current()
Me.Text804 = GetUserName
If Me.Text804 = "blaird" Then
Me!Check396.Enabled = True
Me!Check806.Enabled = False
If Me.Text804 = "bswanson" Then
Me!Check396.Enabled = False
Me!Check806.Enabled = True
If Me.Text804 = "pswanson" Then
Me!Check396.Enabled = False
Me!Check806.Enabled = False
If Me.Text804 = "jmilam" Then
Me!Check396.Enabled = False
Me!Check806.Enabled = False
If Me.Text804 = "jswanson2" Then
Me!Check396.Enabled = True
Me!Check806.Enabled = True
If Me.Check396 = False Then
Me!Check806.Enabled = False
End if
End If
End If
End if
End If

Thanks,

Js

0 Votes 0 ·

With username "bswanson" I would like me.check806 to be disabled, unless me.check396=checked/true

0 Votes 0 ·
KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered

Each Case statement should be the value of the Text804 control for which you want the code which follows to be executed, so you can shorten the code as below. To exclude any of the code from executing if your special case criterion is met, then the code can be wrapped in an If….End If construct:

     Me.Text804 = GetUserName
        
     If Not <your special case criterion goes here>
         Select Case Me.Text804
             Case "bswanson"
             Me!Check396.Enabled = False
             If Me.Check396 = False Then
                 Me!Check806.Enabled = False
             End If
             Case "blaird"
             Me!Check396.Enabled = True
             Me!Check806.Enabled = False
             Case "pswanson"
             Me!Check396.Enabled = False
             Me!Check806.Enabled = False
             Case "jmilam"
             Me!Check396.Enabled = False
             Me!Check806.Enabled = False
             Case "jswanson2"
             Me!Check396.Enabled = True
             Me!Check806.Enabled = True
         End Select
     End If



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BobLarson-6601 avatar image
0 Votes"
BobLarson-6601 answered

I'll point out something as well. This is a bad idea simply because hard coding information is a maintenance nightmare. You could use a hidden sheet to list users and have ROLES defined which you use in code and then do a look up in the hidden sheet which lists the user and their role. That way the code will not need updating and you can just add/edit/delete users as necessary on the user sheet list.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered KenSheridan-7466 edited

There are two ways you could prevent an email being sent:

  1. In the code in the Command179 button's Click event procedure make it conditional on the value of the Check806 control:

    If Me.Check806 = False Then
    MsgBox "Your MessageGoes Here", vbExclamation, "Invalid Operation"
    Else
    ' your code to send an email
    End If

  2. At the end of the code I posted earlier, add some further code after the End Select line to disable the Command179 button button:

    Me.Command179.Enabled = (Me.Check806 = True)

Do the same in the form's Current event procedure.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered JustinSwanson-4770 commented

PS: Bear in mind that without further code the Enabled property of a control will remain at the same value when the user moves off the current record. You will probably want to add code before the above code which sets each check box's Enabled property to a default value of True before disabling them conditionally.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Ken, I think this is going to work great! I really appreciate it!
For now I've removed the Special Criterion portion because I'm not sure that I have one at this point.
But I would like to take this code a step further and Disable an Email from being sent with Outlook automation via a command button [Me.Command179] until Me.Check806=True. Would that go best in the aforementioned Criterion portion or would that go best elsewhere?
What do you suggest for setting the check box's Enabled property to a default value of True before disabling them conditionally?

Thanks,

Js

0 Votes 0 ·
KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered JustinSwanson-4770 commented

If you handle your special case first in an IF construct in the Current event procedure, then put the code to execute on the basis of the current user in an ELSEIF construct, the latter will only execute in cases other than the special case.

As regards your current code, by nesting the IF constructs, if user 'blaird' is logged in, then all of the subsequent IF lines will evaluate to False. However, if user 'bswanson' or any of the other users is logged in, the IF lines catering for those users will never execute, because the IF line for 'blaird' will already have evaluated to False. This means than only 'blaird' will be identified as the current user.

Rather than using nested IF constructs use a SELECT CASE construct. This will examine the return value of the GetUserName function successively until the current user is identified.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Ken, I'm not expert coder, but i think i understand what you're saying. I've never used a Select Case statement before, but below is what i have so far, i know it's not correct because it's not working. Can you take a look? I'm not too good with variables either. Would I use the Me.Text804=GetUserName? Or can I set the username variable with the GetUserName Function?

Private Sub Form_Current()
Dim username As String
Me.Text804 = GetUserName
username = GetUserName
Select Case username
Case 1
Me.Text804 = "bswanson"
Me!Check396.Enabled = False
If Me.Check396 = False Then
Me!Check806.Enabled = False
End If
Case 2
Me.Text804 = "blaird"
Me!Check396.Enabled = True
Me!Check806.Enabled = False
Case 3
Me.Text804 = "pswanson"
Me!Check396.Enabled = False
Me!Check806.Enabled = False
Case 4
Me.Text804 = "jmilam"
Me!Check396.Enabled = False
Me!Check806.Enabled = False
Case 5
Me.Text804 = "jswanson2"
Me!Check396.Enabled = True
Me!Check806.Enabled = True
End Select
End Sub

0 Votes 0 ·