String concatenation issue

[nosferatu26]

Hello,

I have a list of different user ids that are admins for my database. I am trying to write code to validate a button so only amins can successfully enter a form. the way I have them defined is:

dim admin1 as string
dim admin2 as string
dim admin3 as string

and so on..

I was wondering if it is possible to cycle through these using a for loop instead of comparing each admin id in a really long if statement.

for example, what I currently have is:

For i = 1 To 4
        nextAdmin = "admin" & i
        If user = nextAdmin Then
            validateUser = True
            End
        End If
Next I

on the first iteration, I assign nextAdmin "admin1" but I want it to actually be assigned the reference admin1 points to, instead of the string itself. I just want to cycle through each admin by simply appending the correct number to the end of "admin". I'm new to VBA so I was wondering if there was a way to get this to work without the long if/or statement.

Any help would be greatly appreciated!

 

[Ranman256]

you are assigning nextAdmin to "admin1", are you saying the user is not that?
what is user = to? "bob1"?

 

[Galaxiom]

Use an array for the admin names and refer to them as Admin(i).

 

[pbaldy]

How are you populating the variables? It looks like you're trying to validate the user against a table, in which case I'd do it directly, via a recordset or DCount().

 

[Cronk]

I would have expected you would have the admin user names in a table. Why not just look up the particular user to see if their username is in the table?

Normally in setting access levels to different forms, you would have a table of all users with Yes/No fields for storing access levels such as Read/write data, add to lookup tables etc.

 

[nosferatu26]

Thanks for all of the responses! I actually thought it would be easiest to add a field "Admin" to my "Names" table and use a recordset to iterate through all of the ID's in the table who are also an admin. I appreciate all the help. Here's the implementation, if anyone is interested:

Function validateUser() As Boolean
    Dim strSQL As String
    Dim rst_admins As Recordset
    Dim user As String 'user to validate
    user = GetUserName
   
    'create record set
    strSQL = "SELECT Employee_ID FROM [tbl_Names] WHERE Admin = Yes;"
    Set db = CurrentDb()
    Set rst_admins = db.OpenRecordset(strSQL, dbOpenDynaset)
    
    With rst_admins 'iterate through list of admin ID's
        While Not .EOF
            If user = rst_admin.Fields("Employee_ID") Then
                validateUser = True
                End
            End If
            .MoveNext
        Wend
    End With
   
   'user id doesnt match any admin id
   validateUser = False
End Function

 

[pbaldy]

I would add the user to the criteria and test for EOF. Less efficient to loop the names, though if the table is small you probably won't see a difference.

 

[nosferatu26]

I would add the user to the criteria and test for EOF.

Could you elaborate on this? what would the code look like then?

For now, there wont be more than 10 admins so I think itll work how it is. But I would like to know out of curiosity, and for future reference if possible. I'm always interested in making my code more efficient.

Thanks!

 

[Cronk]

An even simpler way is to use Dlookup

Function validateUser() As Boolean
    
    Dim user As String 'user to validate
    user = GetUserName
    
    validateUser = (nz(dlookup("Employee_ID","[tbl_Names]","Admin = Yes AND Employee_ID='" & user & "'") )= user)
   
 End Function

Incidentally, is the user name actually stored in the field Employee_ID, or are you mixing up the fields

That is, in your code
user = rst_admins.Fields("Employee_ID")

 

[MarkK]

An even simpler way is use DCount()...

Function UserIsAdmin() As Boolean
    UserIsAdmin = DCount("*","tbl_Names","Employee_ID = '" & GetUserName & "' AND Admin")
End Function

 

[pbaldy]

For the record, it would look something like this, presuming user is text:

strSQL = "SELECT Employee_ID FROM [tbl_Names] WHERE Admin = Yes AND Employee_ID = '" & user & "'"

 

[NauticalGent]

Nosferatu26,

I have a similar need but mine is a little more complex. I have multiple forms where I need certain controls enabled/disabled or even invisible/visible depending on the user's job title.

To do this, I have a public function that looks up the users job title and returns a True or False depending on the parameters of the sub that called it.

Here is the Function code:

Public Function GetJobTitle(strJobTitle As String)
    Dim blnResp As Boolean
    Dim strDBA As String
    Dim strResp As String
    Dim strUser As String
    
    strUser = fosUserName 'this code can be found on the forum
    strDBA = "DBA"
    strResp = Nz(DLookup("[Responsibilities]", "tblUsers", "[UserName] = '" & strUser & "'"))
    
    Select Case strResp
        Case strJobTile 'this is the argument from the sub that called this function
            blnResp = True
        Case strDBA
            blnResp = True
        Case Else
            blnResp = False
        End Select
    
    GetJobTitle = blnResp
End Function

Here is an example of a sub that calls it:

Private Sub Form_Current()
On Error GoTo err_handler
    
    Dim blnResp As Boolean
    Dim ctlCurr As Control
    
    blnResp = GetJobTitle("OPS Center")
    
    ' This makes any control in the form with a Tag of 'OPS_Cen" invisible to anyone who is
    ' not designated as an OPS Center member in the tblUsers table
    If blnResp = False Then
        For Each ctlCurr In Me.Controls
            If ctlCurr.Tag = "OPS_Cen" Then
                ctlCurr.Visible = False
            End If
        Next ctlCurr
    End If

exit_handler: ' Courtesy of Pbaldy!
    Exit Sub

err_handler:
    MsgBox Err.Number & "-" & Err.Description
    Resume exit_handler
End Sub

This may be a solution to what you are trying to do. If any subscribed to this thread has any recommendations as to how to improve this, I am all ears.

I need to make this where I can have multiple and/or optional arguments but I haven't been able to dedicate some quality time to it yet.

Feel free to step in and pinch-hit for me!