Forcing a clean close of forms before access quit

Question

I have an Access 2010 database which is using a form frmTimeKeeper to keep track of the users in the database. The form itsself has a width of 0 so it cannot be seen. It is the first form opened when the database opens and closes when access closes. It has 2 events, open and close:

Private Sub Form_Close()
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE tblUserLogs SET timeOut=#" & Date & " " & time() & "# WHERE id=" & UserLogID
    DoCmd.SetWarnings True
    MsgBox "time keeper close"
End Sub

Private Sub Form_Open(Cancel As Integer)
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO tblUserLogs(user, timeIn) VALUES ('" & Environ("Username") & "',#" & Date & " " & time() & "#)"
    DoCmd.SetWarnings True
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM tblUserLogs WHERE user='" & Environ("Username") & "' AND timeOut IS NULL ORDER BY id DESC")
    UserLogID = rst(0)

    DoCmd.OpenForm "frmLogin"
End Sub

UserLogID is a global varriable

Essentially the form opens (When the db opens) and writes to tblUserLogs the username and the date time. Then when the database is closed the close sub writes to that same record using UserLogID to ensure its the right record. And it works great until I use a button on another form that runs the command DoCmd.Quit acQuitPrompt which closes the DB. When this happens There is no timeOut inputted for that record. I have been able to get a msgbox to appear from the Form_Close event in frmTimeKeeper if it is before the SQL statement but not when it is after the SQL statement. Is there a way I can make all the other forms close cleanly before quiting Access?

tblUserLogs for reference:

ID    user      timeIn                          timeOut
2   Evan      7/29/2014 3:41:58 PM              7/29/2014 3:42:11 PM
3   Evan      7/29/2014 3:42:50 PM              7/29/2014 3:42:57 PM
4   Evan      7/29/2014 3:43:10 PM              7/29/2014 3:43:22 PM
5   J         7/29/2014 3:45:06 PM              7/29/2014 3:46:35 PM
6   J         7/29/2014 3:46:57 PM              7/29/2014 3:47:20 PM
7   Evan      7/30/2014 7:16:43 AM              7/30/2014 7:17:49 AM
8   Evan      7/30/2014 7:36:56 AM              7/30/2014 7:38:56 AM
9   Evan      7/30/2014 10:42:36 AM             7/30/2014 10:42:42 AM
10  Evan      7/30/2014 10:51:56 AM             7/30/2014 10:52:06 AM
11  Evan      7/30/2014 10:55:07 AM 
12  Evan      7/30/2014 11:05:25 AM             7/30/2014 11:05:42 AM
13  Evan      7/30/2014 11:06:00 AM 
14  Evan      7/30/2014 11:07:20 AM 
15  Evan      7/30/2014 11:08:21 AM 
16  Evan      7/30/2014 11:14:42 AM 
17  Evan      7/30/2014 11:17:50 AM             7/30/2014 11:18:02 AM
18  Evan      7/30/2014 11:19:28 AM 
19  Evan      7/30/2014 11:23:06 AM             7/30/2014 11:23:08 AM
20  Evan      7/30/2014 11:23:55 AM             7/30/2014 11:24:22 AM

The missing timeOut values are because of this error

Thanks

You are making some horrible mistakes stackoverflow.com/questions/11213892/… — Fionnuala
Noted and changed, I am going to have to tell my boss/mentor about this because he showed it to me... Thanks — Evan

PaulFrancis PaulFrancis · Accepted Answer · 2014-07-30T16:38:06

In the click of the button that calls the DoCmd.Quit manually close the timeKeeper form. Like,

Private Sub yourOtherButtonName_Click()
    DoCmd.Close acForm, "frm_TimeKeeper"
    DoCmd.Quit
End Sub

Forcing a clean close of forms before access quit

1 Answers