Multi user login screen - question about tracking logged in users

**NeoPa** · Nov 7 '12, 01:28 AM

Well, ignoring declarations of the variables and the procedures which I don't expect you'll need, the breakdown is as follows :

Line #7 is the basic structure of the WHERE clause that is applied when searching through the table. We need to check that both the Initials and tyhe password match.
Line #8 replaces %I in the string with the actual value from your form control.
Line #9 replaces %P in the string with the actual value from your form control for Password after it's been through the mangler of Encrypt().
Line #10 checks in the table for a record that matches what the user has entered. If there is no match then the return will be Null.
Lines #11 through #13 only execute if a matching record was found. This is fundamentally your code anyway, so no further explanation required.
Line #18 returns whatever was passed as a parameter as the return value of Encrypt(). In your case that's probably not a great idea, but worrying about the details of your encryption algorithm is not part of the answer.

I hope that helps.

**anewuser** · Nov 7 '12, 08:38 AM

Hi, sorry I can't get it to open the relevant forms this is what I am working with please help.

Code:

Option Compare Database
Option Explicit
  
Private Sub Login_Click()
    Dim strWhere As String, strPassword As String
  
    strWhere = "([UserInitials]='%I') AND ([Userpassword]='%P')"
    strWhere = Replace(strWhere, "%I", Me.UserName)
    strWhere = Replace(strWhere, "%P", Me.Password)
    If Not IsNull(DLookup("UserInitials", "tbl_User", strWhere & "Regular" = False & "Admin" = False)) Then
        DoCmd.Close
        MsgBox "Welcome"
        DoCmd.OpenForm "Staff1"
    ElseIf Not IsNull(DLookup("UserInitials", "tbl_User", strWhere & "Regular" = True)) Then
        DoCmd.Close
        DoCmd.OpenForm "Staff2"
    ElseIf Not IsNull(DLookup("UserInitials", "tbl_User", strWhere & "Admin" = True)) Then
        MsgBox "Please use caution when changing the conditions of tables and queries"
        DoCmd.Close
        DoCmd.OpenForm "Manager1"
    End If
 CurrentDb.Execute "UPDATE usyscurrentuser set CurrentUser=UserName"
End Sub

**NeoPa** · Nov 7 '12, 05:54 PM

Normally, I would suggest using Recordset processing for multiple returned values, but let's walk first. In this scenario I return both values ([Regular] and [Admin]) in a string as long as a record is found. If no record is found the returned value is Null. Otherwise, there will be two numbers stored in the returned value as a string. 0 represents FALSE and -1 represents TRUE.

Code:

Option Compare Database
Option Explicit

Private Sub Login_Click()
    Dim strSQL As String, strForm As String, strMsg As String
    Dim dbVar As DAO.Database

    strSQL = "([UserInitials]='%I') AND ([Userpassword]='%P')"
    strSQL = Replace(strSQL, "%I", Me.UserName)
    strSQL = Replace(strSQL, "%P", Me.Password)
    Select Case Nz(DLookup("[Regular] & [Admin]", "tbl_User", strSQL), "")
    Case ""                 'No matching record found
        Call MsgBox("Please re-enter UserName and Password")
        Exit Sub
    Case "00"               'Both FALSE
        strMsg = "Welcome"
        strForm = "Staff1"
    Case "-10", "-1-1"      'Regular TRUE; Admin Either
        strMsg = ""
        strForm = "Staff2"
    Case "0-1"              'Admin TRUE; Regular FALSE
        strMsg = "Please use caution when changing the conditions of " & _
                 "tables and queries."
        strForm = "Manager1"
    End Select
    Set dbVar = CurrentDb()
    strSQL = Replace("UPDATE [uSysCurrentUser] SET [CurrentUser]='%N'", _
                     "%N", Me.UserName)
    Call dbVar.Execute(strSQL, dbFailOnError)
    Call DoCmd.Close
    If strMsg > "" Then Call MsgBox(strMsg)
    Call DoCmd.OpenForm(strForm)
End Sub

PS. I have no idea where uSysCurrentUser comes from. If it's a system table (I don't have it in my databases running under Access 2003.) then lose lines #6 and #25 through #28 and don't even think about updating system tables in your code. If it's not a system table then the name starting with "uSys" is only going to confuse. You and anyone else trying to work with that code.

**anewuser** · Nov 9 '12, 01:33 PM

Hi, I am trying to work with the code given in the previous post, however, I can't seem to get it to work. It doesn't return an error when I click login but it doesn't do anything else either. I have tried to amend it in a number of ways most notably of which is shown below.

Code:

Option Compare Database
Option Explicit
  
Private Sub Login_Click()
    Dim strSQL As String, strCase As String
    Dim dbVar As DAO.Database
  
    strSQL = "([UserInitials]='%I') AND ([Userpassword]='%P')"
    strSQL = Replace(strSQL, "%I", Me.UserName)
    strSQL = Replace(strSQL, "%P", Me.Password)
    strCase = DLookup("[Regular] & [Admin]", "tbl_User", strSQL)
    If strCase = Null Then                  'No matching record found
        MsgBox "Please re-enter username and password"
        Exit Sub
    ElseIf strCase = "00" Then              'Both FALSE
        DoCmd.Close
        MsgBox "Welcome"
        DoCmd.OpenForm "Staff1"
    ElseIf strCase = "-10" Then             'Regular TRUE; Admin False
        DoCmd.Close
        MsgBox = "Welcome"
        DoCmd.OpenForm "Staff2"
    ElseIf strCase = "0-1" Or "-1-1" Then   'Admin TRUE; Regular Either
        DoCmd.Close
        MsgBox "Please use caution when changing the conditions of " & _
                 "tables and queries."
        DoCmd.OpenForm "Manager1"
    End If
    Set dbVar = CurrentDb()
    strSQL = Replace("UPDATE [usyscurrentuser] SET [CurrentUser]='%N'", _
                     "%N", Me.UserName)
    Call dbVar.Execute(strSQL, dbFailOnError)
    End Sub

I don't know if there is anyway that this code can work or if a recordset would work better. But I can't think of another way of getting it to work.

Thanks for all of your help so far it is much appreciated and just for your information usyscurrentuser was a name of a table suggested earlier in this thread which would be used in a function to track which users are added, editing or deleting table data. I have created it based on previous advice for this reason.

**NeoPa** · Nov 9 '12, 02:33 PM

I have little idea why you might have wanted to revert to the If...Then...Els eIf etc structure from the Select Case one already provided, but assuming that's how you want to do your code then you should be looking to check for Null by using :

Code:

If IsNull(strCase) Then

If you do that you should get the message you want.

To do it properly, from my point of view, see the earlier post which I've now updated to fix my oversight by adding a Case Else line to handle the situation where no matching record was found (I should have known better than to try to compare a value with Nul).

**anewuser** · Nov 9 '12, 03:28 PM

I did not mean to cause any offence going back to If...Then...Els eIf. I just wanted to try to fix the problem myself before asking for more help (and that was the only way I knew how). Sorry. For the record I have no preference.

In any case it still isn't working when I click the login button nothing happens...

**NeoPa** · Nov 9 '12, 07:52 PM

It's not a question of offending per se. I was just confused by your changing what I'd already provided for you. If I see you taking a course that seems ill-advised then I, as an advisor in this situation, feel bound to bring that to your attention. In effect, your reason was a sound one anyway, but it may have made better sense to return to where we both were before posting it. That way we can continue to focus on the actual problem and not get side-tracked by other changes that can only be a distraction.

DLookup() doesn't return a String value, but rather a Variant which is generally of type String. When no matching record is found then the result is Null. Null is not a String value and, as such, cannot be compared to other string values. I can only assume that my suggested code (I'm not able to test it so I have to do it all in my head) is still not handling the Null value adequately. I should have used Nz() in line #11, and I will proceed to do so now. BTW Line #11 from your post #19 will crash if no record is found as String variables cannot be assigned the value Null.

**TheSmileyCoder** · Nov 9 '12, 08:09 PM

Just for info:
If you prefix a table with usys it automatically becomes hidden and is considered a developer implemented system table.

**NeoPa** · Nov 9 '12, 08:59 PM

Well. I didn't know that Smiley. Very interesting indeed. I'm already thinking about where i've used normal tables that could be better handled by such tables. Thanks :-)

**anewuser** · Nov 12 '12, 08:51 AM

Hi, the amended code still isn't working. I don't know there is any more information that you need or if there is anything that I can do to test the current code other than trial and error (which I am currently doing).

For what it's worth I have just tried all of the usernames and passwords currently set up in tbl_User each one tests the different cases and one which is not in the table and each time nothing happens.

**zmbd** · Nov 12 '12, 02:17 PM

From the other posts, it sounds like there have been quite a few modifications since the last posting in thread at #19.
Please post your current code.

