Setting a variable within a called sub

[xanadude]

Hi All
Firstly thanks for the help I have received so far - much appreciated but here is another

I am trying to create a call sub that checks a user id from the Environ("Username") and then change a variable to say that the user is valid or not.

I have it running by having a lot of If statements but would like to cut down on the overall content by having one list to lookup

This is what I have done so far - just is mess mode but the Variable AdminUser does not change when coming out of the Called Public Sub back to the main sub ?

Private Sub TESTBUTTON_Click()
' Test button for security Access

  AdminUser = -1
   MsgBox [AdminUser]
    Call CallRoutine
     If AdminUser = -1 Then GoTo Pass
   MsgBox "User Not Found"
  MsgBox [AdminUser]

Exit Sub

Pass:
MsgBox "Found User"
  MsgBox [AdminUser]

End Sub

Public Sub CallRoutine()
If Environ("Username") = "Roger.Goswell" Then Exit Sub

MsgBox "User Check"
AdminUser = 0
  MsgBox [AdminUser]

End Sub

I'm sure there is a easier way of doing this but as I am learning on the job to say getting myself lost quite easily..

thanks

 

[DJkarl]

Do I don't see where AdminUser is declared anywhere. If you have not declared the variable then by default it is local in scope.

Many ways to do this, you can put the variable declaration outside either sub

Dim AdminUser as Variant

You could also change the Sub to a function that return the type you need and set AdminUser = to the function.

 

[pr2-eugin]

I am not sure what this code does.. But if you are trying to change the variable AdminUser, you have a small typo.. You have set AdminUser1= 0.. So try changing that to AdminUser..

Also you could rewrite you code as follows..

Dim AdminUser As Integer

Private Sub TESTBUTTON_Click()
    ' Test button for security Access
    AdminUser = -1
    MsgBox (AdminUser)
    Call CallRoutine
    If AdminUser = -1 Then 
        MsgBox "Found User"
        MsgBox (AdminUser)
    Else
        MsgBox "User Not Found"
        MsgBox (AdminUser)
    End If
End Sub

Public Sub CallRoutine()
    If Environ("Username") <> "Roger.Goswell" Then
        MsgBox "User Check"
        AdminUser = 0
        MsgBox (AdminUser)
    End If
End Sub

 

[xanadude]

Thanks for that - Been looking at it too long today

This does the same as What I had happen before

Sorry if explanation is misleading I will try to explain a bit better

On Clicking a button in the form then this VBA runs checking the user is ok to continue....


Step 1 is set the AdminUser to the value of -1
Then Call the Subroutine to Validate the user as OK or Not.
If the user is ok then it Exits the Called Sub without changing the AdminUser value indicating that the user is ok to continue.

If the user is not a valid user it then sets the AdminUser value to 0 and then exist the Called Sub.

Back in the main Sub it Check the AdminUser value = 0 and exit the Main Sub doing nothing.

If it has not been changed and is still -1 then Goto Pass that will then process my next list of commands.



I wish to use this rather than keep using a long list of users as I am multiple times at present ...

Thanks

 

[pr2-eugin]

Okay, walk with me here, so currently you have in the call routine a long long list of If''s.. something like..

Public Sub CallRoutine()
    If Environ("Username") <> "Roger.Goswell" Then
        MsgBox "User Check"
        AdminUser = 0
        MsgBox (AdminUser)
        Exit Sub
    End If
    
    If Environ("Username") <> "Stan.Goswell" Then
        MsgBox "User Check"
        AdminUser = 0
        MsgBox (AdminUser)
        Exit Sub
    End If
    
    If Environ("Username") <> "Steve.Goswell" Then
        MsgBox "User Check"
        AdminUser = 0
        MsgBox (AdminUser)
        Exit Sub
    End If
End Sub

There is a very easy solution, create a table, have three fields,
tbl_activeAgents
agentID - AutoNumber (PK)
activeAgent - Yes/No
agentName - Text

Sample Data in the table..

agentID    agentName    activeAgent
1        Roger.Goswell   True
2        Stan.Goswell    True
3        Steve.Goswell   True
4        Haylie.Goswell  False

So your CallRoutine will be..

Public Sub CallRoutine()
    If Not Nz(DLookUp("activeAgent","tbl_activeAgents","agentName = '" & Environ("Username") & "'"),False) Then
        MsgBox "User Check"
        AdminUser = 0
        MsgBox (AdminUser)
        Exit Sub
    End If
End Sub

This way you always have a list of all active agents.. Easy to manage and verify..

 

[xanadude]

Thank you very much I will look at this and let you know if I have managed it but sounds a much easier option

Again thanks

 

[pr2-eugin]

I edited/added more information, so please look at it again...

Post back if you get stuck..

 

[xanadude]

Hi And thanks for all you help. This is what I have done and its working using your example as a guide....

Created a Button that checks auth when clicked before continuing...

Table Created call SecurityAccessList

agentID    agentName       Dept1     Dept2     Dept3
1             Roger.Goswell    True       True       True
2             Ian.Goswell       True       False      False
3             Dave.Jones       False      False      True

The "Dept" fields here enable me to use the same table to auth multiple groups access to different forms just by changing the "Dept" group in the VBA


Then used this VBA for auth check

Private Sub TESTBUTTON_Click()
    ' Test button for security Access
    If Not Nz(DLookup("Dept1", "SecurityAccessList", "agentName = '" & Environ("Username") & "'"), False) Then
        MsgBox "User Not In Access List - Exit Subroutine and Close Form "
        Exit Sub
    End If
    
    MsgBox "User Authorised. Continue with the next step"
End Sub

Hope this helps other newbies
Again Thanks for the help

 

[pr2-eugin]

Glad you have it sorted.. and thanks for Posting your solution back..

Good Luck !!

 

[xanadude]

No problem...... Its nice to actually be on a group that is willing to help without being made feel stupid. Even if like me new to this and going from a basic telephone list knowledge of access to being asked to create in depth as I am doing now......

Again thanks