By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,488 Members | 2,513 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,488 IT Pros & Developers. It's quick & easy.

Multi user login screen - question about tracking logged in users

P: 44
Hi,

I currently have a working multi user login form which automatically directs users to the relevent forms e.g. general staff have access to a form called staff1 and staff2 whereas managers have access to a form called manager1. I have used the following code:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Private Sub Login_Click()
  3. UserName.SetFocus
  4. If UserName = "Staff1" And Password = "Staff1" Then
  5. MsgBox "Welcome"
  6. DoCmd.Close
  7. DoCmd.OpenForm "Staff1"
  8. ElseIf UserName = "Staff2" And Password = "Staff2" Then
  9. MsgBox "Welcome"
  10. DoCmd.Close
  11. DoCmd.OpenForm "Staff2"
  12. ElseIf UserName = "Manager1" And Password = "Manager1" Then
  13. MsgBox "Welcome, please exercise caution when changing query or table conditions", vbInformation, "CDSignatures"
  14. DoCmd.Close
  15. DoCmd.OpenForm "Manager1"
  16. Else
  17. MsgBox "Please re-enter Username and Password"
  18. End If
  19. End Sub
  20.  
My question is how can I track users once they have logged in. I have a database which tracks data that has been editted from the main database and I would like to know how to show which user has done this.

Thanks in advance.
Oct 29 '12 #1

✓ answered by NeoPa

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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Login_Click()
  5.     Dim strSQL As String, strForm As String, strMsg As String
  6.     Dim dbVar As DAO.Database
  7.  
  8.     strSQL = "([UserInitials]='%I') AND ([Userpassword]='%P')"
  9.     strSQL = Replace(strSQL, "%I", Me.UserName)
  10.     strSQL = Replace(strSQL, "%P", Me.Password)
  11.     Select Case Nz(DLookup("[Regular] & [Admin]", "tbl_User", strSQL), "")
  12.     Case ""                 'No matching record found
  13.         Call MsgBox("Please re-enter UserName and Password")
  14.         Exit Sub
  15.     Case "00"               'Both FALSE
  16.         strMsg = "Welcome"
  17.         strForm = "Staff1"
  18.     Case "-10", "-1-1"      'Regular TRUE; Admin Either
  19.         strMsg = ""
  20.         strForm = "Staff2"
  21.     Case "0-1"              'Admin TRUE; Regular FALSE
  22.         strMsg = "Please use caution when changing the conditions of " & _
  23.                  "tables and queries."
  24.         strForm = "Manager1"
  25.     End Select
  26.     Set dbVar = CurrentDb()
  27.     strSQL = Replace("UPDATE [uSysCurrentUser] SET [CurrentUser]='%N'", _
  28.                      "%N", Me.UserName)
  29.     Call dbVar.Execute(strSQL, dbFailOnError)
  30.     Call DoCmd.Close
  31.     If strMsg > "" Then Call MsgBox(strMsg)
  32.     Call DoCmd.OpenForm(strForm)
  33. 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.

Share this Question
Share on Google+
55 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
I use a combination of alot of different techniques picked up over the years.

First I have a table of users with the fields:
PK_User (Autonumber, primary key)
tx_UserInitials (in my case matching the username of people logged in) (Text Field)
tx_FirstName (Text Field)
tx_LastName (Text Field)
b_Admin (Yes/no field)


In your login screen, you can capture the username, and match it to a ID in your user table. This could be done in the login forms open event

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel as Integer)
  2.   Dim strUserInitials as String
  3.   strUserInitials=Environ("UserName")
  4.  
  5.   'Match user initials to a user
  6.     Dim lngUserID as long
  7.     lngUserID=nz(Dlookup("PK_User","tbl_User","tx_UserInitials='" & strUserInitials & "'"),0)
  8.     If lngUserID=0 then
  9.       'Means user was not found in table of users. Inform user 
  10.       ' to contact a database admin
  11.       Cancel=True
  12.       MsgBox "You are not a registered user" & vbnewline & _
  13.              "Please contact John Doe by email JD@MyCompany.Com",vbokonly+vbinformation
  14.  
  15.       Docmd.Quit
  16.     End If
  17.  
  18.   'We made it to here, so User is recognized
  19.   'Store information in local table for easy access
  20.   CurrentDB.Execute "UPDATE uSysCurrentUser set CurrentUser=" & lngUserID
  21. End Sub
