Update query for capturing log out time of user (1 Viewer)

gold007eye · May 18, 2006

I have done some searching to find a method for logging & timestamping when users log in and out. I have no problems with capturing the Log IN information my problem lies with the log out portion where I am trying to use a Update query to update when the user logs out of the database:

I can get the update query to do what I want, but for some reason it is creating a duplicate record (exactly the same as the last record updated)

Here is the code I am using:

Code:

UPDATE LogInTable SET LogInTable.LogOutTime = Time()
WHERE (((LogInTable.LogOutTime) Is Null) AND ((LogInTable.UserName)=LogUserName()));

Basically I am trying to tell the Query to ONLY update records where the "LogOutTime" is Null AND where the UserName field in the table = the username of the person trying to run the update query.

Please help I can't figure out how to prevent these duplicates.

FoFa · May 18, 2006

Ah, but an update does not INSERT records, where is the record/s coming from intitially?

gold007eye · May 18, 2006

I thought the same thing.. I was running the query straight (to test it out) Although I think I know what you are getting at.

here is the code for the LogUserName() function.

Code:

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function LogUserName() As String

' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String, db As Database, r As Recordset, rst As Recordset
Dim table2 As String
Set db = CurrentDb
Set r = db.OpenRecordset("LogInTable")



strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
LogUserName = Left$(strUserName, lngLen - 1)
Else
LogUserName = ""
End If

r.MoveLast
r.AddNew
r.Fields("UserName") = UCase(LogUserName)
r.Fields("LogInDate") = Date
r.Fields("LogInTime") = Time
r.Update


End Function

Is there another way you would suggest tackling this issue?

pbaldy · May 18, 2006

FoFa doesn't appear to be online, but has identified the problem. Your code to look up the user also adds a record, so when you look them up during logout it adds the record again. Either move the code to add a login record someplace else (my preference), or store the user name someplace and get it from there during logout, so you don't call this function again.

gold007eye · May 18, 2006

That's just what I was thinking

I seperated the function out and it is working perfectly now (in my test database..) Now to move it to the production database.

Thanks for the help

gold007eye · May 18, 2006

Ok well this all worked fine in my test database, but when I tried to move it into another production database I am getting "Runtime Error '13': Type mismatch" what am I doing wrong now?

Code:

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function LogUserName() As String

' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String, db As Database, r As Recordset, rst As Recordset
Dim table2 As String
Set db = CurrentDb
Set r = db.OpenRecordset("LogInTable")



strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
LogUserName = Left$(strUserName, lngLen - 1)
Else
LogUserName = ""
End If

r.MoveLast
r.AddNew
r.Fields("UserName") = UCase(LogUserName)
r.Fields("LogInDate") = Date
r.Fields("LogInTime") = Time
r.Update


End Function

Edit: The line that is giving the error is:

Set r = db.OpenRecordset("LogInTable")

KeithG · May 18, 2006

Do you have a reference set to ADODB? Try using Dim r as DAO.Recordset instead of Dim r as recordset.

gold007eye · May 18, 2006

Perfect!

That fixed the problem. What is the difference between the original method and adding the "DAO" before .recordset?

KeithG · May 18, 2006

Access was confused wether you were referencing a DAO.recordset or a ADODB.Recordset . I could tell by the methods you were using that you wanted a DAO recordset. ADO is a new technology that is replacing DAO.

gold007eye · May 18, 2006

Thanks for the explanation that makes more sense now

I am happy to have it working properly now, because it just helped me figure out another problem I was trying to solve.

Update query for capturing log out time of user (1 Viewer)

gold007eye

Registered User.

FoFa

Registered User.

gold007eye

Registered User.

pbaldy

Wino Moderator

gold007eye

Registered User.

gold007eye

Registered User.

KeithG

AWF VIP

gold007eye

Registered User.

KeithG

AWF VIP

gold007eye

Registered User.

Similar threads

Users who are viewing this thread