What I would do is place a STOP command after the line strCase = DLookup("[Regular] & [Admin]", "tbl_User", strSQL) (based on #19's code block)
Compile and save.
Start the process to log-in.
Make sure you use a valid username/password
when the parser hits the STOP you will enter break/debug mode...
<Ctrl-G>
In the immedate window:
?strSQL
Please post the resolved string. Most errors when returning values such as this involve a malformed string.

Continue to step thru your code using [F8] and note what branch in the conditionals is being taken... second most common error is a malformed logic statement.

when you step thru: strSQL = Replace("UPDATE [usyscurrentuser] SET [CurrentUser]='%N'", "%N", Me.UserName)
Once again please obtain the string as before In the immedate window: ?strSQL.

Repeat this for a valid-user/wrong-pw
Repeat for invalid-user/wrong-pw
Repeat for invalid-user/valid-pw

Post all of the strings along with the updated code.

**NeoPa** · Nov 12 '12, 02:26 PM

The most important information we need at this juncture is an explanation of what "nothing happens" means. No messages seen? No data updated? No form opened? Please be as clear as you can.

Originally posted by Zmbd

Zmbd:
Please post the resolved string. Most errors when returning values such as this involve a malformed string.

That's my code you're talking about Z! Surely you don't suspect it of creating a malformed string :-D

More seriously, the instructions Z gives are very worthwhile following (Even the ones about malformed strings. Of course it's possible for bugs to get in there). Nevertheless, it is also important to have a clear idea of exactly what is meant by something not working, so that info too, would be helpful.

**zmbd** · Nov 12 '12, 03:10 PM

NeoPa: That's my code you're talking about Z! Surely you don't suspect it of creating a malformed string :-D

From you never, from the Gremlins that play with the datatables... always! Especially those elusive twin Gremlins of Null-n-Empty - nasty little beasties, yessssss my precious.... nasty little beasties they are!

(ROTFL)

oh.. how to get back on topic... sigh... OH Yes... I'm looking for something that is letting one of the Gremlins muck up the string or for the logic to fail which is why the request for the latest version of the code.

**anewuser** · Nov 13 '12, 09:01 AM

Hi my current code is:

Code:

Option Compare Database
Option Explicit
  
Private Sub Login_Click()
    Dim strSQL As String, strForm As String, strMsg As String
    Dim dbVar As DAO.Database
    
    strSQL = "([UserInitials]='%I') AND ([Userpassword]='%P')"
    strSQL = Replace(strSQL, "%I", Me.UserName)
    strSQL = Replace(strSQL, "%P", Me.Password)
    Select Case Nz(DLookup("[Regular] & [Admin]", "tbl_User", strSQL), "")
    Case ""                 'No matching record found
        Call MsgBox("Please re-enter UserName and Password")
        Exit Sub
    Case "00"               'Both FALSE
        strMsg = "Welcome"
        strForm = "Staff1"
    Case "-10"              'Regular TRUE; Admin FALSE
        strMsg = "Welcome"
        strForm = "Staff2"
    Case "0-1", "-1-1"      'Admin TRUE; Regular EITHER
        strMsg = "Please use caution when changing the conditions of " & _
                 "tables and queries."
        strForm = "Manager1"
    End If
    Set dbVar = CurrentDb()
    strSQL = Replace("UPDATE [uSysCurrentUser] SET [CurrentUser]='%N'", _
                     "%N", Me.UserName)
    Call dbVar.Execute(strSQL, dbFailOnError)
    Call DoCmd.Close
    If strMsg > "" Then
    Call MsgBox(strMsg)
    Call DoCmd.OpenForm(strForm)
End Sub

My question is I have tried putting stop after the Nz(DLookup...) line and have also tried putting a codebreak point on this line. I have just tried a correct username and password and tried to login. However, the debugging mode did not activate at any point have I done something wrong?

To clarify when I say "nothing happens" what I mean is that when I click on the login button - no messages are displayed, no data is updated and no forms are opened.

**NeoPa** · Nov 13 '12, 01:37 PM

Good work.

You're unfortunate in that the Stop you put after line #11 won't work because it is a Select command. It's a little bit like putting it after an If command, only even more complicated. You followed the instructions well, but came unstuck through no fault of your own. The instructions were good for the previously posted code, but not for this.

Here's a new set :

Put a Stop command before line #11.
When code execution stops run the following commands from the Immediate pane of the VB Editor and post the results back in here :
Code:
```
?strSQL
```

Code:

Set db=CurrentDb():?db.Name:Set tdf=db.TableDefs("tbl_User"):?tdf.Name:For Each fld In tdf.Fields:?"[" & fld.Name & "] ";:Next fld

Code:

?"'" & Nz(DLookup("[Regular] & [Admin]", "tbl_User", strSQL), "") & "'"

This should give us most of the information we require to see what might be going wrong.

Multi user login screen - question about tracking logged in users

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment