Help with Functions (1 Viewer)

[tmyers]

I am still having a hard time learning how to properly use functions.
I find myself repeating a the same process over and over and would like to create a module with a public function.

The code I keep repeating is a simple one. It checks username via Environ("Username") then with another variable, I list a few different usernames to return true/false.

Dim Admin       As Boolean
Dim User        As String

    User = Environ("Username")
    Admin = (User = "user1" Or User = "user2")
    
        If Admin = False Then
        
            MsgBox ("You are not authorized to use this feature")
            Exit Sub
            
        Else
        
            DoCmd.OpenForm "NameMappingEditFrm", acNormal
        
        End If

End Sub

I would possibly have 2-3 different variables that return true/false vs a list of usernames, then return that to the calling procedure to determine if the sub can continue, or exits with a message box. Could someone please give me a hand with writing this function?

 

[Isaac]

Function IsAdmin(strUsername As String) As Boolean

Select Case LCase(strUsername)
    Case "user1", "user2"
    IsAdmin = True
    
    Case Else
    IsAdmin = False
End Select

End Function

'to call it:

If IsAdmin(Environ("username")) = True Then
    'do something appropriate for admins
Else
    'don't do it
End If

 

[Isaac]

I am still having a hard time learning how to properly use functions.
I find myself repeating a the same process over and over and would like to create a module with a public function.

The code I keep repeating is a simple one. It checks username via Environ("Username") then with another variable, I list a few different usernames to return true/false.

Dim Admin       As Boolean
Dim User        As String

    User = Environ("Username")
    Admin = (User = "user1" Or User = "user2")
  
        If Admin = False Then
      
            MsgBox ("You are not authorized to use this feature")
            Exit Sub
          
        Else
      
            DoCmd.OpenForm "NameMappingEditFrm", acNormal
      
        End If

End Sub

I would possibly have 2-3 different variables that return true/false vs a list of usernames, then return that to the calling procedure to determine if the sub can continue, or exits with a message box. Could someone please give me a hand with writing this function?

I'd write one for each usertype you want to determine if is true.
Unless you want to come up with some real mind-boggling weird method like Microsoft did with summing attributes.

 

[tmyers]

Function IsAdmin(strUsername As String) As Boolean

Select Case LCase(strUsername)
    Case "user1", "user2"
    IsAdmin = True
   
    Case Else
    IsAdmin = False
End Select

End Function

'to call it:

If IsAdmin(Environ("username")) = True Then
    'do something appropriate for admins
Else
    'don't do it
End If

Cases, something else I have been trying to learn and get proficient with since they seem nice and flexible. I still have a hard time following your example, but will try and see if writing it out helps a little.

 

[tmyers]

Ok I think I understand it better now. IsAdmin is recognized as the function, then I am passing Environ("Username") to it. It then uses that info to check against the case and returns true/false. There is no "calling" it, I just use the name of the function as if it were a variable.

Why is strUsername within the functions "title" though and not just defined via Dim as string?

 

[Isaac]

Cases, something else I have been trying to learn and get proficient with since they seem nice and flexible.

Yeah, they make it easy to list out multiple values, and even allow the "Is" keyword + comparison operators like > < and between.

So a function generally reduces the need for repetitive code elsewhere. Its name also helps guide the understanding of the future code-reader.
In the case of what I posted of course, the "reduces the need for repetitive code elsewhere" is barely served at all, since it only saves you a line or three of code - but perhaps enough to justify it.

 

[Isaac]

Why is strUsername within the functions "title" though and not just defined via D

That is the mechanism by which we tell VBA that strUsername MUST be passed into the function. It's the reason that the compiler will give you an error if you write something like: If IsAdmin()=true, without passing it in.

You could have a few parameters that you want a function to require, you just list them:

Function Something(strSomething as string, lngSomething as long, optional blSomething as boolean) as String

End Function

... which then means people have to pass strSomething and lngSomething, but blSomething is optional.

Also, some people write functions without the "as something" at the very end of it, but I would start off not getting in that habit, and only do that if you find a good reason for it. I view it about the same as not typing variables. The clearer and more explicit everything in your code is, the easier to follow, understand & predict.

Finally, someone will come along and remind me that = true is unnecessary with statements like If Boolean
This is true & fine; my preference is to include the =True or = False; I just feel it's more quickly readable.

 

[tmyers]

That is the mechanism by which we tell VBA that strUsername MUST be passed into the function. It's the reason that the compiler will give you an error if you write something like: If IsAdmin()=true, without passing it in.

You could have a few parameters that you want a function to require, you just list them:

Function Something(strSomething as string, lngSomething as long, optional blSomething as boolean) as String

End Function

... which then means people have to pass strSomething and lngSomething, but blSomething is optional.

