Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    102
    Provided Answers: 6

    Talking Answered: Add data to specific record before exiting the db

    Good morning knowledgeable friends. I hope someone is able to assist with this question.

    I have a DB accessible to multiple users over a network.

    When each person opens the DB a record is written to a table (LOGGER) recording LoginDate, LoginTime, Username, Workstation name as well as an action text field that records the text Login.

    When they exit out of the DB, a new record is written to the same table recording LoginDate, LoginTime, Username, Workstation name as well as an action text field that records the text Logout.

    The date and time information is written by setting the default value to current date & time, when the record is created.

    Code:
    Set rst = CurrentDb.OpenRecordset("Logger", dbOpenSnapshot)
    CurrentDb.Execute "INSERT INTO [Logger] ([UserName], [ComputerName], [Action])" & "VALUES (' " & UserName & "', ' " & ComputerName & "', ' " & Action & "' );"
    This works fine because I can easily see a login history for the DB over the network by running a query or just opening the LOGGER table.

    I'm thinking of changing this so I can run a query to see who is currently logged in to the DB and still provide a login history, but I don't think that the current LOGGER table structure would allow this easily. (or correctly)

    So, in order to achieve this, I'm considering adding another two fields to the LOGGER table LogoutDate and LogoutTime and have these updated automatically by writing the values to the appropriate record based on the last login occurrence for that Username and Workstation. So for each session a user creates a single record in the LOGGER table that shows all the login and logout data, rather than creating a separate record for login and a separate record for logout, as is currently the case.

    As I write this it occurs to me that this is how it should have been done in the beginning.

    What I haven't been able to figure out is how to go to the last record in the LOGGER table for a particular Username and Workstation, since they may not be the last person to log in.

    So my question is.. What code would I use to locate the last record in the LOGGER DB that matches the Username and Workstation so I can write the logout information to that record?

    Any assistance would be greatly appreciated.

  2. Best Answer
    Posted by sheusz

    "Hi Wejas

    I re-worked my approach and came up with a better solution. FYI, the code is below. and works a treat.

    Private Sub Form_Close()
    Dim rst As DAO.Recordset
    Dim CurUserName As String
    Dim CurComputerName As String
    Dim stLinkCriteria As String
    'Table fields are Logout(date), UserName(string), ComputerName(string)

    'Get username and computername for this terminal
    CurUserName = Environ("USERNAME")
    CurComputerName = Environ("COMPUTERNAME")

    Set rst = CurrentDb.OpenRecordset("Logger", dbOpenSnapshot)

    CurrentDb.Execute "UPDATE Logger SET [Logout] = Now() Where ([UserName] = '" & CurUserName & "' AND [ComputerName] = '" & CurComputerName & "' AND IsNull(Logout) );"

    End Sub

    Thanks for your assistance. "


  3. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    It's possible. What key value(s) are defined on this table?
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  4. #3
    Join Date
    Sep 2010
    Posts
    102
    Provided Answers: 6
    Quote Originally Posted by weejas View Post
    It's possible. What key value(s) are defined on this table?
    Hello Weejas

    Thanks for the reply.

    The table has the following structure,

    ID (Autonumber)
    LoginDate (Date/Time) (value acquired on FormLoad)
    LoginTime (Date/Time) (value acquired on FormLoad)
    UserName (Short Text) (value acquired using Environ("USERNAME"))
    ComputerName (Short Text) (value acquired using Environ("COMPUTERNAME")
    Action (Short Text) (either Login - On FormLoad, or LogOut - On FormClose)
    LogoutDate (Date/Time) (value acquired on FormClose)
    LogoutTime (Date/Time) (value acquired on FormClose)

    I'm actually thinking of ditching the LoginTime & LogoutTime fields and using DateDiff to calculate the Duration of the session in a query before I run a report. Is this a better idea? Less storage required.

    I think I need to parse the table on FormClose for the last entry in the table where UserName & ComputerName match the current UserName & ComputerName (in case the user logs off the PC without closing the DB and someone else logs on to that PC and opens the DB). At this point I want to write the LogoutDate and LogoutTime to that record.

  5. #4
    Join Date
    Sep 2010
    Posts
    102
    Provided Answers: 6
    Hi Wejas

    I re-worked my approach and came up with a better solution. FYI, the code is below. and works a treat.

    Private Sub Form_Close()
    Dim rst As DAO.Recordset
    Dim CurUserName As String
    Dim CurComputerName As String
    Dim stLinkCriteria As String
    'Table fields are Logout(date), UserName(string), ComputerName(string)

    'Get username and computername for this terminal
    CurUserName = Environ("USERNAME")
    CurComputerName = Environ("COMPUTERNAME")

    Set rst = CurrentDb.OpenRecordset("Logger", dbOpenSnapshot)

    CurrentDb.Execute "UPDATE Logger SET [Logout] = Now() Where ([UserName] = '" & CurUserName & "' AND [ComputerName] = '" & CurComputerName & "' AND IsNull(Logout) );"

    End Sub

    Thanks for your assistance.

Posting Permissions

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