Autoexec Question (1 Viewer)

[belly0fdesire]

I'm still new at this, so bear with me, please...

I want the database to open to different forms for different users.

I have a table that's called "users" with 3 fields: RealName, UsNm and Level.

I want the Level to be what determines the formt that's opened, based on the username they are logged on with. I got it to figure out what the user's username is, but it just opens the same form for every user.

fOSUserName() is the username the user is logged on with, then I have 2 more functions that I'm trying to use to make this happen...

Here's one of the functions:

Public Function lookuplevel2()
lookuplevel2 = DLookup("[Level]", "users", "[UsNm] = fOSUserName()")
End Function

And here's the other, rather ghetto function that I'm using:

Public Function starter()
Dim stdocument1 As String
Dim lvl As String
lvl = lookuplevel2()
stdocument1 = "mainman"
stdocument2 = "mainarm"
stdocument3 = "mainstaff"
If lvl = "3" Then GoTo man
If lvl = "2" Then GoTo rev
If lvl = "1" Then GoTo staf
man:
DoCmd.OpenForm stdocument1
GoTo enditall
rev:
DoCmd.OpenForm stdocument2
GoTo enditall
staf:
DoCmd.OpenForm stdocument3
GoTo enditall
e:
End Function

Then I put "Run Code" in the Autoexec macro and Starter() as the code to run.

Can someone help me with this?

 

[FoFa]

I think this might be the problem:
lookuplevel2 = DLookup("[Level]", "users", "[UsNm] = fOSUserName()")
Try this instead (assuming it returns a string):
lookuplevel2 = DLookup("[Level]", "users", "[UsNm] = " & fOSUserName())

 

[Bodisathva]

lookuplevel2 = DLookup("[Level]", "users", "[UsNm] = '" & fOSUserName() & "'")

if fOSUserName is a string, you'll also need the single quotes.

also, your function doesn't return anything, lookuplevel2 must be defined as Public Function lookuplevel2() as String in order to return a string value

(if you change it to Integer, you can clean up the entire second function to:

Public Function starter()
    Dim docName As String
    Select Case lookuplevel2
           Case 1
                docName = "mainstaff"
           Case 2
                docName = "mainarm"
           Case 3
                docName = "mainman"
    End Select
    DoCmd.OpenForm docName
End Function