Recording Date and Time--More clarification

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

A couple of responses suggest setting the default value of a date time field
to
Now(). This process, however, sets the date/time of when the previous user
exited his/her entry but it may be hours before the next user makes an entry,
thus making the date/time record inaccurate. I need the date/time recorded
at the time which the next user enters his/her data into the field created by
the previous user exiting his/her record. Hope that makes sense. Thanks.
 
Hughie,

The Default Value will apply at the point where a record is first
created. As you have noted, subsequent data entry/editing into this
record will not change this value. You apparently have more than one
user of the database contributing to the entry of data into any given
record. So, it is not clear what point in the process you want to
record the Date/Time. I assume the record is saved after the first user
has finished with it, before the second user starts to add their data?
So apparently we record the time of when the data entry into the record
is "completed"? But there are more than one event of the record being
saved between the time the record is started and the time that it is
completed? So, how do we know when the record is completed?... When
there is data in every field? Or when a certain form is used for the
final data entry? Or when a button on the form is clicked? Or
something else?
 
Use the following example to populate your field with the date and time the
record is created ...

Private Sub Form_BeforeInsert(Cancel As Integer)
[YourFieldName] = Now()
End Sub

Change "YourFieldName" to the correct name of the field that is to store
this value ...

R. Hicks
 
Steve ... if you set the field or the control on the form's default value to
Now() ...
The Date and Time will be the value when the form is opened to a new record
....
The problem will be .. if the user does not start creating the record at
that moment .. the default value will not reflect the time the correct value
that the record was actually created ...
The solution I posted will reflect the actual time that the first character
is typed into the new record ....

R. Hicks
 
Maybe describing the project would help. Each successive record in the form
is used to describe key features of a behavioral event of a student in our
school. Only one user inputs to a particular record and when he/she exits
the record it is saved and uneditable by other users. But, as Rick said, the
time of the exit is the time recorded in the next record.
It's remarkable the patience you folks have with us newbies. I've
been learning to use Access for about 2 weeks now. Thanks again.
 
You understand what I'm looking for. But my form needs to be in continuous
view and Access said I couldn't attach a subform to a continuous form. I am
very, very new at this and I didn't understand how to use your suggestions,
e.g., whether "Private Sub Form_BeforeInsert" is a form or a field property
or whether (Cancel as Ingteger) was part of my solution or part of the
solution you based the model for me on. I can't overstate my appreciation of
your patience. Hope to hear back. H.

Ricky Hicks MVP said:
Use the following example to populate your field with the date and time the
record is created ...

Private Sub Form_BeforeInsertPrivate Sub Form_BeforeInsert
[YourFieldName] = Now()
End Sub

Change "YourFieldName" to the correct name of the field that is to store
this value ...

R. Hicks


Hughie said:
A couple of responses suggest setting the default value of a date time field
to
Now(). This process, however, sets the date/time of when the previous user
exited his/her entry but it may be hours before the next user makes an entry,
thus making the date/time record inaccurate. I need the date/time recorded
at the time which the next user enters his/her data into the field created by
the previous user exiting his/her record. Hope that makes sense. Thanks.
Click to expand...
Click to expand...
 
No "subform" involved here ...

Open your form (your continuous form) in design veiw ...
Open the property list for this form ...
Locate the "On Before Insert" property in this list ...
Place you cursor in this property and an ellipsis (3 dots) will appear to
the right ...
Click this ellipsis .. and choose Code Builder ...
Two lines of code will be created for you with your cursor placed between
these 2 lines ...

Add the line: [YourFieldName] = Now()

Change "YourFieldName" to the correct name of your field ...
Close the VBA code window and save the form changes ...

That should be all that is needed ...

R. Hicks

Hughie said:
You understand what I'm looking for. But my form needs to be in continuous
view and Access said I couldn't attach a subform to a continuous form. I am
very, very new at this and I didn't understand how to use your suggestions,
e.g., whether "Private Sub Form_BeforeInsert" is a form or a field property
or whether (Cancel as Ingteger) was part of my solution or part of the
solution you based the model for me on. I can't overstate my appreciation of
your patience. Hope to hear back. H.

Ricky Hicks MVP said:
Use the following example to populate your field with the date and time the
record is created ...

Private Sub Form_BeforeInsertPrivate Sub Form_BeforeInsert
[YourFieldName] = Now()
End Sub

Change "YourFieldName" to the correct name of the field that is to store
this value ...

R. Hicks


Hughie said:
A couple of responses suggest setting the default value of a date time field
to
Now(). This process, however, sets the date/time of when the previous user
exited his/her entry but it may be hours before the next user makes an entry,
thus making the date/time record inaccurate. I need the date/time recorded
at the time which the next user enters his/her data into the field created by
the previous user exiting his/her record. Hope that makes sense. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
YES!!! And your description of the process will aid me to understand the
other event titles. Thanks again. H.

Ricky Hicks MVP said:
No "subform" involved here ...

Open your form (your continuous form) in design veiw ...
Open the property list for this form ...
Locate the "On Before Insert" property in this list ...
Place you cursor in this property and an ellipsis (3 dots) will appear to
the right ...
Click this ellipsis .. and choose Code Builder ...
Two lines of code will be created for you with your cursor placed between
these 2 lines ...

Add the line: [YourFieldName] = Now()

Change "YourFieldName" to the correct name of your field ...
Close the VBA code window and save the form changes ...

That should be all that is needed ...

R. Hicks

Hughie said:
You understand what I'm looking for. But my form needs to be in continuous
view and Access said I couldn't attach a subform to a continuous form. I am
very, very new at this and I didn't understand how to use your suggestions,
e.g., whether "Private Sub Form_BeforeInsert" is a form or a field property
or whether (Cancel as Ingteger) was part of my solution or part of the
solution you based the model for me on. I can't overstate my appreciation of
your patience. Hope to hear back. H.

Ricky Hicks MVP said:
Use the following example to populate your field with the date and time the
record is created ...

Private Sub Form_BeforeInsertPrivate Sub Form_BeforeInsert
[YourFieldName] = Now()
End Sub

Change "YourFieldName" to the correct name of the field that is to store
this value ...

R. Hicks


:

A couple of responses suggest setting the default value of a date time field
to
Now(). This process, however, sets the date/time of when the previous user
exited his/her entry but it may be hours before the next user makes an entry,
thus making the date/time record inaccurate. I need the date/time recorded
at the time which the next user enters his/her data into the field created by
the previous user exiting his/her record. Hope that makes sense. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Hughie said:
A couple of responses suggest setting the default value of a date time field
to
Now(). This process, however, sets the date/time of when the previous user
exited his/her entry but it may be hours before the next user makes an entry,
thus making the date/time record inaccurate. I need the date/time recorded
at the time which the next user enters his/her data into the field created by
the previous user exiting his/her record. Hope that makes sense. Thanks.
Click to expand...

Just to add here... It has been suggested that you use BeforeInsert and while
that should work be aware that BeforeInsert has a similar condition to using the
Default. The data entry person could dirty the record and then go to lunch and
come back before actually finishing the record and saving it. In this scenario
which time do you want to capture?

If you want to capture when they *started* creating the record then BeforeInsert
is the appropriate event. If you want to capture when they saved the record
then you need to use the BeforeUpdate event instead. That event fires just a
split second before the save. The only caveat with BeforeUpdate is that it can
fire multiple times over the lifetime of a record so you need to use an If-Then
block to make sure you only apply the Now() value if the field is Null.
 
I agree Rick ...
If they want to catpture the time that the record is committed to the table
... I would use the Before Update event of the form.

I would test for a new record using ... If Me.NewRecord Then ...

R. Hicks
 
Hey, Thanks to all contributors. I think Ricky Hicks' first solution is most
useful as I want users to get in and out quick, doing all the input at the
same time. H.
 
I want users to get in and out quick, doing all the input at the
same time.
Click to expand...

as the developer, you have to keep in mind that what you want users to do,
or what you think they'll do, or what they should do - is not always what
they *do* do. so you have to make a decision based on which solution will
provide the most accurate data in ALL situations. at the very least, you
need to be clear in your mind about what the data represents - because you
may have to explain that context to managers, or auditors, or regulators...

so your choices are as described elsewhere in this thread: BeforeInsert
event, or BeforeUpdate event. you need to decide if you want the record to
be date/time stamped *when it is begun*, or when it is *saved to disk*. also
keep in mind that if you want the record to be completed all at one time,
you need to enforce that rule in the user interface and/or the table(s).
otherwise, a user could enter something in just one field in the form and
then save the record. in that scenario, even using the BeforeUpdate event
(with a test for "NewRecord") does not record the "true" date/time that the
record was completed.

the process that your database supports may not require a very precise
notation in the date/time data, in which case you don't have to think so
deeply about it. the above just illustrates why a developer's job is more
complex, and harder, than just knowing how to build the objects in a
database. :)

hth
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Time from Date in Report 5
Date Query 1
Trying to run excel vba code when cell value changes from empty to entered value. 1
Problem filtering records using date 4
Showing multiple entries in form 3
Query for Records AFTER Certain Time on a Certain DATE ?? 4
Access How to record date/time when specific field is modified in Access 2007 0
Automatic date 4

Back
Top