Different switchboard displayed depending on user.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been given a database (it was created long before I arrived at the company, I am using Access 2000) that displays one switchboard when launched. There are eight functions listed on the switchboard. I have been asked split up the functions, so that team leads and above can access all eight functions, but regular users can only access one

I think this requires a module, either to retrieve the user's Windows login, or to display a message box requiring the person to login to determine which switchboard he/she sees. However, I have zero experience with VBA or building modules. I read the threads about "Startup with Conditional Views per User" and "Using Windows logon", and what they want to do makes sense, I just have no idea how to do this. Help?
 
Build a table called tblUserForms, with two text fields, fldUser and fldForm
(holding the form name for each user), where you use the users' Windows
Logon name in field fldUser.
Open a new module in design mode, and paste in the following code:

Function Launch_User_Form()
Dim frm As String
frm = DLookup("[fldForm]","tblUserForms","[fldUser] = ' " &
Environ("UserName") & " ' "
DoCmd.OpenForm frm
End Function

Save the module (the name is not important), and them open a new macro in
design mode, add a single action RunCode, with argument Launch_User_Form()
(the name of the function, don't omit the brackets at the end) and save it
with the name Autoexec.
Each time the database is opened the Autoexec macro runs, call the function
in the module, the Dlookup function returns the name of the form associated
with the current user (as ientified by the Environ("UserName") function
which returns the Windows logon name) and the pertinent form is opened. The
beauty of it is the user doesn't have to log in, and doesn't even realize
he/she is getting a user-dependent form.

HTH,
Nikos


MCB said:
I have been given a database (it was created long before I arrived at the
Click to expand...
company, I am using Access 2000) that displays one switchboard when
launched. There are eight functions listed on the switchboard. I have been
asked split up the functions, so that team leads and above can access all
eight functions, but regular users can only access one.
I think this requires a module, either to retrieve the user's Windows
Click to expand...
login, or to display a message box requiring the person to login to
determine which switchboard he/she sees. However, I have zero experience
with VBA or building modules. I read the threads about "Startup with
Conditional Views per User" and "Using Windows logon", and what they want to
do makes sense, I just have no idea how to do this. Help?
 
The problem is not Access, the problem is word wrap in the newsgroup post!!!

The line you saw next ( Environ("UserName") & " ' " ) actually belongs at
the end of the previous one, the one that is now producing the syntax error.

Hope it works now,

Nikos

MCB said:
Ok, created the second menu, and then followed those instructions, and
Click to expand...
populated tblUserForms with some test data (jsmith, UserSwitchboard /
bjones, LeadSwitchboard, etc., plus one that's my username), however, when I
re-launch the database, I get "Compile Error: Syntax Error" pointing to this
line: frm = DLookup("[fldForm]","tblUserForms","[fldUser] = ' " &
 
Umm... now it's one line, beginning with "frm = DLookup" and ending with "Environ("UserName") & " ' ")" and the syntax error has gone away, but it's been replaced with an invalid use of null error. Argh. (I really do appreciate your help, though.

Function Launch_User_Form(
Dim frm As Strin
frm = DLookup("[fldForm]", "tblUserForms", "[fldUser] = ' " & Environ("UserName") & " ' "
DoCmd.OpenForm fr
End Function
 
What does the error mesage refer to? To identify it, change the code to:

Function Launch_User_Form()
Dim frm As String, usernam As String
usernam = Environ("UserName")
frm = DLookup("[fldForm]", "tblUserForms", "[fldUser] = ' " & usernam & " '
")
DoCmd.OpenForm frm
End Function

Then place the cursor somewhere in the function and execute step by step (by
hitting F8). Once the highlight reaches the DoCmd.OpenForm statement (which
means the previous one has executed), place and leave the cursor over the
usernam variable. If it shows the current username as a value then that part
is OK (I don't expect a problem here). Do the same with the frm variable. It
should show a form's name if everything OK, but my guess is you'll see a
"frm = Null". If this is the case, then it's because there is no record in
tblUserForms for the current user (typo?) so the DLookup returns a null
value.

HTH,
Nikos


MCB said:
Umm... now it's one line, beginning with "frm = DLookup" and ending with
Click to expand...
"Environ("UserName") & " ' ")" and the syntax error has gone away, but it's
been replaced with an invalid use of null error. Argh. (I really do
appreciate your help, though.)
Function Launch_User_Form()
Dim frm As String
frm = DLookup("[fldForm]", "tblUserForms", "[fldUser] = ' " & Environ("UserName") & " ' ")
DoCmd.OpenForm frm
End Function
Click to expand...
 
That's really funny, the only possible explanation I can think of is there
is nothing in the form name field for user "myname" in the table.

MCB said:
usernam = "myname", so that's fine, since I'm logged in on my own PC.

frm = "", so yeah, it's not finding what it's looking for. However, my
Click to expand...
username is, indeed, in tblUserForms - it was the first username I entered,
and there's no typo. I even tried deleting all the others except that one,
no change in the error.
****************
What does the error mesage refer to? To identify it, change the code to:

Function Launch_User_Form()
Dim frm As String, usernam As String
usernam = Environ("UserName")
frm = DLookup("[fldForm]", "tblUserForms", "[fldUser] = ' " & usernam & " '
")
DoCmd.OpenForm frm
End Function

Then place the cursor somewhere in the function and execute step by step (by
hitting F8). Once the highlight reaches the DoCmd.OpenForm statement (which
means the previous one has executed), place and leave the cursor over the
usernam variable. If it shows the current username as a value then that part
is OK (I don't expect a problem here). Do the same with the frm variable. It
should show a form's name if everything OK, but my guess is you'll see a
"frm = Null". If this is the case, then it's because there is no record in
tblUserForms for the current user (typo?) so the DLookup returns a null
value.
Click to expand...
 
Thanks for your attempts to help. I am going to try setting things up a totally different way. Wish me luck! :)
 
OK then, good luck, but my offer still stands.

Nikos

MCB said:
Thanks for your attempts to help. I am going to try setting things up a
Click to expand...
totally different way. Wish me luck! :)
 
I realize this is crazy old, but just in case someone else finds this I did find the solution using the 2007-2010 version. Issue was extra spaces in this part of the code...

Original Post: "[fldUser] = ' " & Environ("UserName") & " ' "
Correction: "[fldUser] = '" & Environ("UserName") & "'")
 
Back
Top