Also, some people write functions without the "as something" at the very end of it, but I would start off not getting in that habit, and only do that if you find a good reason for it. I view it about the same as not typing variables. The clearer and more explicit everything in your code is, the easier to follow, understand & predict.

Finally, someone will come along and remind me that = true is unnecessary with statements like If Boolean
This is true & fine; my preference is to include the =True or = False; I just feel it's more quickly readable.

Ok I get it now. It is a requirement and will not run without passing the information into the function. Still learning. Slowly but surely. I should take a class on coding some day, rather than being mostly self taught (with all of the wonderful people from here's help of course).

 

[theDBguy]

Normally, you would store user permissions in a table, so it's easy to look up.

 

[tmyers]

Normally, you would store user permissions in a table, so it's easy to look up.

I had initially done that, but seemed like a function or two was easier since there is only 2-3 user levels I was after and only dealing with ~5 users I need to define. Seemed like a waste to do a table for so few.

 

[theDBguy]

I had initially done that, but seemed like a function or two was easier since there is only 2-3 user levels I was after and only dealing with ~5 users I need to define. Seemed like a waste to do a table for so few.

Requirements could change at any time. Modifying a table is easy, not so much for functions. Cheers!

 

[tmyers]

Requirements could change at any time. Modifying a table is easy, not so much for functions. Cheers!

Fair enough. I will keep it in mind.

 

[theDBguy]

Fair enough. I will keep it in mind.

Thanks for understanding. Just to give you an example, it's also very common to have a table with only a single record to keep track of any of the following:

1. Version Number
2. Company Info/Logo
3. User Preferences
4. etc... (you get the picture)

So, all the user have to do is update that single record table, and the developer doesn't have to update the code.

 

[Isaac]

Thanks for understanding. Just to give you an example, it's also very common to have a table with only a single record to keep track of any of the following:

1. Version Number
2. Company Info/Logo
3. User Preferences
4. etc... (you get the picture)

So, all the user have to do is update that single record table, and the developer doesn't have to update the code.

Of course, theoretically, I agree with dbGuy. But you still might have a function to determine access level anyway, especially if it involves opening a recordset once to avoid having multiple dlookups - it just all depends on the case.

PS. If the Microsoft team updated Access functions as often as they do Excel, I bet we'd have a Dlookups() function by now. In my dream, it would return multiple columns and still be as efficient.

 

[Gasman]

Fair enough. I will keep it in mind.

What would you do, if you had to make userC an Admin as the current Admin was off sick or on Maternity Leave?

 

[Isaac]

What would you do, if you had to make userC an Admin as the current Admin was off sick or on Maternity Leave?

Redeploy, LOL - that's why I love an ultra robust distribution/versioning system.
Just kidding.........I'm just playing devil's advocate. I agree with you guys on the table stuff.
The only time I deviate is when the information seems too sensitive for tables & my security context doesn't cover it.

 

[theDBguy]

Of course, theoretically, I agree with dbGuy. But you still might have a function to determine access level anyway, especially if it involves opening a recordset once to avoid having multiple dlookups - it just all depends on the case.

The point being was to avoid hard coding data into your functions. That's all.

PS. If the Microsoft team updated Access functions as often as they do Excel, I bet we'd have a Dlookups() function by now. In my dream, it would return multiple columns and still be as efficient.

I'm not good with Excel, but are you saying it has VLOOKUPS()? Anyway, we already have DLookups() now, sort of:

DLookup("Field1 & ';' & Field2 & ';' & Field3", "TableName")

 

[Gasman]

Redeploy, LOL - that's why I love an ultra robust distribution/versioning system.
Just kidding.........I'm just playing devil's advocate. I agree with you guys on the table stuff.
The only time I deviate is when the information seems too sensitive for tables & my security context doesn't cover it.

That wasn't directed at you Isaac, but the O/P ?

 

[Isaac]

That wasn't directed at you Isaac, but the O/P ?

I should have held my tongue, sorry. I was just being humorous more than anything, sorry

 

[Pat Hartman]

I know Isaac has tried to help by giving you the answer to your specific question. However, he and the other experts who have joined in have all told you that this is a bad idea. You can use a function if you want but, you should not be hardcoding the security settings and userIDs, no matter how few they are.

Proper practice is to designate at least two users with authority to add/change users of the application as well as yourself. Use a table to store information so that the users have a form to use to manage users and you don't have to make code changes to change user permissions. I can't even begin to tell you how bad an idea that is. If you need a sample for security, I can post one but it might not be exactly what you are looking for since it uses a modified version of the Switchboard Items table to control who has what access to which forms. The sample also includes a custom switchboard which you may or may not want to use. If you don't use it, you can still use the standard Access switchboard but you'll need code in all the forms to check security.