PLEASE HELP WITH LOG IN FEATURE

A

AccessHelp

Good morning,

I have a table (Table A) for which I created to keep track of who has been
logging in and out of the database. In the table, I have 3 fields: LogInBy,
LogInTime and LogOutTime.

I would like the database to keep track of who (LogInBy) and when
(LogInTime) in that table when a user is logging in and the database is
opened. At the same time, when the user leaves the database, I would like
the database to have the time populated in the LogOutTime field on the same
record as LogInBy and LogInTime. For example, when I log in to the database,
the database will create a new record (Record 3) in Table A, and when I leave
the database, the database will populate the time in the LogOutTime field of
Record 3.

Should I use the Append query in On Load event of the Main Menu form? If I
do that, a new record will be created whenever the users return to the Main
Menu from other forms.

Thanks.
 
L

Lisab

If your log table has a unique id for each record, you could us it to
determine which record to update before the database is closed.

create a new module and add the following:
----------------------------------------
Global GLBL_LogInRecordID as integer

Public Function LogUserIn()
Dim TheDB As DAO.Database
Dim SourceRS As DAO.Recordset

'add new record to tblLog

Set TheDB = CurrentDb
Set SourceRS = TheDB.OpenRecordset("tblLog", dbOpenDynaset,
dbSeeChanges)


With SourceRS
.AddNew
!LogInBy = CurrentUser
!LogInTime = Now
.Update
.Bookmark = .LastModified
End With 'RecordID is the
Unique ID field name
GLBL_LogInRecordID = SourceRS![RecordID]

SourceRS.Close
End Function

Public Function LogUserOut()
Dim TheDB As DAO.Database
Dim SourceRS As DAO.Recordset

SQLStatement = "SELECT tblLog.* FROM tblLog " _
& "WHERE (((tblLog.RecordID)=" & GLBL_LogInRecordID & "));"

Set TheDB = CurrentDb
Set SourceRS = TheDB.OpenRecordset(SQLStatement, dbOpenDynaset,
dbSeeChanges)


With SourceRS
.Edit
!LogOutTime = Now
.Update
'.Bookmark = .LastModified
End With

SourceRS.Close

End Function
 
J

John Spencer

I would use a form that opens on startup and has its visible property set to
no.

In the on open code I would execute a query that set any null logout times
for the relevant loginid to the login time for that record
UPDATE logRecords SET logout = Login WHERE Logout is Null and USERID =
"CurrentUserID"
INSERT INTO LogRecords (USERID, Login) Values("CurrentUserID", Now())

Then keep the form open (with its visible property set to NO).
When the form gets closed (that entire database is closed) use vba code to
update the relevant record

UPDATE logRecords
SET logout = Now()
WHERE Logout is Null and USERID = "CurrentUserID"


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
A

AccessHelp

Hi Lisab,

Thank you very much for the code and for your help.

Lisab said:
If your log table has a unique id for each record, you could us it to
determine which record to update before the database is closed.

create a new module and add the following:
----------------------------------------
Global GLBL_LogInRecordID as integer

Public Function LogUserIn()
Dim TheDB As DAO.Database
Dim SourceRS As DAO.Recordset

'add new record to tblLog

Set TheDB = CurrentDb
Set SourceRS = TheDB.OpenRecordset("tblLog", dbOpenDynaset,
dbSeeChanges)


With SourceRS
.AddNew
!LogInBy = CurrentUser
!LogInTime = Now
.Update
.Bookmark = .LastModified
End With 'RecordID is the
Unique ID field name
GLBL_LogInRecordID = SourceRS![RecordID]

SourceRS.Close
End Function

Public Function LogUserOut()
Dim TheDB As DAO.Database
Dim SourceRS As DAO.Recordset

SQLStatement = "SELECT tblLog.* FROM tblLog " _
& "WHERE (((tblLog.RecordID)=" & GLBL_LogInRecordID & "));"

Set TheDB = CurrentDb
Set SourceRS = TheDB.OpenRecordset(SQLStatement, dbOpenDynaset,
dbSeeChanges)


With SourceRS
.Edit
!LogOutTime = Now
.Update
'.Bookmark = .LastModified
End With

SourceRS.Close

End Function
-----------------------------

Use the LogUserIn function to Log the user in
use the LogUserOut function to Log the user out

AccessHelp said:
Good morning,

I have a table (Table A) for which I created to keep track of who has been
logging in and out of the database. In the table, I have 3 fields: LogInBy,
LogInTime and LogOutTime.

I would like the database to keep track of who (LogInBy) and when
(LogInTime) in that table when a user is logging in and the database is
opened. At the same time, when the user leaves the database, I would like
the database to have the time populated in the LogOutTime field on the same
record as LogInBy and LogInTime. For example, when I log in to the database,
the database will create a new record (Record 3) in Table A, and when I leave
the database, the database will populate the time in the LogOutTime field of
Record 3.

Should I use the Append query in On Load event of the Main Menu form? If I
do that, a new record will be created whenever the users return to the Main
Menu from other forms.

Thanks.
Click to expand...
Click to expand...
 
A

AccessHelp

Hi John,

Thanks for your help. Why didn't I think of the trick on creating an
invisible form for the log-in purpose. That is simple and works great.