First off, for the above to make sense it requires that each user uses a individual frontend, and that the frontend contains a uSysCurrentUser table with 1 record allready created, and the table should contain a number field named currentuser



With the above code, we register the user as he open the DB.

Now step 2 is tying his user identity to each record he or she creates. That means we need fields in each table called:
ID_CreatedBy Number, Long
Date_Created, DateField
ID_ChangedBy, Number, Long
Date_Changed, DateField

In every form you need to modify the beforeUpdate event with the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_beforeUpdate(Cancel as Integer)
  2.   If me.NewRecord then
  3.     me.ID_CreatedBy=UserID()
  4.     Me.Date_Created=Now()
  5.   End If
  6.     Me.ID_ChangedBy=USerID()
  7.     Me.Date_Changed=Now()
  8. End Sub
Now UserID is a custom function, that simply looks into the table uSysCurrentUser. The function should be placed in a public module, so it can be called from anywhere
Expand|Select|Wrap|Line Numbers
  1. Public Function UserID() as Long
  2.   UserID=Dlookup("CurrentUser","uSysCurrentUser")
  3. End Function
Oct 29 '12 #2

P: 44
Firstly thanks for your help.

Just one question, do I need to use your code instead of my code on the login form? or is it in addition to my code. Sorry, this feels like a stupid question but I am very new to access.
Oct 29 '12 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
The above is simply an example of how to record the user logging into the db, and how to use that information to mark records with his userID. At this point none of my code really relates to what rights the user has. This could be setup in a the user table, or in a seperate table.
Oct 29 '12 #4

P: 44
Hi, I am having problems with the first code provided. Ih ave myself set up in the tbl_User table and yet when I am trying to login it tells me that I am not registered and closes the program. Am I doing something wrong.

And by the way I don't know if this will make a difference but the front end will only be accessible on a couple of computers but different users will need access to it.
Oct 30 '12 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
Try this in your VBE windows immediate pane:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Environ("UserName") 
What the code shows you, is what should be stored as initials in the tbl_User. It might make sense to call it something else, depending on your company setup, but where I have worked the most common thing is to have the username be the initials of the persons working there.


If people are using the client from shared workstations that are "open" all the time, then the Environ function won't give the results you are after. The Environ returns the username of the person who logged into windows during startup. So if its a shared PC where each individual does not log off and back on again, then
you need a different approach.
Oct 30 '12 #6

P: 44
Is there a way of doing it so that the Username and Password can be identified from the tbl_User table?
Nov 2 '12 #7

NeoPa
Expert Mod 15k+
P: 31,186
If multiple users require access to the database from a single machine / logon then you will need something where the user selects or types their own ID. Giving access based simply on the Windows logon code will not suffice in such circumstances.
Nov 2 '12 #8

P: 44
What I have is a login form with a username and password field and a table, "tbl_User", which I would like to use to store user details i.e. Username, first name, surname, password, admin (yes/no) as suggested earlier. I have the source of the form set to tbl_User and what I am trying to do is to allow users to type their username and password into the relevant boxes and use a code to identify that they are in the table and what access rights they have.

However, at the moment I am having trouble simply getting the form to identify the user. I have tried using the nz(Dlookup...) function shown earlier in the thread and I have also tried running it through a query. I just can't seem to get it to work.
Nov 2 '12 #9

NeoPa
Expert Mod 15k+
P: 31,186
Typically, you wouldn't need, or even want, the form to be bound to any table. The process would be that the user enter the UserName and Password in unbound TextBoxes then click on a button to submit the logon attempt. The code behind the button would first encrypt the password using the same function as was used to encrypt the passwords stored in the table (Never store passwords unencrypted as that's asking for trouble. There are threads on this site that can help with that side of things - AES Encryption Algorithm for VBA and VBScript, RC4 Encryption Algorithm for VBA and VBScript & SHA2 Cryptographic Hash Algorithm for VBA and VBScript.) then it finds the values for the [Password] and [Tries] fields from the table for the matching record. I suggest you do this using a DAO.Recordset as you will need to update [Tries] when done.

If the encrypted password entered matches that found in the record then you have entry. Reset [Tries] to zero.

If the encrypted password entered doesn't match that found in the record then you have a failed attempt. Increment [Tries].

If there are other field values you may need from this table later on in your session then make sure you get them and make them available somewhere accessible for use later.
Nov 2 '12 #10

Rabbit
Expert Mod 10K+
P: 12,315
You may want to read this article: http://bytes.com/topic/access/insigh...atabase-access
Nov 2 '12 #11

P: 44
Hi, I am still having trouble with the login form I can't get the code to compare the username and password typed in with the one in the table. I need some help as I have tried numerous different methods e.g. running through a query, applying a filter and using the Nz(DLookup... mentioned earlier. I don't know how else I can do this.
Nov 6 '12 #12

P: 44
I have now made some progress, possibly, the code will now let people type into the Username and password boxes and will allow them entry, however, it will also let in those whose usernames and passwords are not in the tbl_User. Below is the new code I have used and I need some advice on improving it.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Login_Click()
  4. UserName.SetFocus
  5. UserName = DLookup("UserInitials", "tbl_User", "UserInitials =" & strUserInitials & "UserName")
  6. Password = DLookup("Userpassword", "tbl_User", "Userpassword =" & strUserpassword & "Password")
  7. If True Then
  8.     DoCmd.Close
  9.     MsgBox "Welcome"
  10.     DoCmd.OpenForm "Staff1"
  11. End If
  12. End Sub
Nov 6 '12 #13

NeoPa
Expert Mod 15k+
P: 31,186
First, please check out Require Variable Declaration. This is a very important point and you will benefit greatly by taking it on board at this early stage of your development.

Anyway, try the following. I'm not sure all the control and field names are correct but you should be able to fix that up easily enough :

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Login_Click()
  5.     Dim strWhere As String, strPassword As String
  6.  
  7.     strWhere = "([UserInitials]='%I') AND ([UserPassword]='%P')"
  8.     strWhere = Replace(strWhere, "%I", Me.UserName)
  9.     strWhere = Replace(strWhere, "%P", Encrypt(Me.Password))
  10.     If Not IsNull(DLookup("UserInitials", "tbl_User", strWhere)) Then
  11.         Call DoCmd.Close
  12.         Call MsgBox("Welcome")
  13.         Call DoCmd.OpenForm("Staff1")
  14.     End If
  15. End Sub
  16.  
  17. Private Function Encrypt(strText) As String
  18.     Encrypt = strText
  19. End Function
The Encrypt function I've used here is just a dummy. It's there to illustrate how you should be working with such data. Earlier posts include links that can help you to produce a function to handle this for yourself (Mainly just Copy / Paste TBF).
Nov 6 '12 #14

P: 44
Thanks for the previous code, with a few small tweaks I have got it working perfectly, thank you. I have one more question, in my tbl_User I have two fields regular and admin (both yes/no) I would like to allow some users to access the form "staff2" if the regular box is yes and others to access the form "manager1" if the admin box is yes.

If you can break down the code above for me I will give it a try myself. I don't really understand it and may be able to get some results if I understand it better.
Nov 6 '12 #15

NeoPa
Expert Mod 15k+
P: 31,186
Well, ignoring declarations of the variables and the procedures which I don't expect you'll need, the breakdown is as follows :
  1. 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.
  2. Line #8 replaces %I in the string with the actual value from your form control.
  3. 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().
  4. 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.
  5. Lines #11 through #13 only execute if a matching record was found. This is fundamentally your code anyway, so no further explanation required.
  6. 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.
Nov 7 '12 #16

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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Login_Click()
  5.     Dim strWhere As String, strPassword As String
  6.  
  7.     strWhere = "([UserInitials]='%I') AND ([Userpassword]='%P')"
  8.     strWhere = Replace(strWhere, "%I", Me.UserName)
  9.     strWhere = Replace(strWhere, "%P", Me.Password)
  10.     If Not IsNull(DLookup("UserInitials", "tbl_User", strWhere & "Regular" = False & "Admin" = False)) Then
  11.         DoCmd.Close
  12.         MsgBox "Welcome"
  13.         DoCmd.OpenForm "Staff1"
  14.     ElseIf Not IsNull(DLookup("UserInitials", "tbl_User", strWhere & "Regular" = True)) Then
  15.         DoCmd.Close
  16.         DoCmd.OpenForm "Staff2"
  17.     ElseIf Not IsNull(DLookup("UserInitials", "tbl_User", strWhere & "Admin" = True)) Then
  18.         MsgBox "Please use caution when changing the conditions of tables and queries"
  19.         DoCmd.Close
  20.         DoCmd.OpenForm "Manager1"
  21.     End If
  22.  CurrentDb.Execute "UPDATE usyscurrentuser set CurrentUser=UserName"
  23. End Sub
Nov 7 '12 #17

NeoPa
Expert Mod 15k+
P: 31,186
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Login_Click()
  5.     Dim strSQL As String, strForm As String, strMsg As String
  6.     Dim dbVar As DAO.Database
  7.  
  8.     strSQL = "([UserInitials]='%I') AND ([Userpassword]='%P')"
  9.     strSQL = Replace(strSQL, "%I", Me.UserName)
  10.     strSQL = Replace(strSQL, "%P", Me.Password)
  11.     Select Case Nz(DLookup("[Regular] & [Admin]", "tbl_User", strSQL), "")
  12.     Case ""                 'No matching record found
  13.         Call MsgBox("Please re-enter UserName and Password")
  14.         Exit Sub
  15.     Case "00"               'Both FALSE
  16.         strMsg = "Welcome"
  17.         strForm = "Staff1"
  18.     Case "-10", "-1-1"      'Regular TRUE; Admin Either
  19.         strMsg = ""
  20.         strForm = "Staff2"
  21.     Case "0-1"              'Admin TRUE; Regular FALSE
  22.         strMsg = "Please use caution when changing the conditions of " & _
  23.                  "tables and queries."
  24.         strForm = "Manager1"
  25.     End Select
  26.     Set dbVar = CurrentDb()
  27.     strSQL = Replace("UPDATE [uSysCurrentUser] SET [CurrentUser]='%N'", _
  28.                      "%N", Me.UserName)
  29.     Call dbVar.Execute(strSQL, dbFailOnError)
  30.     Call DoCmd.Close
  31.     If strMsg > "" Then Call MsgBox(strMsg)
  32.     Call DoCmd.OpenForm(strForm)
  33. 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.
Nov 7 '12 #18

P: 44
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Login_Click()
  5.     Dim strSQL As String, strCase As String
  6.     Dim dbVar As DAO.Database
  7.  
  8.     strSQL = "([UserInitials]='%I') AND ([Userpassword]='%P')"
  9.     strSQL = Replace(strSQL, "%I", Me.UserName)
  10.     strSQL = Replace(strSQL, "%P", Me.Password)
  11.     strCase = DLookup("[Regular] & [Admin]", "tbl_User", strSQL)
  12.     If strCase = Null Then                  'No matching record found
  13.         MsgBox "Please re-enter username and password"
  14.         Exit Sub
  15.     ElseIf strCase = "00" Then              'Both FALSE
  16.         DoCmd.Close
  17.         MsgBox "Welcome"
  18.         DoCmd.OpenForm "Staff1"
  19.     ElseIf strCase = "-10" Then             'Regular TRUE; Admin False
  20.         DoCmd.Close
  21.         MsgBox = "Welcome"
  22.         DoCmd.OpenForm "Staff2"
  23.     ElseIf strCase = "0-1" Or "-1-1" Then   'Admin TRUE; Regular Either
  24.         DoCmd.Close
  25.         MsgBox "Please use caution when changing the conditions of " & _
  26.                  "tables and queries."
  27.         DoCmd.OpenForm "Manager1"
  28.     End If
  29.     Set dbVar = CurrentDb()
  30.     strSQL = Replace("UPDATE [usyscurrentuser] SET [CurrentUser]='%N'", _
  31.                      "%N", Me.UserName)
  32.     Call dbVar.Execute(strSQL, dbFailOnError)
  33.     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.
Nov 9 '12 #19

NeoPa
Expert Mod 15k+
P: 31,186
I have little idea why you might have wanted to revert to the If...Then...ElseIf 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 :
Expand|Select|Wrap|Line Numbers
  1. 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).
Nov 9 '12 #20

P: 44
I did not mean to cause any offence going back to If...Then...ElseIf. 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...
Nov 9 '12 #21

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 9 '12 #22

TheSmileyCoder
Expert Mod 100+
P: 2,321
Just for info:
If you prefix a table with usys it automatically becomes hidden and is considered a developer implemented system table.
Nov 9 '12 #23

NeoPa
Expert Mod 15k+
P: 31,186
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 :-)
Nov 9 '12 #24

P: 44
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.
Nov 12 '12 #25

zmbd
Expert Mod 5K+
P: 5,287
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.
Nov 12 '12 #26

NeoPa
Expert Mod 15k+
P: 31,186
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.
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.
Nov 12 '12 #27

zmbd
Expert Mod 5K+
P: 5,287
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.
Nov 12 '12 #28

P: 44
Hi my current code is:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Login_Click()
  5.     Dim strSQL As String, strForm As String, strMsg As String
  6.     Dim dbVar As DAO.Database
  7.  
  8.     strSQL = "([UserInitials]='%I') AND ([Userpassword]='%P')"
  9.     strSQL = Replace(strSQL, "%I", Me.UserName)
  10.     strSQL = Replace(strSQL, "%P", Me.Password)
  11.     Select Case Nz(DLookup("[Regular] & [Admin]", "tbl_User", strSQL), "")
  12.     Case ""                 'No matching record found
  13.         Call MsgBox("Please re-enter UserName and Password")
  14.         Exit Sub
  15.     Case "00"               'Both FALSE
  16.         strMsg = "Welcome"
  17.         strForm = "Staff1"
  18.     Case "-10"              'Regular TRUE; Admin FALSE
  19.         strMsg = "Welcome"
  20.         strForm = "Staff2"
  21.     Case "0-1", "-1-1"      'Admin TRUE; Regular EITHER
  22.         strMsg = "Please use caution when changing the conditions of " & _
  23.                  "tables and queries."
  24.         strForm = "Manager1"
  25.     End If
  26.     Set dbVar = CurrentDb()
  27.     strSQL = Replace("UPDATE [uSysCurrentUser] SET [CurrentUser]='%N'", _
  28.                      "%N", Me.UserName)
  29.     Call dbVar.Execute(strSQL, dbFailOnError)
  30.     Call DoCmd.Close
  31.     If strMsg > "" Then
  32.     Call MsgBox(strMsg)
  33.     Call DoCmd.OpenForm(strForm)
  34. 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.
Nov 13 '12 #29

NeoPa
Expert Mod 15k+
P: 31,186
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 :
  1. Put a Stop command before line #11.
  2. When code execution stops run the following commands from the Immediate pane of the VB Editor and post the results back in here :
  3. Expand|Select|Wrap|Line Numbers
    1. ?strSQL
  4. Expand|Select|Wrap|Line Numbers
    1. Set db=CurrentDb():?db.Name:Set tdf=db.TableDefs("tbl_User"):?tdf.Name:For Each fld In tdf.Fields:?"[" & fld.Name & "] ";:Next fld
  5. Expand|Select|Wrap|Line Numbers
    1. ?"'" & Nz(DLookup("[Regular] & [Admin]", "tbl_User", strSQL), "") & "'"

This should give us most of the information we require to see what might be going wrong.
Nov 13 '12 #30

P: 44
Hi have tried putting the stop command and a codebreak point before the select case line but still the debugging mode did not activate. and still no data updated, no messages displayed and no forms opened.
Nov 13 '12 #31

Rabbit
Expert Mod 10K+
P: 12,315
You definitely should get an error message since you have an End If on line 25 without a preceding If, it should be an End Select. If you're not getting an error message, then you have On Error Resume Next somewhere and that makes it hard to debug anything. You probably also want an End If after line 33.
Nov 13 '12 #32

NeoPa
Expert Mod 15k+
P: 31,186
That came from my original version in post #18 Rabbit. Nevertheless, code should always be compiled before it's tested. So much time and energy is wasted when this is not done. The OP may not have heard me saying this before so here it is now for all future interactions - Before Posting (VBA or SQL) Code includes that point and various others that you may find helpful.

From the OP's latest comment, though, I'm getting the impression the code isn't even linked to the Command Button called [Login] that is supposed to trigger the code. Try checking the On Click property of that control and make sure :
  1. The control is called [Login].
  2. The code is linked to the control by means of that property.
Nov 14 '12 #33

P: 44
Hi, I have followed your advice and have chaecked the properties of the command button. It is called Login and the event is in the "on click" property. I have also looked at the http://bytes.com/forums/feedback/913...g-vba-sql-code that has been suggested and have tried to compile the code. It doesn't come up with an error but it does in another do I need to rectify this before it will find more or are all problems identified in one go.
Nov 14 '12 #34

zmbd
Expert Mod 5K+
P: 5,287
Let's try a very simple thing.
I've had events that have become... unlinked to the control.
I've also had events that started out as an embedded macro in the control that when converted to VBA have continued to attempt to use the macro even though it was deleted.

SO:
I know it seems stupid; however, please follow my step-by-step EXACTLY! This method has been vetted by myself going on 2 decades.

This beats re-building a form scratch.

You can sometimes achieve the following by simply creating a new command button and cut-n-paste the code (which is what I would do firts!); however, if you can't or don't want to then...

Open the VBA code editor.
Goto the Sub for the onclick event for the button.
Change the name of this sub...
From:
Private Sub Login_Click()
To:
From:
Private Sub DEBUG_Login_Click()
Compile the code
Save
Close the VBA editor
Save the form
Close the form
Close the application
Close Access
Re-open the application
In DESIGN MODE re-open the form
Open the properties for the button
Check that the ONClick event has cleared.
It MUST be cleared!
If not then click on the [...] button in the event.
Repeat the rename... however, rename to "DEBUG_2_" instead of just the DEBUG
IF the event is cleared (which it should be)
Rebuild the event.
You should start out with nothing except:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.  
  3. End Sub
(note the Command0 is just example)

Now cut and past the working code back into this new codeblock for the command button.
Compile.
Save
Close VBA
Save form
Close form
Close and reopen the application
See if things are working as intended... if that stop command is in the code... it should stop the code solong as it is not nested within a loop or logic block. Which is why I suggested placing right after the varible declaration.
Nov 14 '12 #35

P: 44
Hi I have tried both of your methods and have still come up with nothing.
Nov 14 '12 #36

zmbd
Expert Mod 5K+
P: 5,287
Do you have "use special keys" turned off under the options settings for this database?
If so, for troubleshooting, go back in and turn them back on. It is known that this can cause the set break points (red dots) to fail to stop execution... maybe this is the issue with the STOP
Nov 14 '12 #37

P: 44
Just FYI to all those who have helped me in this thread. I have just tried Rabbits suggestion of "it should be an End Select" and "You probably also want an End If after line 33." and it worked...

I have so far tried it against all the valid usernames and passwords I have got set up and against all invalid conditions i.e. valid username invalid password, invalid username valid password and both username and password invalid. And it works beautifully. Again thanks to all who have helped.

PS. I have only checked that the login works I don't yet know if the usyscurrentuser table is updated. Will post further when I do.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Login_Click()
  5.     Dim strSQL As String, strForm As String, strMsg As String
  6.     Dim dbVar As DAO.Database
  7.  
  8.     strSQL = "([UserInitials]='%I') AND ([Userpassword]='%P')"
  9.     strSQL = Replace(strSQL, "%I", Me.UserName)
  10.     strSQL = Replace(strSQL, "%P", Me.Password)
  11.     Select Case Nz(DLookup("[Regular] & [Admin]", "tbl_User", strSQL), "")
  12.     Case ""                 'No matching record found
  13.         strMsg = "Please re-enter UserName and Password"
  14.     Case "00"               'Both FALSE
  15.         strMsg = "Welcome"
  16.         strForm = "Staff1"
  17.     Case "-10"              'Regular TRUE; Admin FALSE
  18.         strMsg = "Welcome"
  19.         strForm = "Staff2"
  20.     Case "0-1", "-1-1"      'Admin TRUE; Regular EITHER
  21.         strMsg = "Please use caution when changing the conditions of " & _
  22.                  "tables and queries."
  23.         strForm = "Manager1"
  24.     End Select
  25.     Set dbVar = CurrentDb()
  26.     strSQL = Replace("UPDATE [uSysCurrentUser] SET [CurrentUser]='%N'", _
  27.                      "%N", Me.UserName)
  28.     Call dbVar.Execute(strSQL, dbFailOnError)
  29.     Call DoCmd.Close
  30.     If strMsg > "" Then
  31.     Call MsgBox(strMsg)
  32.     Call DoCmd.OpenForm(strForm)
  33.     End If
  34. End Sub
