Losing recordset object with form close and docmd.quit

[Guest]

I appear to be losing a recordset object when closing the application with a
DoCmd.Quit. However when I close the form itself or use the close box of
Access, I do not lose it and everything works as I expected.

I am attempting to track logins and keep a persistent connection to my
backend database to avoid any lock file performance hits. If I can get this
working I have solved several problems (possilbe performance increase,
identify users doing ctl+alt+del for exit, auto exit if application is left
idle, report application activity, etc.).

I have reduced the code to two forms to show the behavior. The table can be
in the same database or linked. When you use the exit button on the Launch
Edit window, you will get the message that the recordset is lost. Close the
Dummy window or use the close box of the Access application and it reports it
is working. Why am I losing the recordset object and what can I do to get
around this?

The code for form frmLaunchEdit (unbound form with single command button) is:
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "frmDummy"
End Sub

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
DoCmd.Quit
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub

The code for form frmDummy (unbound form with no controls) is:
Option Compare Database
Option Explicit
Public LoginRS As Recordset

Private Sub Form_Open(Cancel As Integer)
Dim timeStamp As Date
' Capture a timestamp (date and time)
timeStamp = Date + Time
' Open a connection and add a login record for the user.
Set LoginRS = CurrentDb.OpenRecordset("tblLogins")
With LoginRS
.AddNew
!UserId = "dummy"
!userName = "test app"
!Login = timeStamp
!LastActivity = timeStamp
.Update
' Set the cursor on the added record.
.Bookmark = .LastModified
End With
End Sub

Private Sub Form_Close()
If LoginRS Is Nothing Then
MsgBox "Recrodset is gone"
Else
MsgBox "Closing..."
With LoginRS
.Edit
!logoutby = "logout trapped!"
.Update
.Close
End With
End If
End Sub

The table tblLogins is defined as:
UserId - text, indexed
UserName – text
Login – data/time, indexed
LastActivityDate – date/time
Logout – date/time
LogoutBy – text

None of the columns are required.

 

[Guest]

Okay I found the the other posts explaining that public declarations fall out
of scope with DoCmd.Quit. This "feature" makes a lot of sense.......not!!!!

But this does explain what is happening. It also apppears that I need to
clean up everything before calling DoCmdQuit. Okay, I can do this.

So why is using the close box of access giving me different results than
docmd.quit? And, do I have to worry about explicitly closing the recordset I
opened with code when I am in this condition even though I lost the original
object?

 

[Van T. Dinh]

DoCmd.Quit is only available for backward compatibility (with Access 95!).

Try using the Quit Method of the Application object instead.

Windows will most like recover the resources for the workspace when you
close the the application anyway so the Recordset will be destroyed. OTOH,
I always explicit close Recordsets and set the ObjectVariables to Nothing.
This way, I know for sure ...

 

[Dirk Goldgar]

DoCmd.Quit is only available for backward compatibility (with Access
95!).

Try using the Quit Method of the Application object instead.

Windows will most like recover the resources for the workspace when
you close the the application anyway so the Recordset will be
destroyed. OTOH, I always explicit close Recordsets and set the
ObjectVariables to Nothing. This way, I know for sure ...

Van, I'm not convinced that Bruce should use Application.Quit instead of
DoCmd.Quit. Application.Quit is much more abrupt than DoCmd.Quit -- it
terminates the application without even closing the forms. DoCmd.Quit
does at least close the forms and fire their events, even if it does
reset the variables.

Bruce, I can't really give you a detailed explanation of why there is a
difference between closing the application window and issuing
DoCmd.Quit, except that obviously there is. Here's a way to quit the
application in just the same orderly fashion as if you'd clicked File ->
Exit on the menu bar. Note: this function works in Access 2000 and
later.

'----- start of code -----
Sub CloseDatabaseAndAccess()

CommandBars("Menu Bar"). _
Controls("File"). _
Controls("Exit"). _
accDoDefaultAction

End Sub
'----- end of code -----

Paste that into a standard module, and then in the code for your Exit
button, just write

CloseDatabaseAndAccess

and your Dummy form will be closed in the normal fashion, without losing
the recordset until you close it and set it to Nothing.

 

[Van T. Dinh]

Dirk

I tested quickly (in A2002) with Application.Close and the Unload Event of
an open Form fired correctly.

But sure, if File / Exit is known to work, CommandBar is fine. However, I
tend to use:

CommandBars("Menu Bar").Controls("File").Controls("Exit").Execute

rather than "accDoDefaultAction" (which is not shown in the ObjectBrowser).
Not sure whether there is any difference between the 2 methods.

 

[Dirk Goldgar]

Dirk

I tested quickly (in A2002) with Application.Close and the Unload
Event of an open Form fired correctly.

(Application.Quit, I take it you mean. As far as I know, there is no
Application.Close method.)

Interesting. I'm using A2002 also, and I tested this before I posted,
and the form's Unload and Close events did not appear to fire. Seeing
your message, I tested it again, with the same form open, and they did
fire! I'm perplexed. Did I simply fail to see the message boxes
before, or has something changed between my first test and my second?
That's a hard one to answer at the moment.

I still maintain that Application.Quit doesn't do everything that
DoCmd.Quit does, but I can't back that up at the moment. I'll look over
the weekend for more info.

But sure, if File / Exit is known to work, CommandBar is fine.
However, I tend to use:

CommandBars("Menu Bar").Controls("File").Controls("Exit").Execute

rather than "accDoDefaultAction" (which is not shown in the
ObjectBrowser). Not sure whether there is any difference between the
2 methods.

I doubt there's a difference in this case. accDoDefaultAction is what I
learned from the code one of the other guys posted to compact the
current database, and it works here.

 

[Van T. Dinh]

Sorry. I meant Application.Quit.

I went for Application.Quit in preference to DoCmd.Quit because Access VB
Help recommends to use Application Object rather than DoCmd Object.

 

[Dirk Goldgar]

Sorry. I meant Application.Quit.

I went for Application.Quit in preference to DoCmd.Quit because
Access VB Help recommends to use Application Object rather than DoCmd
Object.

Which would be fine if their behavior is the same, but I don't think it
is. Still, I haven't proven it yet.

 

[Van T. Dinh]

It seems that you were involved in one of the threads that established that
if the Compact-On-Close is set to on, DoCmd.Quit compacts the database but
Application.Quit does not.

See http://tinyurl.com/agvx7

 

[Dirk Goldgar]

It seems that you were involved in one of the threads that
established that if the Compact-On-Close is set to on, DoCmd.Quit
compacts the database but Application.Quit does not.

See http://tinyurl.com/agvx7

How quickly we forget! LOL Yes, that's an example, but wasn't even
thinking of that thread when I was talking about differences in behavior
between DoCmd.Quit and Application.Quit. But here's another --
Application.Quit lets you silently lose data.

For example, suppose you have a table with a required field. Open the
table in datasheet view, or open a form based on that table, and begin
filling in a record, but leave that required field blank. If you now
execute DoCmd.Quit, you'll get an error message about the required field
that can't contain a Null value, and then a message about being unable
to save the record at this time and do you want to close the object
anyway or not -- and if you say No, the database isn't closed. By
contrast, if you execute Application.Quit, no messages are displayed at
all -- the object and application are simply closed, and the incomplete
record is discarded without notice of any kind.

 

[Van T. Dinh]

Thanks, Dirk.

That sounds a bit more critical than Compact-On-Close ...

 

[Van T. Dinh]

I think this may be one of those timing problems because if the Form_Unload
Event fires correctly all the times, the Record would have been saved
previously.

It does sound like there may be cases where the Form_Unload Event doesn't
have a chance to fire before Access is closed from what you posted (even
though I can't repeat the result - I tested on 3 different Forms and Unload
always fired correctly).

 

[Dirk Goldgar]

I think this may be one of those timing problems because if the
Form_Unload Event fires correctly all the times, the Record would
have been saved previously.

It does sound like there may be cases where the Form_Unload Event
doesn't have a chance to fire before Access is closed from what you
posted (even though I can't repeat the result - I tested on 3
different Forms and Unload always fired correctly).

Are you saying that Application.Quit didn't drop an unsavable record
without warning? That would astonish me, since it is 100% reproducible
for me.

If it's the firing of the Unload and Close events you're talking about,
they're firing for me now, too, so either I misread what I saw before,
or there's some timing issue at work here, as you suggest.

 

[Van T. Dinh]

No. What I wrote was that the Application.Quit fire the Unload Event
(consistently for me) and for the Unload Event to fire, the Update (Before &
After) would have fired previously. But if these are fired, then the Record
would have been saved unless Application.Quit just silently undoes the data
entry. OTOH, DoCmd.Quit offers to undo the Record and the default button is
Yes, anyway.

My feeling is that if the Record is can be saved, both Application.Quit &
DoCmd.Quit will save the Record. However, if the Record is not valid,
DoCmd.Quit will give the "Save Failed" message and offer to undo (Yes) or to
back-track while Application.Quit will simply undo silently.

Will test this theory and re-post here.

 

[Van T. Dinh]

Hi Dirk

I did a few more tests on Application.Quit which confirmed that the
Form_BeforeUpdate Event fires and Access silently undoes data entry if
validation fails (Form_AfterUpdate Event doesn't fire) and then unloads the
Form (Form_Unload fires) and then closes the application.

 

[Dirk Goldgar]

Hi Dirk

I did a few more tests on Application.Quit which confirmed that the
Form_BeforeUpdate Event fires and Access silently undoes data entry if
validation fails (Form_AfterUpdate Event doesn't fire) and then
unloads the Form (Form_Unload fires) and then closes the application.

That's what I'm seeing, too, which makes me think I just misinterpreted
what I saw before, unless the firing of the events turns on some
uncontrollable timing issue.

 

[Guest]

Thanks for the great discussion. With your help I got past the problem and
understand more about close, quit, and exit.