Date/Time Stamp for when form is closed

[jmofstead]

So, in my database, I have a form that automatically pops up when opened. It is a hidden form, but esentially it give me our user's ID, and the date/time when the enter into this database. I want to add the date/time of when the close out of the database so that I can see usage, etc...
I have created another field in the table tied to the form which is labeled: DOE (Date of Exit). I tried to do a quick VBA of On Close me.DOE = Now().
But I'm getting an error that says "The Expression you entered refers to an object that is closed or doesn't exit."
I'm not too sure how to make this work...
Any ideas?
Thanks in advance
Julie

 

[JANR]

The close event is too late, try and put it in the UnLoad_Event

JR

 

[jmofstead]

JANR - I tried that and it still give me the same error, but when I hit END and the form closes, it does give me the date/time stamp.
Any suggestions?

 

[vbaInet]

Interrupt the form's close event, perform the operation, then close the db.

 

[jmofstead]

vbaInet - pardon my novice knowledge of Access 2007, but how can I interrupt the form's event?

 

[JANR]

Private Sub Form_Unload(Cancel As Integer)
Me.DOE = Now()
End Sub

I'v tried this on a sample form and it did just that, save a Date/Time stamp in my table.

I have a form that automatically pops up when opened. It is a hidden form

I think there is the culprit. How to you close your db? I would think that this hidden form should have focus for the code to run. If you have a button on a switchboard form that closes access, try and set focus to your hidden form and explicit close this form before access closes.

DoCmd.Close acForm, "YourHiddenForm", acSaveNo

JR

 

[jmofstead]

JANR - I am just playing around with this specific form (not hidden) and when I just close this form it give me the error:
The Expression you entered refers to an object that is closed or doesn't exist.
My code is as follows:
Private Sub Form_Unload(Cancel As Integer)
Me.DOE = Now()
End Sub

I don't know why but when the error message occurs, and I hit End instead of Debug and then try to close my form again, it gives me the actual date/time stamp, but I have no idea how to work it so that i don't get that error message.

VbaInet suggest to interrupt the close event but I have no idea how to do this.
I'm sorry to be such a pill, i just wish this was a quick solution.
Thanks for all your help.

 

[JANR]

Unless som other rogues code is present I'm fresh out of ideas. You could try compact/repair to clear out some old stuff, in my end this works just as it suppose. That is I have nothing in the close event just the code in the Unload_Event.

Another approach is to refer to the DOE field on the hidden form form your main form, and set it's value at the click event of an exit button.

Private Closedb_Click()
Forms![yourHiddenForm]![DOE] = Now()
Application.Quit
End Sub

Just make sure that the DOE field/control is present on that form

JR

 

[boblarson]

Here's a working sample. There is a hidden form which gets the user information and time in and then when the database is closed, either by the form or by the application's X - the time out is written.

 

[vbaInet]

vbaInet - pardon my novice knowledge of Access 2007, but how can I interrupt the form's event?

Looks like Bob has uploaded a sample for you showing what I meant by interrupting the form from closing.