Results 1 to 12 of 12
  1. #1
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370

    Record Users Entering and Exiting database

    Hello all,



    So I've seen similar request and I think this should be an easy task. I'm just not familiar with the vba lingo that's required for it.

    Background info:
    Split database
    Back end on network
    Front end on individual machines

    I have a main menu form that opens up when opening Access.

    What I'm thinking is have some vba in the OnLoad Event of the main menu that Grabs the User and Time and track this to a table.

    When the database closes(Is there an OnDatabaseClose Event?), I'd like to track the User and time as well.

    I'm guessing my table would look like this:

    Table:
    LogID(Autonumber)
    User(text)
    TimeIn(Date)
    TimeOut(Date)

    Thanks guys

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    on load of the main menu, log the person in...

    Code:
    Private Sub Form_Load()
       Post2Log  "Login","db","user login"
    end sub
    There isn't an application quit or close event for MS Access. The common approach seems to be having a hidden form open at all times and then handling any logic for the application within the unload event for that form (which will always be fired before Access completely closes).
    Post2Log "Exit","db","user quit"

    Code:
    '------------
    Public Sub Post2Log(pvEvent, pvSubEvent, pvDescr)
    '------------
    const Q = """"
    
    vUser = Environ("Username")
    
    sSql = "INSERT INTO tLogs ([Event],[subEvent],[USER],[EntryDate]) " _
    values (" & Q & pvEvent & Q & ",'" & pvSubEvent & "','" & vUser & "',#" & Now() & "#")"
    
    docmd.setwarnings false
    docmd.runSql sSql
    docmd.setwarnings true
    end sub

  3. #3
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    I can use the close event of the main form? The only way to close it is by exiting the database.

  4. #4
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    Quote Originally Posted by ranman256 View Post
    on load of the main menu, log the person in...

    Code:
    Private Sub Form_Load()
       Post2Log  "Login","db","user login"
    end sub
    There isn't an application quit or close event for MS Access. The common approach seems to be having a hidden form open at all times and then handling any logic for the application within the unload event for that form (which will always be fired before Access completely closes).
    Post2Log "Exit","db","user quit"

    Code:
    '------------
    Public Sub Post2Log(pvEvent, pvSubEvent, pvDescr)
    '------------
    const Q = """"
    
    vUser = Environ("Username")
    
    sSql = "INSERT INTO tLogs ([Event],[subEvent],[USER],[EntryDate]) " _
    values (" & Q & pvEvent & Q & ",'" & pvSubEvent & "','" & vUser & "',#" & Now() & "#")"
    
    docmd.setwarnings false
    docmd.runSql sSql
    docmd.setwarnings true
    end sub

    So in the code above, If I want to use this exact code, I need table: tLogs, with fields: [Event], [subEvent], [User], [EntryDate], [Login], [db], [user login]?

    Is the above code tracking the time in and time out?

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    yes, it puts a timestamp in the log.

  6. #6
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    Hmm, getting a syntax error:
    Code:
    sSql = "INSERT INTO tLogs ([Event],[subEvent],[USER],[EntryDate]) " _
    values (" & Q & pvEvent & Q & ",'" & pvSubEvent & "','" & vUser & "',#" & Now() & "#")"

  7. #7
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make it a single statement. I broke it in half for readability.

    sSql = "INSERT INTO tLogs ([Event],[subEvent],[USER],[EntryDate]) values (" & Q & pvEvent & Q & ",'" & pvSubEvent & "','" & vUser & "',#" & Now() & "#")"

  8. #8
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    Still getting syntax error

    Thanks for helping with this Ranman

  9. #9
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    I would recommend have the database use an autoexec macro that runs code like <Sub InitializeSystem>. And then from that sub, make the log call and load the startup form. This should prevent an oversite of the logging feature if you ever change the startup form.

  10. #10
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    Figured out the syntax error, had to remove the second to last quotes between the pound and parenthesis.

    Got it working.

    Thanks guys

  11. #11
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    Quote Originally Posted by Perceptus View Post
    I would recommend have the database use an autoexec macro that runs code like <Sub InitializeSystem>. And then from that sub, make the log call and load the startup form. This should prevent an oversite of the logging feature if you ever change the startup form.
    How would I set up the macro with the vba?

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    FYI....

    Shouldn't use spaces in object names. Only letters, numbers and the underscore.
    "User" is a reserved word in Access (Jet and SQL) and shouldn't be used as an object name.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error entering new record on a subform
    By AccessNewb11 in forum Access
    Replies: 1
    Last Post: 07-24-2014, 11:49 AM
  2. Replies: 5
    Last Post: 02-07-2014, 09:53 AM
  3. Replies: 1
    Last Post: 01-12-2013, 07:46 AM
  4. Replies: 1
    Last Post: 12-10-2012, 07:04 AM
  5. VBA code exiting no error.
    By Jmeyering in forum Programming
    Replies: 1
    Last Post: 11-12-2012, 04:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums