TimeStamp Record

[Steve Stad]

How do I get the timestamp =now() from an unbound text box on a form to
update and save in a table...So I know when the record was last updated.
e.g., I have a Master Table in DB1 linked to DB2. In db2 I have a form with
unbound text box with =now() function. Is there a simple way to get the
timestamp on update to save to the Master table. I am not a programmer.

 

[Marshall Barton]

How do I get the timestamp =now() from an unbound text box on a form to
update and save in a table...So I know when the record was last updated.
e.g., I have a Master Table in DB1 linked to DB2. In db2 I have a form with
unbound text box with =now() function. Is there a simple way to get the
timestamp on update to save to the Master table. I am not a programmer.

The text box is no help in setting the field. In fact the
text box will probably be wrong by the time it takes a user
to open the form and the time the record is saved (could be
hours if a users is called away). Since I can not imagine a
reason why users should even see the date/time a record was
saved, I recommend that you delete the text box.

The standard way to set a timestamp field is to use a line
of code in the form's BeforeUpdate event:
Me.thefield = Now

 

[ryguy7272]

May be overkill, but this is how I'd do it:
Private Sub AddRecord_Click()
Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstcontact As ADODB.Recordset
Dim strCnn As String

'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\Search and Update.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

' Open contact table.
Set rstcontact = New ADODB.Recordset
rstcontact.CursorType = adOpenKeyset
rstcontact.LockType = adLockOptimistic
rstcontact.Open "tblInternet", cnn1, , , adCmdTable

'get the new record data
rstcontact.AddNew
rstcontact!DateAdd = Now()
rstcontact.Update

'close connections
rstcontact.Close
cnn1.Close

Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub

 

[Steve Stad]

Barton - I tried this code below - but can not get the timestamp value to
update the table.

Private Sub Text51_BeforeUpdate(Cancel As Integer)
Text51 = Now()
End Sub

 

[Steve Stad]

Bruce,
I also tried this code. But it does not display the time in the form or the
table. Can you edit or recommend change so the Text box updates the
timestamp field in the table.

Private Sub Text51_BeforeUpdate(Cancel As Integer)
Text51.BeforeUpdate = Now()
End Sub

 

[John W. Vinson]

Barton - I tried this code below - but can not get the timestamp value to
update the table.

Private Sub Text51_BeforeUpdate(Cancel As Integer)
Text51 = Now()
End Sub

The textbox's BeforeUpdate event will only fire when the user types something
into that textbox - which presumably won't happen! Use the Form's BeforeUpdate
event instead.

If Text51 is a textbox bound to a Date/Time field in the table (and in the
form's recordsource) try

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me.Text51 = Now
End Sub

Note that you would do well to use meaningful names for your textboxes and
other controls, just so you'll be able to understand your own code after a few
weeks. This textbox should perhaps be named txtTimestamp.

 

[Marshall Barton]

That's NOT what we said to do. Use the FORM's BeforeUpdate,
not a text box's event.

I said to set the (record source) table FIELD's value to
Now, not a text box control.

Did you understand what I said about a text box with =Now()
being hours earlier than when a record is actually saved?

 

[ChrisO]

In a multi-user networked environment Now() would not be reliable. There are
reasons why a FE workstation may have its clock running at a different time
to other FE workstations. Forcing FE’s to time synchronize may not be
appropriate under some circumstances.

Code found here: -
http://msdn.microsoft.com/en-us/library/aa370612(VS.85).aspx
can be rewritten in VBA to achieve the same purpose.

Such VBA code can be written in such a way to overload the FE Now() function
but you need to be careful. The overload would only work with VBA but not
with JET so it is best to rename the function something like ServerNow().