Nov 14 '12 #38

NeoPa
Expert Mod 15k+
P: 31,186
Indeed :-) As I said earlier, that was an error in my suggested code. These should be picked up quickly and easily though, as you should always compile your project before testing code. Yes. This means that you should always be using a fully compiled system. If you put new code into a system that cannot compile then you will struggle to identify where the problems are. I'm sure you can see why it's so important to work only within compiled projects. I suggest you find and fix all compilation errors in your project as a matter of priority.

As for the earlier post (#18), I will now update it to reflect the code as it should have been, in order that others searching for answers to similar problems are not led astray.
Nov 14 '12 #39

P: 44
Hi, as it turns out I do still need some help I have compiled the project that I have so far and no errors have been returned. However, the usyscurrentuser table is not being updated and I don't understand how the code should currently work to set the username as the currentuser.
Nov 15 '12 #40

zmbd
Expert Mod 5K+
P: 5,287
Post#34: You state that one error was found... was that fixed and then the code re-compiled? The complier will only find one error at a time and must be re-ran after each fix.

Have you check for the special keys option?

Have you placed the STOP in line and been able to step thru the code yet?
Nov 15 '12 #41

NeoPa
Expert Mod 15k+
P: 31,186
Not a problem. We're happy to help further.

First though, what state is your code in now? Exactly as in post #38?
Nov 15 '12 #42

P: 44
Yes it is in the same condition as post #38. In answer to Z I have fixed all the errors that were found during compiling (the whole project is now compiled. But I have not placed the stop command in yet and I have deactivated the special keys option in access options.
Nov 15 '12 #43

NeoPa
Expert Mod 15k+
P: 31,186
Try running the following line of code in your Immediate pane (Ctrl-G from the VB Editor window) with the database open :
Expand|Select|Wrap|Line Numbers
  1. Set db=CurrentDb():Set tdf=db.TableDefs("uSysCurrentUser"):?tdf.Name;" -";:For Each fld In tdf.Fields:?,fld.Name;:Next fld
Post the results back in here obviously ;-)
Nov 15 '12 #44

P: 44
I am still struggling to activate the debugging mode. I have even tried putting a code break on the very start of the code and have even put a code break on every line but it just won't start the debugging mode.
Nov 16 '12 #45

zmbd
Expert Mod 5K+
P: 5,287
Close your database and reboot the pc.
Reopen your database.
From the MAIN Access window... not VBA...
You must do this from Regular old Access GUI (forms, reports, etc... showing are ok).

Press <Ctrl-G>
Please post back what happens...
Nov 16 '12 #46

NeoPa
Expert Mod 15k+
P: 31,186
I had no idea Ctrl-G worked from there too Z. I've always used it from the VB Editor window, It seems it will work from both.

ANewUser:
I am still struggling to activate the debugging mode.
If you're referring to breaking into code execution, then it's not necessary for this. Please just follow the instructions exactly as posted.
Nov 16 '12 #47

zmbd
Expert Mod 5K+
P: 5,287
super-secret-chemist-formula...
Now I must send the MIB out to erase your minds once OP starts the self-destruct sequence (1-1A, 1-1A-2-2B, 1-B2-B3, 0-0-destruct-0)

@ANewUser
Dont red-dot your code to goto to break mode.
Use STOP
Red-Dots will mess with your code forcing at minimum a decompile/recompile. It is a known bug in the editor (at least to those of us that do a lot of programing) the MS doesn't seem to (or care to) want to fix. Their reply has been along the lines of "well... don't use it then... use the STOP like we do (use your best dunce-mode voice)"

I must have the answer to the instructions given in #46
Nov 16 '12 #48

P: 44
Hi I tried running that line (from post #44) in the immediate pane. I was still able to login as normal but usyscurrentuser wasn't updated.
Nov 19 '12 #49

NeoPa
Expert Mod 15k+
P: 31,186
The only reason to use the code from post #44 was to post the results back here in the thread. There were no changes made to your project in that code whatsoever. If you read the post again you'll see clearly what was asked for.
Nov 19 '12 #50

55 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.