Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2012
    Posts
    9

    Unanswered: VBA : Need help on Log In Log Out Session

    Helo guys,

    I'm having trouble with my program on access I have made a log in and log out database that puts the time stamp whenever you log in.

    Yet the problem is, it replaces the current value whenever the user logs in back and logs out again.

    And also for my problem #2 I can't seem to make the username and password authentication work.

    I'm a newbie with VBA so any help would be appreciated.

    Here is my code :

    Code:
    Private Sub cmdLogIn_Click()
    ' double check for value in userid
        If Me.txtUserID.Value = "" Then
            MsgBox "You must enter a User ID", vbOKOnly + vbCritical, "No User ID Entered"
            Me.txtUserID.SetFocus
            Exit Sub
        End If
    ' double check for value in user pw
        If Me!txtPW.Value = "" Then
            MsgBox "You must enter your Password", vbOKOnly + vbCritical, "No Password Entered"
            Me.txtPW.SetFocus
            Exit Sub
    End If
    
        DoCmd.Hourglass True
        DoCmd.SetWarnings False
    'UPDATE USER LOGIN DATE
        DoCmd****nSQL "UPDATE tblUserLog SET tblUserLog.BeginTime = Now()" & _
                     "WHERE (((tblUserLog.userID)='" & Me.txtUserID.Value & "'));"
        DoCmd.SetWarnings True
        DoCmd.Hourglass False
    Last edited by confuseduser; 07-10-12 at 01:26.

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    To make the validation work you need to understand that null and "" are not the same thing, but the solution is easy.

    Instead of: If Me.txtUserID.Value = "" Then
    Use: If nz(Me.txtUserID.Value,"") = "" Then

    You can also use a single if statement to control the logical flow which avoids the need to have Exit Sub in the middle. This allows the option of adding code at the end that will always execute or more easily adding error handling (which you really should do)
    Code:
    Private Sub cmdLogIn_Click()
        ' double check for value in userid
        If nz(Me.txtUserID.Value,"") = "" Then
            MsgBox "You must enter a User ID", vbOKOnly + vbCritical, "No User ID Entered"
            Me.txtUserID.SetFocus
            
        ' double check for value in user pw
        ElseIf nz(Me.txtPW.Value,"") = "" Then
            MsgBox "You must enter your Password", vbOKOnly + vbCritical, "No Password Entered"
            Me.txtPW.SetFocus
            
        'UPDATE USER LOGIN DATE
        Else
    
          DoCmd.Hourglass True
          DoCmd.SetWarnings False
           
          DoCmd****nSQL "UPDATE tblUserLog SET tblUserLog.BeginTime = Now()" & _
                       "WHERE (((tblUserLog.userID)='" & Me.txtUserID.Value & "'));"
          
          DoCmd.SetWarnings True
          DoCmd.Hourglass False
          
        End if
    End Sub
    Yet the problem is, it replaces the current value whenever the user logs in back and logs out again.
    Are you looking for a history of login records? If so, you need to use an insert statement rather than an update. The update is always going to change the existing value where the insert will leave the existing record alone and add a new one.

    If that's not what you are looking for I guess I'm not understanding the question.

    Steve

  3. #3
    Join Date
    Jul 2012
    Posts
    9
    Hello Steve,

    Thank you! It works on the authentication.

    I appreciate your help.

    And regarding "that" question, I need to have a record/history of the log in/out sessions. So it's a yes.

    I tried to make it as this code but it ends up having problem.

    Code:
    DoCmd****nSQL "INSERT tblUserLog SET tblUserLog.BeginTime = Now()" & _
                       "WHERE (((tblUserLog.userID)='" & Me.txtUserID.Value & "'));"
    Any suggestion on how will I be able to code this if it's for INSERT procedure?

  4. #4
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    The insert into sql follows a different pattern. You have to provide the values that you want in the table but you don't need to specify a where clause. Assuming that the only two fields in your table are the user ID and the login date, you could try something like this:

    INSERT INTO tblUserLog VALUES('" & Me.txtUserID.Value & "',#" & Now() & "#)"

    Otherwise you will have to specify the fields or the additional values. A google search on INSERT INTO will get you more information than you ever wanted to know.

    Steve

  5. #5
    Join Date
    Jul 2012
    Posts
    9
    Hi Steve,

    I have tried to google that many times and to be honest.

    I'm very confused regarding the "insert into" statement.

    I have tried the one that you have suggested yet it gives me the error.

    Number of query values and destination fields are not the same.

    I'm a newbie regarding MS ACCESS so bear with me.

    Thanks for helping me this far Steve!

    BTW I have 1 table and 1 query the query logs the history/records of their session.

    While the tblUserLog consist of the username and password with their time log in on it.

    You would be saving my life if you would be able to help me fix it!

  6. #6
    Join Date
    Jul 2012
    Posts
    9
    Sorry to double post but just to update with the code.

    Code:
    INSERT INTO tblUserLog.UserID VALUES('" & Me.txtUserID.Value & "',#" & Now() & "#)"
    Well I have seen alot of INSERT INTO statement on google and It's eating my brains out now! lol!

    INSERT INTO table_name
    VALUES (value1, value2, value3,...)

    Okay let's say

    INSERT INTO tblUserLog
    VALUES ('" & Me.txtUserID.Value & "',#" & Now() & "#)

    yet this still gives me error, so what is wrong with the statement? agghh so confused right now.

  7. #7
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by confuseduser View Post
    Number of query values and destination fields are not the same.
    The INSERT INTO is written assuming that you have two fields in the table but later on you say

    Quote Originally Posted by confuseduser View Post
    While the tblUserLog consist of the username and password with their time log in on it.
    which indicates that the tblUserLog table contains three fields. That's the source of the error. You either need to specify which fields to insert the values into or specify a value for every field in the table.

    Assuming that tblUserLog contains three fields named username, password, and timelog your statements would look something like this:

    INSERT INTO tblUserLog Values ('Tom','abc123',#7/11/12#)

    or you could specify the fields to add data to by using

    INSERT INTO tblUserLog (username,timelog) Values ('Tom',#7/11/12#)

    But.... earlier on you said that you needed to keep the history of logins
    Quote Originally Posted by confuseduser View Post
    And regarding "that" question, I need to have a record/history of the log in/out sessions. So it's a yes.
    This means that you would be duplicating the password information for every login! You need to have a User table in addition to tblUserLog. Move the password field to the User table along with anything else that is unique to the user. Use the Primary Key of the User table in tblUserLog to connect to that user and add the timelog. If tblUserLog only contains these two fields the initial INSERT statement should work.

    and one last thing... you are inserting into a table not a field so you don't want INSERT INTO tblUserLog.UserID, just INSERT INTO tblUserLog

    Steve

  8. #8
    Join Date
    Jul 2012
    Posts
    9
    THANK YOU STEVE!! You have enlightened me! Now all I need to do is do the proper programming! I wish I can do it without problems now. Though I will update if there are still problems because I won't be able to do it today.

  9. #9
    Join Date
    Jul 2012
    Posts
    9
    Hi again, I have made another table for the Log In and Log Out time stamp.

    And now I'm confused "again".

    Okay I have set the other table as tblTimeStamp which contains Name,BeginTime,Endtime and made it connect with the tblUserlog's UserID. tblUserlog contains UserID and Password.

    So I made this :

    Code:
    "INSERT INTO tblTimeStamp (Name,EndTime) VALUES('" & Me.txtUserID.Value & "',#" & Now() & "#)"
    and it does work but the only problem is begin time and end time ends on different FIELD!
    let's say :

    Field 1 BeginTime : 1. 9:00 PM EndTime : 1. BLANK
    2. BLANK 2. 9:30 PM


    and my last one is :

    My only problem is the admin access now! That's the only problem that I've got!
    Last edited by confuseduser; 07-17-12 at 02:59. Reason: GOT IT!

  10. #10
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    I think you have figured this out, but just in case....

    The INSERT INTO statement would work great for the BeginTime but to keep the EndTime on the same record you would need to use an update statement.

    You could do something like:
    Code:
    "Update tblTimeStamp set EndTime = # & now() & "# 
     where Name = 'John' and EndTime is null"
    However, the problem with this is you really can't guarantee that you are on the same record as before. If something happened the last time they were logged in and the endTime wasn't entered you would be updating both of the records with today's date.

    Alternatively, you could put an unique ID on the table and rework the query to get only the latest entry for that user. Or you could store the ID in the code while the user is logged in and use that ID as your where clause when they leave. You could also store the BeginTime in the code and use that.

    The approach I usually take is to restructure the table so it contains a entry Type field which specifies if the user is entering or leaving. So tblTimeStamp would contain Name,TimeStamp,Reason and the statements would be something like:

    Code:
    "INSERT INTO tblTimeStamp 
    VALUES('" & Me.txtUserID.Value & "',#" & Now() & "#, 'BeginTime')"

    Steve

  11. #11
    Join Date
    Jul 2012
    Posts
    9
    I didn't get the last statement that you're telling me steve.

    Alternatively, you could put an unique ID on the table and rework the query to get only the latest entry for that user. Or you could store the ID in the code while the user is logged in and use that ID as your where clause when they leave. You could also store the BeginTime in the code and use that.

    The approach I usually take is to restructure the table so it contains a entry Type field which specifies if the user is entering or leaving. So tblTimeStamp would contain Name,TimeStamp,Reason and the statements would be something like:


    Code:
    "INSERT INTO tblTimeStamp
    VALUES('" & Me.txtUserID.Value & "',#" & Now() & "#, 'BeginTime')"
    Well I haven't found a solution on how to make the BeginTime and EndTime to end up on the same field.

    I'm sorry for dragging you this far, and btw what do you mean I need to restructure the table?

  12. #12
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Essentially, you have two options:

    1) Keep the table as it is (Name, BeginTime, EndTime) and find some way to reference back to the correct row (see my last post for the ideas). I don't find any of these methods to be foolproof.

    2) Restructure the table to purposely store the Begin Time and End Time on two different rows (Name, TimeStamp, Reason). This way you don't need to worry if the last entry was properly recorded or how to reference it in the table. You just add another row for the current need.

    The data would look like this:

    In Option 1:
    Name, BeginTime, EndTime
    John, 07/17/12 2:21, 07/17/12 6:18
    Tom, 07/17/12 3:25,
    Mike, 07/17/12 4:06, 07/17/12 5:32

    In Option 2:
    Name, TimeStamp, Reason
    John, 07/17/12 2:21, Begin
    Tom, 07/17/12 3:25, Begin
    Mike, 07/17/12 4:06, Begin
    Mike, 07/17/12 5:32, End
    John, 07/17/12 6:18, End

    You can get there either way, I just find option 2 to be simpler and I prefer to structure my tables to avoid null values whenever possible.

    Steve

  13. #13
    Join Date
    Jul 2012
    Posts
    9
    Hey steve, your suggestion in option 2 is quite good and I have tried it. It's working. Thank you.

    BUT!

    I really need the option 1 for a clean record.
    Because I will be extracting this to MS Excel.

    So If I use the "UPDATE and WHERE" will this expression work like every row after the "begin time"?

    I would be trying that but there's something in my mind like the one I saw on google, I'm slight confused though.

    'Add Data to Next Row
    Code:
    Sub UpdateRunning()
        Dim currDate, LastPlc, AlanAverage, DowJ, NasdaQ
        ActiveSheet.Unprotect
        Application.ScreenUpdating = False
        currDate = Application.Text(Now(), "mm/dd/yy HH:MM")
        Sheets("Holdings").Select
        LastPlc = Application.CountA(ActiveSheet _
            .Range("Y:Y")) + 1  'Find last cell/row plus one
        AlanAverage = [ARBave]  'Get amt from Named cell
        Cells(LastPlc, 25) = [CurTotl]  'Get amt from Named cell
        Cells(LastPlc, 26) = currDate   'Get amt from Named cell
        Cells(LastPlc, 29) = [DJI]
        Cells(LastPlc, 30) = [nasd]
        Cells(LastPlc, 29).Select
        ActiveSheet.Protect
        Application.ScreenUpdating = True
    End Sub
    I need some help regarding this if I would be implementing this.

  14. #14
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    I'm not sure what you are asking here.

    I do very little integration with Excel though so you're probably best starting a new thread with a specific question.

    Steve

  15. #15
    Join Date
    Jul 2012
    Posts
    9
    Thanks steve, I really appreciate your help and for helping me this far.

    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •