Multi user login screen - question about tracking logged in users

[anewuser]

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:

Option Compare Database
Private Sub Login_Click()
UserName.SetFocus
If UserName = "Staff1" And Password = "Staff1" Then
MsgBox "Welcome"
DoCmd.Close
DoCmd.OpenForm "Staff1"
ElseIf UserName = "Staff2" And Password = "Staff2" Then
MsgBox "Welcome"
DoCmd.Close
DoCmd.OpenForm "Staff2"
ElseIf UserName = "Manager1" And Password = "Manager1" Then
MsgBox "Welcome, please exercise caution when changing query or table conditions", vbInformation, "CDSignatures"
DoCmd.Close
DoCmd.OpenForm "Manager1"
Else
MsgBox "Please re-enter Username and Password"
End If
End Sub
 

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.

[TheSmileyCoder]

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

Private Sub Form_Open(Cancel as Integer)
  Dim strUserInitials as String
  strUserInitials=Environ("UserName")
 
  'Match user initials to a user
    Dim lngUserID as long
    lngUserID=nz(Dlookup("PK_User","tbl_User","tx_UserInitials='" & strUserInitials & "'"),0)
    If lngUserID=0 then
      'Means user was not found in table of users. Inform user 
      ' to contact a database admin
      Cancel=True
      MsgBox "You are not a registered user" & vbnewline & _
             "Please contact John Doe by email [e-mail]",vbokonly+vbinformation
 
      Docmd.Quit
    End If
 
  'We made it to here, so User is recognized
  'Store information in local table for easy access
  CurrentDB.Execute "UPDATE uSysCurrentUser set CurrentUser=" & lngUserID
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:

Private Sub Form_beforeUpdate(Cancel as Integer)
  If me.NewRecord then
    me.ID_CreatedBy=UserID()
    Me.Date_Created=Now()
  End If
    Me.ID_ChangedBy=USerID()
    Me.Date_Changed=Now()
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

Public Function UserID() as Long
  UserID=Dlookup("CurrentUser","uSysCurrentUser")
End Function

[anewuser]

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.

[TheSmileyCoder]

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.

[anewuser]

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.

[TheSmileyCoder]

Try this in your VBE windows immediate pane:

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.

[anewuser]

Is there a way of doing it so that the Username and Password can be identified from the tbl_User table?

[NeoPa]

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.

[anewuser]

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.

[NeoPa]

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.

[Rabbit]

You may want to read this article: http://bytes.com/topic/access/insigh...atabase-access

[anewuser]

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.

[anewuser]

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.

Option Compare Database
 
Private Sub Login_Click()
UserName.SetFocus
UserName = DLookup("UserInitials", "tbl_User", "UserInitials =" & strUserInitials & "UserName")
Password = DLookup("Userpassword", "tbl_User", "Userpassword =" & strUserpassword & "Password")
If True Then
    DoCmd.Close
    MsgBox "Welcome"
    DoCmd.OpenForm "Staff1"
End If
End Sub

[NeoPa]

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 :

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", Encrypt(Me.Password))
    If Not IsNull(DLookup("UserInitials", "tbl_User", strWhere)) Then
        Call DoCmd.Close
        Call MsgBox("Welcome")
        Call DoCmd.OpenForm("Staff1")
    End If
End Sub
 
Private Function Encrypt(strText) As String
    Encrypt = strText
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).

[anewuser]

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.

[NeoPa]

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.

[anewuser]

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

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]

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.

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]

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.

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]

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 :

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]

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...

[NeoPa]

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]

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

[NeoPa]

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]

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]

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]

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.

[zmbd]

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]

Hi my current code is:

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]

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. ?strSQL

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

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

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

[anewuser]

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.

[Rabbit]

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.

[NeoPa]

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.

[anewuser]

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.

[zmbd]

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:

Private Sub Command0_Click()
 
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.

[anewuser]

Hi I have tried both of your methods and have still come up with nothing.

[zmbd]

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

[anewuser]

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.

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
        strMsg = "Please re-enter UserName and Password"
    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 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 If
End Sub

[NeoPa]

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.

[anewuser]

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.

[zmbd]

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?

[NeoPa]

Not a problem. We're happy to help further.

First though, what state is your code in now? Exactly as in post #38?

[anewuser]

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.

[NeoPa]

Try running the following line of code in your Immediate pane (Ctrl-G from the VB Editor window) with the database open :

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 ;-)

[anewuser]

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.

[zmbd]

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...

[NeoPa]

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.

[zmbd]

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

[anewuser]

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.

[NeoPa]

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.