Multi user login screen - question about tracking logged in users

[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_Cli ck()
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.