Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all,
    Looking for a bit of help with an Access database I'm doing. I have a sort of load screen that is basically just a form with 2 combo boxes, the first combo is called User and has Environ("UserName") as the default value to capture the Users PID when they log on, I then have another combo box called UserLevel that will returm the user lvl based on a query to a list of users I have. So what I want to do is to have diffrent forms load depending on what user level the person logging on is, so for example if UserLevel is 1 then I would want form 1 to load etc, if anyone could help me getting this to work that would be great.

    Cheers

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Your title suggests you want different Switchboad forms to open based on the UserLevel they choose, which may be a several levels for a given user - is that correct? If so, the switchboard isn't really suited for what you are about. If there were only 2 options, you might consider creating a second Switchboard form that uses a second table, but if you need 3 or more, that approach really gets out of hand. (I presume you are familiar with the table used by the Switchboard form and the code that drives it.) If you have multiple UserLevel values, then I would suggest you modify the table to include a UserLevel field that indicates whether that particular form is displayed for that level. That also means you will need to do some pretty extensive modifications to the VBA that is behind the Switchboard form - and that code is more complex that it appears at first glance. If you are proficient with VBA, then proceed. Otherwise, you might want to look for a commercial add-in that gives you the functionallity you are seeking.
    Wendell

  3. #3
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Each user will have one user level(from 1 - 4) assigned, kept on a table with User ID's and the user level for each one. When a user logs on I have a form that runs 2 combo boxes(that are not visible) 1 that capture the user ID and another that returns that users level from the table UserLevels. What I hope to do is have a diffrent form(or switchboard) to open for each level so that some people have access to just case tracking, some have access to case tracking and reports, etc. Hope this makes sense

    Cheers

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Your description does make sense, and you could create 4 different switchboard forms that control what the user sees. However if you use the standard Switchboard that Access will build for you using a wizard, then you have some challenges. If you construct your own "switchboard" forms and use buttons on that form to open various forms and reports, you may find it less complicated. (I assumed from your description that you are not using Access User Security, but are using the Windows login - is that correct?) Is your form hidden and expected to open the appropriate switchbaord form? Or does the user have to do something - like click a button that says Login for example - before the switchboard form is displayed? Also what version of Access are you using? If you are contemplating the standard Access Switchboard form, there are some changes in 2007 that may make using it even more difficult.
    Wendell

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    If you are happy to hack into the standard switchboard code
    and also add some fields to the Switchboard Items Table (you won't be able to use the Switchboard manager though)
    You can fairly easily create a multi use login switchboard.
    I have used this technique with good success on several databases in all versions of Access 2000 through to 2010.
    For even more flexibility, you could convert the standard switchboard menu form by using a Tab Control
    to switch between the different options.

    However, for what you want to achieve, if you are going to use the standard switchboard interface,
    you will need to do some manual coding.
    Andrew

  6. #6
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am using Access User Level Security but have made everyones User ID their PID and that is also their windows profile name so when they log into Access the user name is filled in nand they only have to enter their password. The form I am using is hidden and will capture the User Id when they log on(I used environ("UserName") for this and seems to work ok) then will run a query on update of this combo box that returns the user level from a table. I want this to run automatically when a user logs on so that once they enter their User Name and Password they will be taken to the appropriate form/swirchboard. I'm not using the standard Access switchboards as prefer just to use a from with command buttons. I'm using Access 2003 for this and I'm looking for a bit of help with the coding for this, or to be pointed in the right direction anyway. I'm pretty new to Access and my background is really just excel and word but I'm kind of getting the hang of some of the VBA.

    Cheers guys and any more help would be much appreciated

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Sorry about missing the fact you are using Access 2003 - it's clearly stated in the thread title. Since you are using your own switchboard forms, the code to do this sort of thing is reasonably straightforward. I assumed in the following code that you have a table with UserName and UserLevel where you run the query - I used a DLookup since the table will be small - and is running on the OnLoad event for your hidden form:
    Code:
    Private Sub MyForm_Load()
    
        Dim UserLevel As Integer
        
        UserLevel = DLookup("UserLevel", "tblUsers", CurrentUser())
        Select Case UserLevel
            Case 1
                DoCmd.OpenForm "SwitchBoard1"
            Case 2
                DoCmd.OpenForm "SwitchBoard2"
            Case 3
                DoCmd.OpenForm "SwitchBoard3"
            Case 4
                DoCmd.OpenForm "SwitchBoard4"
            Case Else
                MsgBox "Invalid Login attempt - please try again!"
        End Select
        DoCmd.Close
        
    End Sub
    I presume you have no reason for the hidden form to remain open, so the last line closes the form. Also I used the CurrentUser() function to retrieve the UserName - your Environ("UserName") should work as well, although you may need to put it outside the DLookup() function and assign it to a variable. Finally I added a bit to deal with the odd case where a value gets returned outside the expected range - you might want something more sophisticated and a Message Box in that case.

    Finally, this is air code, and has not been tested, so you may encounter issues I didn't anticipate. Hope it helps.
    Wendell

  8. #8
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you so much, that's exactly what I was looking for, your a Star!

  9. #9
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry I thought I had this working but I think I just had some of the forms open in the background.

    So I have a table called UserLevelTbl and in that I have to fields PID(the user name) and UserLevel(just 1, 2, 3 or 4) so what I had was pretty much the same as yours apart from

    UserLevel=DLookup("UserLevel", "UserLevelTbl", "PID=CurrentUser()")

    but whenI run this it just loads up the blank form that has the code and not any switchboards. Should I declare the currentuser() as well, something like

    dim UserL as string

    UserL = currentuser()

    and then use

    "PID = UserL" ?

    Sorry to be a pain with this

    Cheers

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    If your field name for the UserName is PID then your statement should be:

    UserLevel=DLookup("[UserLevel]", "UserLevelTbl", "[PID] =" & CurrentUser())

    There is no need separately declare a variable, although you can if it makes it easier to debug. The real trick is the concatenation of the CurrentUser() returned value to the string for the criteria. Apologies for air code that wasn't correct.
    Wendell

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Coming late to this tread, where I can see that you have solved your problem, I just want to add that there is an alternative approach.
    As I see it, the problem with showing different versions of the Menu form to different people is that you need to maintain the different versions. Whether that is much of a problem depends on whether the application is still evolving.

    The alternative is to show/hide components of the one form, depending on the user level.

    In the onload event of the form, you can set the Visible property of various Command buttons, or Tab pages to false, depending on the User Level.
    Regards
    John



  12. #12
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok so I must be doing something wrong here but don't know what. When ever I try to run this the blank form that has this code just comes up and it doesn't switch to any of the other forms I want it too. I don't get any error message or asked to de-bug like I normally would if the coding was wrong so hopefully one of you nice, helpfull people can take a look and maybe tell me what I'm doing wrong?

    First off I have a table called UserLevelTbl with 4 fields, Ref, Name, PID and Userlevel. The 2 fields I'll be using for this are PID(7 digit number used as Name at log in) and Userlever(either 1, 2, 3 or 4) and what I have so far is

    Private Sub Form_Load()

    Dim Userlevel As Integer

    Userlevel = DLookup("[Userlevel]", "UserLevelTbl", "[PID] =" & CurrentUser())
    Select Case Userlevel
    Case "1"
    DoCmd.OpenForm "SB1"
    Case "2"
    DoCmd.OpenForm "SB2"
    Case "3"
    DoCmd.OpenForm "SB3"
    Case "4"
    DoCmd.OpenForm "Switchboard"
    Case Else
    MsgBox "Invalid Login attempt - please try again!"
    End Select
    DoCmd.Close


    End Sub


    With SB1, SB2, SB3 and Switchboard as the 4 test forms. Can anyone point out where I'm going wrong with this?

    Cheers

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    UserLevel is an Integer

    So your Case Statements do not need double quotes. "1"

    Case 1

    Case 2

    etc
    Regards
    John



  14. #14
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok I've taken those out but still get the same result

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Try this

    Code:
    Userlevel = DLookup("[Userlevel]", "UserLevelTbl", "[PID] =" & CurrentUser())
    Msgbox "The user is :" & currentUser()
    MsgBox "The user level is: " & UserLevel
    You need to find out whether the UserLevel is being correctly determined.

    Also perhaps rename the UserLevel variable so it is not the same as the field UserLevel.

    Code:
    Dim intUserlevel As Integer
     
     intUserlevel = DLookup("[Userlevel]", "UserLevelTbl", "[PID] =" & CurrentUser())
    Msgbox "The user is :" & currentUser()
    MsgBox "The user level is: " & intUserLevel
     Select Case intUserlevel
    Regards
    John



Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •