Current Time - Datasheet subform

[Annette]

I have a datasheet subform that has a time field (short time) that has
a default value of time(). WHen I open the form, the new record row in
the datasheet shows the current time. If I do not enter anything in
this form for a few minutes, the new record will have the current time
as when the form was open.

I have an evidence database that I would like the current time to
enter when an entry is made, but if I am busy tagging information and
don't make the entry for 3 minutes, the time is off.

Any suggestions on getting the current time when the entry is made -
now when the form is opened?

 

[Wayne-I-M]

Private Sub SomeControl_AfterUpdate()
Me.TimeField.Requery
End Sub

 

[Tom Wickerath]

Hi Annette,

Try the following example in the Northwind sample database (I used the
version that ships with Access 2003, not the newer version that ships with
Access 2007).

1.) Add a field named TimeAdded (Date/Time) to the Order Details table.
2.) Add this new field to the Order Details Extended query.
3.) Add this field to the Orders Subform with a default value = Now() and
the appropriate format.

Add the following AfterUpdate procedure to the Orders Subform:

Option Compare Database
Option Explicit

Private Sub Form_AfterInsert()
On Error GoTo ProcError

Dim strSQL As String
strSQL = "UPDATE [Order Details] " _
& "SET [TimeAdded] = Now() " _
& "WHERE [OrderID] = " & Me.OrderID & " AND " _
& "[ProductID] = " & Me.ProductID

CurrentDb.Execute strSQL, dbFailOnError


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_AfterInsert..."
Resume ExitProc
End Sub


Note: The use of the optional dbFailOnError parameter requires that you have
a reference set to the DAO 3.6 Object Library.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Wayne-I-M]

HI Tom

I just tried the example you gave and it work really well. But so does this

Private Sub SomeControl_AfterUpdate()
Me.TimeField=Now()
End Sub

Is there a reason to use the MS example over the shorter "stuff". I am
always looking to learn reason for things.

Thank you
--
Wayne
Manchester, England.

Hi Annette,

Try the following example in the Northwind sample database (I used the
version that ships with Access 2003, not the newer version that ships with
Access 2007).

1.) Add a field named TimeAdded (Date/Time) to the Order Details table.
2.) Add this new field to the Order Details Extended query.
3.) Add this field to the Orders Subform with a default value = Now() and
the appropriate format.

Add the following AfterUpdate procedure to the Orders Subform:

Option Compare Database
Option Explicit

Private Sub Form_AfterInsert()
On Error GoTo ProcError

Dim strSQL As String
strSQL = "UPDATE [Order Details] " _
& "SET [TimeAdded] = Now() " _
& "WHERE [OrderID] = " & Me.OrderID & " AND " _
& "[ProductID] = " & Me.ProductID

CurrentDb.Execute strSQL, dbFailOnError


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_AfterInsert..."
Resume ExitProc
End Sub


Note: The use of the optional dbFailOnError parameter requires that you have
a reference set to the DAO 3.6 Object Library.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have a datasheet subform that has a time field (short time) that has
a default value of time(). WHen I open the form, the new record row in
the datasheet shows the current time. If I do not enter anything in
this form for a few minutes, the new record will have the current time
as when the form was open.

I have an evidence database that I would like the current time to
enter when an entry is made, but if I am busy tagging information and
don't make the entry for 3 minutes, the time is off.

Any suggestions on getting the current time when the entry is made -
now when the form is opened?

 

[Tom Wickerath]

Hi Wayne,

In the example I gave for Northwind, which control would be "SomeControl"
with your method? It seems to me that if a person later edited this record,
using SomeControl_AfterUpdate(), the indicated time would reflect the last
time edited, not the original time that the record was added. Annette asked
about the current time that the record was committed to the database.

Is there a reason to use the MS example over the shorter "stuff".

The MS example??? I wrote the code I offered, not Microsoft.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Douglas J. Steele]

Put code in the form's BeforeInsert event to reset the field's value to the
current time.

 

[Tom Wickerath]

Doug,

Doesn't the BeforeInsert event fire only one time, when the user enters the
first keystroke on a new record? If it takes the person 10 minutes, from
start to finish, to enter a new record, my understanding is that Annette
wants the time that the record was finally committed (saved) as the official
time.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Douglas J. Steele]

No, the form's BeforeInsert event doesn't fire until the record is going to
be saved.

 

[Tom Wickerath]

That's not what the Help file states. Suspecting that the Help file might be
wrong, I tested it with a Msgbox statement. Indeed, it fires as soon as the
first keystroke is entered. Is there something different about how
BeforeInsert works in Canada? <Smile>


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Douglas J. Steele]

Hmm. You know, I never noticed that before. In that case, use the
BeforeUpdate event instead:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord = True Then
Me.TimeField = Now()
End If

End Sub

 

[Wayne-I-M]

Hi Tom

Thanking for you advise. I agree with you and I should have put something
like this in the form's afterupdate


If Me.NewRecord Then
Private Sub SomeControl_AfterUpdate()
Me.TimeField=Now()
End If


Life is all about learning - well for me anyway

 

[Wayne-I-M]

ooops - that will teach me to cut and paste

should be


If Me.NewRecord Then
Me.TimeField=Now()
End If

Mind you - your example does has some error handeling where as mine does not
so I think I would need to add some

thank you again for you time

 

[Bob Quintal]

Hi Tom

Thanking for you advise. I agree with you and I should have put
something like this in the form's afterupdate


If Me.NewRecord Then
Private Sub SomeControl_AfterUpdate()
Me.TimeField=Now()
End If


Life is all about learning - well for me anyway

Almost. You'd want this in the from's Before_Update event.

 

[Tom Wickerath]

Hi Doug,

That works better. It's also easier than what I suggested. And come to think
of it, I've used this method in a database at work before. I hate it when one
re-invents the wheel, but it not as nice and round the second time!


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________