Thanks again.
 
A

AccessHelp

Hi John,

I just tried creating your first query, and I was getting a syntax error
"missing operator". Below is my sql code.

UPDATE tblLogInActivities SET LogOutTime = LogInTime WHERE LogOutTime is
null and LogInBy = CurrentUser()
INSERT INTO tblLogInActivities (LogInBy , LogInTime) Values (currentuser() ,
now())

Did I miss something?

Thanks.
 
A

AccessHelp

Hi Lisab,

I just tried your codes, and they worked perfectly. You are genius. Thanks
again.

Lisab said:
If your log table has a unique id for each record, you could us it to
determine which record to update before the database is closed.

create a new module and add the following:
----------------------------------------
Global GLBL_LogInRecordID as integer

Public Function LogUserIn()
Dim TheDB As DAO.Database
Dim SourceRS As DAO.Recordset

'add new record to tblLog

Set TheDB = CurrentDb
Set SourceRS = TheDB.OpenRecordset("tblLog", dbOpenDynaset,
dbSeeChanges)


With SourceRS
.AddNew
!LogInBy = CurrentUser
!LogInTime = Now
.Update
.Bookmark = .LastModified
End With 'RecordID is the
Unique ID field name
GLBL_LogInRecordID = SourceRS![RecordID]

SourceRS.Close
End Function

Public Function LogUserOut()
Dim TheDB As DAO.Database
Dim SourceRS As DAO.Recordset

SQLStatement = "SELECT tblLog.* FROM tblLog " _
& "WHERE (((tblLog.RecordID)=" & GLBL_LogInRecordID & "));"

Set TheDB = CurrentDb
Set SourceRS = TheDB.OpenRecordset(SQLStatement, dbOpenDynaset,
dbSeeChanges)


With SourceRS
.Edit
!LogOutTime = Now
.Update
'.Bookmark = .LastModified
End With

SourceRS.Close

End Function
-----------------------------

Use the LogUserIn function to Log the user in
use the LogUserOut function to Log the user out

AccessHelp said:
Good morning,

I have a table (Table A) for which I created to keep track of who has been
logging in and out of the database. In the table, I have 3 fields: LogInBy,
LogInTime and LogOutTime.

I would like the database to keep track of who (LogInBy) and when
(LogInTime) in that table when a user is logging in and the database is
opened. At the same time, when the user leaves the database, I would like
the database to have the time populated in the LogOutTime field on the same
record as LogInBy and LogInTime. For example, when I log in to the database,
the database will create a new record (Record 3) in Table A, and when I leave
the database, the database will populate the time in the LogOutTime field of
Record 3.

Should I use the Append query in On Load event of the Main Menu form? If I
do that, a new record will be created whenever the users return to the Main
Menu from other forms.

Thanks.
Click to expand...
Click to expand...
 
J

John Spencer

Yes those are two queries and need to be run separately.

This one creates a record

INSERT INTO tblLogInActivities (LogInBy , LogInTime)
Values (currentuser() , now())

This one updates existing records

UPDATE tblLogInActivities SET LogOutTime = LogInTime
WHERE LogOutTime is null and LogInBy = CurrentUser()

By the way, unless you have implemented Access Security, CurrentUser() is
going to return "Admin" every time.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
A

AccessHelp

Hi John,

Thanks for your fast response. Yes, I do have a security set up in Access.
Speaking of setting up security in Access, after the security has set up on
the FE, no one can open the FE from Windows Explorer and the FE is secured
(What does it mean by secured?)? I have created security feature on a few FE
and I still can open and get into them through Windows Explorer.

As far as the log in feature from my original post, I am a little confused
on the following query:

UPDATE logRecords SET logout = Login WHERE Logout is Null and USERID =
"CurrentUserID"

Do I need that query? What is the purpose of this query? It appears to me
that it is setting the logout time with login time.

Thanks.
 
J

John Spencer

The query handles those instances where the user managed to quit the
database without updating the logout time. The database crashed, the user
did a force quit, etc. In most cases the query would actually do nothing.
Since the datetime would already be filled in.

UPDATE logRecords
SET logout = Login
WHERE Logout is Null and USERID = CurrentUser()


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
A

AccessHelp

Where or when would I use that update query? If I use it right after the
append query on the invisible form opens, the LogOutTime field will be
populated with time. Then, when the invisible form closes, the second update
query would not work to update the logout time because the field is already
populated.

Thanks.
 
J

John Spencer

You would use it BEFORE you create the new login record. It is designed
to catch OLD records for the current user that have no logout time.
Again, it should rarely be needed and rarely do anything. The only time
it should do anything would be when a user exits from your application
and somehow bypasses the routine that is supposed to populate the logout
field.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
A

AccessHelp

John,

Thank you for your patience. Again, why didn't I think of that. That is
the way to solve the failures. You are the best.

Thanks again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

login and logout 5
Dynamic Naming of Form Button 3
Clearing combo boxes and a text box 2
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Dumb question alert! What program to use? 0
Help creating a search form in Access XP 0
"MSysCompactError", "System resource exceeded" & "You do not have the necessary permissions to use the...." 0
Capture Logout 0

Top