Sorry, Pam: the idea is to close the bound form first, since it could be
dirty with a record that needs to be saved in the same table.
I'm sorry for being slow. Where would I put this code if I close the
hidden form first?
The update query is dead easy:
strSql = "UPDATE Table1 SET StopTime = Now() WHERE StopTime Is
Null;"
dbEngine(0)(0).Execute strSql, dbFailOnError
You probably want to close the form first (so as not to get
concurrency problems.)
Allen,
I'm sorry for the confusion. Based on your statements below, I'll
try to clarify.
I do have two forms.
- a form where users enter jobs
Yes, they enter name, start time, and type of work performed for
selected job number.
- a hidden form where you are trying to prevent them closing the
database if the job lacks a close time.
Yes, it is hidden and is used so that when they start another job it
will popup reminding them they are still logged on with start time
entered and stop time empty. I wanted to use this same form when
they close the database.
So you already have the jobs entered and saved without a close time,
Yes, the user logs onto a specific job, clicks all necessary info
including start time and logs off to start work on shop floor. When
user completes task, he is supposed to come back to computer and
select his name and job number and it will open form to enter stop
time.
But this is where the problem is, some will leave for the day without
logging off the computer and time continually runs until the next
time they log on. They click stop time, which is set to Now() with
double click, and never bother to tell anyone until it shows up on
some report a month later.
An alternative approach might be to exeute an Update query that sets
the StopTime to Now() for all records where it is null.
I had originally thought about this, searched thru posts and came up
with the Unload event for hidden forms and since I already had one in
place for catching between jobs why not use it for when the computer
is shut down.
Can you tell me how to set up the Update query (I've only rarely used
them) to set StopTime to Now() upon closing? I like this idea better
anyway.
If this is a multi-user database, there is also the question of how
you identify the correct records that each user must fill in before
they can close.
I don't care if whoever shuts down the computer in the evening is the
one to log off another user's time, as long as it doesn't run for
days (like thru the weekend).
Thanks again for your help,
Pam
Okay, I'm a bit confused here.
Reading back through your thread, it seems you have 2 forms:
- a form where users enter jobs
- a hidden form where you are trying to prevent them closing the
database if the job lacks a close time.
So you already have the jobs entered and saved without a close time,
and you are now trying to insist that the database cannot be closed
unless the user goes back and adds a close time to those jobs?
If that's the idea, your hidden form would do something like this:
Private Sub Form_Unload(Cancel As Integer)
If Not IsNull(DLookup("ID", "JobTable", "[StopTime] Is Null"))
Then
Cancel = True
MsgBox "You must enter a Stop Time in all records before you
can close the database."
Docmd.OpenForm "frmJob",, "[StopTime] Is Null", _
WindowMode:=acDialog
End If
End Sub
This kind of thing bothers me: I think there is a temptation to just
crash out of Access or turn the computer off rather than enter all
the required data. As you probably know that's a good way to corrupt
the database. An alternative approach might be to exeute an Update
query that sets the StopTime to Now() for all records where it is
null.
If this is a multi-user database, there is also the question of how
you identify the correct records that each user must fill in before
they can close.
Allen,
Your last comment stated "so you are notified that the record was
not saved". I don't want the user to see a message that the record
was not saved with an "ok" button to press. These users will press
okay and go on about their business. I want them to be required to
put time in the "StopTime" field for any jobs left running before
quitting the database.
Far be it from me to question your suggestions (most of this is
Greek to me anyway), but will you please explain the posts I've
read about using a hidden form with the Unload event for anything
that has to do with procedures before closing a database. I really
thought I was headed in the right direction.
I appreciate your help and time.
Pam
I presume that prevented the bad records being saved to the table,
but it failed to report this to you.
That's a long-standing bug in Access. For details, see:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html
The solution is to add code to the command button that closes the
form to explicitly save the record before you close the form. That
generates a trappable error, and so you are notified that the
record was not saved.
Allen,
Thanks for replying. I used the same code that I listed in my
first message and applied it to the BeforeUpdate event procedure
and tested with two open "StopTime" entries and closed the db.
It didn't catch that these two entries were not complete before
closing.
Do you have any ideas or suggestions?
Thanks again,
Pam
Use the BeforeUpdate event procedure of the form (not text box),
rather than the form's Unload event.
UnLoad is too late: by that stage the bad record has already
been saved.
Hi,
I'm trying to get users to fill in stop time on a time tracking
database before closing database. I have a hidden form that is
used as a popup when they forget they have start time entered
on one job and try to start time on another job. I'm using the
UnloadEvent on this same form to pop up when they close out of
the database. It works well for one record if I fill in the
stop time, but it allows me to close when there are other
records with stop time still open.
Private Sub Form_Unload(Cancel As Integer)
If IsNull([StopTime]) Then
Cancel = True
DoCmd.OpenForm "fWorkLogHiddenOpen"
Me.StopTime.SetFocus
End If
End Sub
If anyone has any suggestions to close all open jobs before
exiting db, I would appreciate hearing them.
