Results 1 to 10 of 10
  1. #1
    billmark is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    13

    Access login

    Hi


    I've created a login form for the users to login to my program. However, I want to limit only one user at a time (I know MS Access is for multi-users and some suggest using split database which I am not familiar as I am new to access program).

    Please advise if it is possible and how:
    1.when the program is in use and another user tries to login by clicking the icon, then the login form prompts the message 'User - name of the user is in use'
    2.Exit the program.


    Thanks.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What, if any, data are you storing from the login form?
    Is there at least one field in a login record that cannot be Null, thus reliably indicates someone has logged in, AND the record is removed upon exit?
    If so, all you need to do if limiting to one user is DCount that field in that table and close the db if it returns a value.
    HOWEVER, if an abnormal shutdown occurs and leaves that record intact, no one is getting in unless you've got the ability to bypass any startup code and manually remove that record, or you allow anyone with a custom setting in their user profile (such as Admin) whereby you can allow Admins in regardless of how many are logged in.

    There are other ways, such as looking for the locked db file, but I have found that networks can take a long time to purge these. I've had to get network admins to delete that file even when no one was in the db for quite some time, so I don't consider that foolproof. It's easier to guide you through a logged in count.

    There important benefits to splitting a db. You should research and learn how, if not for this project, then in the very near future.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Maybe try to check locking file as Micron suggested:
    If Len(Dir("T:\YourFolder\YourDB.laccdb")) = 0 Then
    Msgbox "This file does NOT exist, let user in."
    Else
    Msgbox "This file does exist, keep user out"
    End If

    Another way which is risky is to have a linked table with a field called say Opened. When a user logs in, check to see if that value is 0, if it is change it to 1 and let the user in. When user logs out change it back to 0. If it is 1 already, then someone else has it open so do not allow them in. Just need to remember to change it to 0 when user exits program or it can get stuck in Open status. I would not recommend this but is one idea. Maybe put in an admin ID that would bypass this to always let you in so if it does get stuck you can change it to 0 or something.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Or maybe just have everyone use a shortcut with a switch to open exclusively? Then there is the Options > Advanced > Advanced section to open exclusively.
    I'm not sure if that options works/doesn't for a share/split db. You could try setting it to Exclusive, close, reopen and see if anyone else can get in at that time.
    I forgot about that option because I've never used it.

  5. #5
    billmark is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    13
    Hi
    Thank for the suggestion.

    I tried both methods but the users can still access.

    For the locked db, do I need to set in access, Options > Client Setting > Advanced > Default open mode as Exclusive

    For the code provided above, message is always prompted (This file does not exist, keep user out) even the first user login
    If Len(Dir("T:\YourFolder\YourDB.laccdb")) = 0 Then
    Msgbox "This file does NOT exist, let user in."
    Else
    Msgbox "This file does exist, keep user out"
    End If

    Here is my code in the login form and please advise how to fix and also how to show the user name or id who is using the program.
    QUOTE
    Private Sub Command1_Click()
    Dim UserLevel As String
    Dim TempPass As String
    Dim ID As Integer

    If IsNull(Me.txtLoginID) Then
    MsgBox "Please enter LoginID", vbInformation, "LoginID Required"
    Me.txtLoginID.SetFocus
    ElseIf IsNull(Me.txtpassword) Then
    MsgBox "Please enter Password", vbInformation, "Password Required"
    Me.txtpassword.SetFocus
    Else
    'Process the job
    If (IsNull(DLookup("[Userlogin]", "tblUser", "[Userlogin] ='" & Me.txtLoginID.Value & "' And Password = '" & Me.txtpassword.Value & "'"))) Then
    MsgBox "Incorrect LoginID or password"
    Else
    UserLevel = DLookup("UserSecurity", "tblUser", "Userlogin ='" & Me.txtLoginID.Value & "'")
    TempPass = DLookup("password", "tblUser", "Userlogin ='" & Me.txtLoginID.Value & "'")
    ID = DLookup("UserID", "tblUser", "Userlogin ='" & Me.txtLoginID.Value & "'")
    DoCmd.Close

    If UserLevel = "user" Then

    DoCmd.OpenForm "frmRunReport"
    Else
    DoCmd.OpenForm "MainForm"
    End If
    End If
    End If


    End Sub
    UNQUOTE

    Thanks

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    This should work. Make sure you have the path spelled right. If it finds the file it will give message and then close the db. Put this code in the OnOpen of your first Login Form.
    "C:\ABC\Database6.laccdb" <-- Change this in the code below to be the path to your database locking file when the database is open.

    If Len(Dir("C:\ABC\Database6.laccdb")) > 0 Then
    MsgBox "Another user is in the System. Please check back later."
    DoCmd.Quit
    End If

  7. #7
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Yah, you were supposed to use your own path, not T:\YourFolder\YourDB.laccdb, and yes you set the exclusive mode in Options, though the route to it is different in my older version. A couple of issues, maybe:
    - if this txtLoginID is a number the quotes won't work.
    - you cannot lookup two fields with one DLookup. Either rewrite as two, or run a sql statement or query and check if the result is 0 records or not
    Sorry, out of time for today.

  8. #8
    billmark is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    13
    Hi
    You are right the txtLoginID is a number and thus it doesn't work. Also, the message prompts every time when login. Please advise how to rewrite as two or sql.

    Thanks.

  9. #9
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Not sure which way you want to do it. If you want to try the locking file check, again place this in the OnOpen of the Login Form. It should check for the file and if found, put up message to user that DB is open, when they hit Ok it will close the Database. Again change that sample path and file name below to your info.

    If Len(Dir("C:\ABC\Database6.laccdb")) > 0 Then
    MsgBox "Another user is in the System. Please check back later."
    DoCmd.Quit
    End If

    If the login is numeric I think the syntax would be:

    If (IsNull(DLookup("[Userlogin]", "tblUser", "[Userlogin] =" & Me.txtLoginID & " And Password = '" & Me.txtpassword.Value & "'"))) Then
    MsgBox "Incorrect LoginID or password"
    Else
    UserLevel = DLookup("UserSecurity", "tblUser", "Userlogin =" & Me.txtLoginID)
    TempPass = DLookup("password", "tblUser", "Userlogin =" & Me.txtLoginID)
    ID = DLookup("UserID", "tblUser", "Userlogin =" & Me.txtLoginID)

  10. #10
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I can't tell you how to get the user name because I don't even know if you're storing it, let alone where it is. You could DLookup the name I guess. You'll notice in the revised code that I've assigned the level and temp pass to your variables first, mainly because you were looking them up twice. Look them up once, assign to variables then reference the variables as many times as necessary. I did nothing about preventing logging in if there was anyone else in already since it's not clear you wanted that, and I don't know where you're at with the exclusive option thing. Watch out for integer vs longs with user id's such as empl numbers if you ever use those. I've seen where the employee id value exceeded the capacity of the integer data type.
    NOTE: I don't get what you are doing with stuff like assigning a lookup on login to a user level and then again to a usersecurity lookup. See in between the ****.
    I think you'll be able to get and work with the user name using what follows as an example.
    Code:
    Private Sub Command1_Click()
    Dim UserLevel As String
    Dim TempPass As String
    Dim ID As Integer
    
    'get user id and password
    If IsNull(Me.txtLoginID) Then
      MsgBox "Please enter LoginID", vbInformation, "LoginID Required"
      Me.txtLoginID.SetFocus
      Exit Sub
    End If
    
    If IsNull(Me.txtpassword) Then
      MsgBox "Please enter Password", vbInformation, "Password Required"
      Me.txtpassword.SetFocus
      Exit Sub
    End If
    
    'validate id and password
    UserLevel = DLookup("[Userlogin]", "tblUser", "[Userlogin] =" & Me.txtLoginID)
    TempPass = (DLookup("[Password]", "tblUser","[Password] = '" & Me.txtpassword & "'")
    If IsNull(UserLevel) OR IsNull(TempPass) Then
     MsgBox "Incorrect LoginID or password"
     Exit Sub
    End If
    
    '****Process the job
    UserLevel = DLookup("UserSecurity", "tblUser", "Userlogin ='" & Me.txtLoginID.Value & "'")
    TempPass = DLookup("password", "tblUser", "Userlogin ='" & Me.txtLoginID.Value & "'")
    ID = DLookup("UserID", "tblUser", "Userlogin ='" & Me.txtLoginID.Value & "'")
    DoCmd.Close
    '****
    If UserLevel = "user" Then
     DoCmd.OpenForm "frmRunReport"
    Else
     DoCmd.OpenForm "MainForm"
    End If
    
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. MS Access Login Form
    By nescb in forum Access
    Replies: 2
    Last Post: 04-19-2016, 09:30 AM
  2. Login access
    By princess12 in forum Access
    Replies: 7
    Last Post: 04-20-2015, 12:57 PM
  3. Replies: 2
    Last Post: 04-17-2015, 10:59 AM
  4. Access Login
    By mithu 1992 in forum Access
    Replies: 1
    Last Post: 10-02-2013, 03:24 PM
  5. Access login form help
    By Tempuser in forum Forms
    Replies: 8
    Last Post: 09-10-2009, 06:55 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums