Time stamping the time a record was added to a table 
Author Message
 Time stamping the time a record was added to a table

Can somebody please enlighten me as to a method for time-stamping a record
that was added to a table?

I have tried many scenarios but none of them have worked. I have searched
around for hours and haven't found any (working) method to accomplish this.
Here is what I have tried:
____________________________________
This is from DAO Object Model, p. 188
Description:
Returns the date and time the recordset was last updated--in other words,
when the data in the base table(s) was last changed. The changes can be made
either in the interface or in code (unlike the LastModified property).

VBA Code:
<code>
strTable = "Categories"
strDBName = "D:\Documents\Northwind.mdb"
Set dbs = OpenDatabase(strDBName)
Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
<code>
Debug.Print "Last Updated: " & rst.LastUpdated
____________________________________

Here is the Access help file definition:
*LastUpdated -- returns the date and time of the most recent change made to
an object, or to a base table if the object is a table-type Recordset object
(Microsoft Jet workspaces only).

So I have tried to incorporate the above code sample in to my code below,
and the ONLY return value I get for the LastUpdated property is dependent
upon when I have closed out of a table, and re-opened it. THAT is the
LastUpdated time, not "when the data in the base table(s) was last changed"
according to the help file & the book.

Plus, theoretically, wouldn't I have to UPDATE the recordset *first*, before
I could get the LastUpdated property? In other words:

'***************
With rs
    !strField1 = Trim(Value1)
    !numField2 = Trim(Value2)
    !strField3 = Time_Record_Was_Added
'    etc.
End With

rs.Update

    !strField3 = Time_Record_Was_Added  ' <= Isn't this where the code
should be, in theory.

rs.Close
'***************

I must be missing something. Somebody please help.
Thank you.
Tim

Here is my code.

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine.OpenDatabase(strDBPath & strDBName)
Set rs = db.OpenRecordset("tblName")

rs.AddNew

With rs
    !strField1 = Trim(Value1)
    !numField2 = Trim(Value2)
    !strField3 = Time_Record_Was_Added_Goes_Here
'    etc.
End With

rs.Update
rs.Close

Set db = Nothing
Set rs = Nothing

End Sub



Sun, 27 Apr 2003 03:00:00 GMT  
 Time stamping the time a record was added to a table
The way to do this is to use a form to enter the data, and then you have an
event already there, BeforeInsert, that you can place some code in:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me!AddTime=Now
End Sub

This assumes that you have a Date/Time field in the table called AddTime.
--
Jon

http://www.applecore99.freeserve.co.uk


Quote:

> Can somebody please enlighten me as to a method for time-stamping a record
> that was added to a table?

> I have tried many scenarios but none of them have worked. I have searched
> around for hours and haven't found any (working) method to accomplish
this.
> Here is what I have tried:
> ____________________________________
> This is from DAO Object Model, p. 188
> Description:
> Returns the date and time the recordset was last updated--in other words,
> when the data in the base table(s) was last changed. The changes can be
made
> either in the interface or in code (unlike the LastModified property).

> VBA Code:
> <code>
> strTable = "Categories"
> strDBName = "D:\Documents\Northwind.mdb"
> Set dbs = OpenDatabase(strDBName)
> Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
> <code>
> Debug.Print "Last Updated: " & rst.LastUpdated
> ____________________________________

> Here is the Access help file definition:
> *LastUpdated -- returns the date and time of the most recent change made
to
> an object, or to a base table if the object is a table-type Recordset
object
> (Microsoft Jet workspaces only).

> So I have tried to incorporate the above code sample in to my code below,
> and the ONLY return value I get for the LastUpdated property is dependent
> upon when I have closed out of a table, and re-opened it. THAT is the
> LastUpdated time, not "when the data in the base table(s) was last
changed"
> according to the help file & the book.

> Plus, theoretically, wouldn't I have to UPDATE the recordset *first*,
before
> I could get the LastUpdated property? In other words:

> '***************
> With rs
>     !strField1 = Trim(Value1)
>     !numField2 = Trim(Value2)
>     !strField3 = Time_Record_Was_Added
> '    etc.
> End With

> rs.Update

>     !strField3 = Time_Record_Was_Added  ' <= Isn't this where the code
> should be, in theory.

> rs.Close
> '***************

> I must be missing something. Somebody please help.
> Thank you.
> Tim

> Here is my code.

> Dim db As DAO.Database
> Dim rs As DAO.Recordset

> Set db = DBEngine.OpenDatabase(strDBPath & strDBName)
> Set rs = db.OpenRecordset("tblName")

> rs.AddNew

> With rs
>     !strField1 = Trim(Value1)
>     !numField2 = Trim(Value2)
>     !strField3 = Time_Record_Was_Added_Goes_Here
> '    etc.
> End With

> rs.Update
> rs.Close

> Set db = Nothing
> Set rs = Nothing

> End Sub



Sun, 27 Apr 2003 03:00:00 GMT  
 Time stamping the time a record was added to a table
That should be the form's BeforeUpdate event.

If all you want is the record creation date (not the date the record
was last modified), just set the Default Value of the field to =Date()

Quote:

> The way to do this is to use a form to enter the data, and then you have an
> event already there, BeforeInsert, that you can place some code in:

> Private Sub Form_BeforeInsert(Cancel As Integer)
>     Me!AddTime=Now
> End Sub

> This assumes that you have a Date/Time field in the table called AddTime.
> --
> Jon

> http://www.applecore99.freeserve.co.uk



> > Can somebody please enlighten me as to a method for time-stamping a record
> > that was added to a table?

> > I have tried many scenarios but none of them have worked. I have searched
> > around for hours and haven't found any (working) method to accomplish
> this.
> > Here is what I have tried:
> > ____________________________________
> > This is from DAO Object Model, p. 188
> > Description:
> > Returns the date and time the recordset was last updated--in other words,
> > when the data in the base table(s) was last changed. The changes can be
> made
> > either in the interface or in code (unlike the LastModified property).

> > VBA Code:
> > <code>
> > strTable = "Categories"
> > strDBName = "D:\Documents\Northwind.mdb"
> > Set dbs = OpenDatabase(strDBName)
> > Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
> > <code>
> > Debug.Print "Last Updated: " & rst.LastUpdated
> > ____________________________________

> > Here is the Access help file definition:
> > *LastUpdated -- returns the date and time of the most recent change made
> to
> > an object, or to a base table if the object is a table-type Recordset
> object
> > (Microsoft Jet workspaces only).

> > So I have tried to incorporate the above code sample in to my code below,
> > and the ONLY return value I get for the LastUpdated property is dependent
> > upon when I have closed out of a table, and re-opened it. THAT is the
> > LastUpdated time, not "when the data in the base table(s) was last
> changed"
> > according to the help file & the book.

> > Plus, theoretically, wouldn't I have to UPDATE the recordset *first*,
> before
> > I could get the LastUpdated property? In other words:

> > '***************
> > With rs
> >     !strField1 = Trim(Value1)
> >     !numField2 = Trim(Value2)
> >     !strField3 = Time_Record_Was_Added
> > '    etc.
> > End With

> > rs.Update

> >     !strField3 = Time_Record_Was_Added  ' <= Isn't this where the code
> > should be, in theory.

> > rs.Close
> > '***************

> > I must be missing something. Somebody please help.
> > Thank you.
> > Tim

> > Here is my code.

> > Dim db As DAO.Database
> > Dim rs As DAO.Recordset

> > Set db = DBEngine.OpenDatabase(strDBPath & strDBName)
> > Set rs = db.OpenRecordset("tblName")

> > rs.AddNew

> > With rs
> >     !strField1 = Trim(Value1)
> >     !numField2 = Trim(Value2)
> >     !strField3 = Time_Record_Was_Added_Goes_Here
> > '    etc.
> > End With

> > rs.Update
> > rs.Close

> > Set db = Nothing
> > Set rs = Nothing

> > End Sub

--
Perth, Western Australia
Tips for MS Access users at:
        http://odyssey.apana.org.au/~abrowne


Mon, 28 Apr 2003 11:13:38 GMT  
 Time stamping the time a record was added to a table
That's exactly what I was looking for. Thank you.



Quote:
> That should be the form's BeforeUpdate event.

> If all you want is the record creation date (not the date the record
> was last modified), just set the Default Value of the field to =Date()


> > The way to do this is to use a form to enter the data, and then you
have an
> > event already there, BeforeInsert, that you can place some code in:

> > Private Sub Form_BeforeInsert(Cancel As Integer)
> >     Me!AddTime=Now
> > End Sub

> > This assumes that you have a Date/Time field in the table called
AddTime.
> > --
> > Jon

> > http://www.applecore99.freeserve.co.uk



> > > Can somebody please enlighten me as to a method for time-stamping
a record
> > > that was added to a table?

> > > I have tried many scenarios but none of them have worked. I have
searched
> > > around for hours and haven't found any (working) method to
accomplish
> > this.
> > > Here is what I have tried:
> > > ____________________________________
> > > This is from DAO Object Model, p. 188
> > > Description:
> > > Returns the date and time the recordset was last updated--in
other words,
> > > when the data in the base table(s) was last changed. The changes
can be
> > made
> > > either in the interface or in code (unlike the LastModified
property).

> > > VBA Code:
> > > <code>
> > > strTable = "Categories"
> > > strDBName = "D:\Documents\Northwind.mdb"
> > > Set dbs = OpenDatabase(strDBName)
> > > Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
> > > <code>
> > > Debug.Print "Last Updated: " & rst.LastUpdated
> > > ____________________________________

> > > Here is the Access help file definition:
> > > *LastUpdated -- returns the date and time of the most recent
change made
> > to
> > > an object, or to a base table if the object is a table-type
Recordset
> > object
> > > (Microsoft Jet workspaces only).

> > > So I have tried to incorporate the above code sample in to my
code below,
> > > and the ONLY return value I get for the LastUpdated property is
dependent
> > > upon when I have closed out of a table, and re-opened it. THAT is
the
> > > LastUpdated time, not "when the data in the base table(s) was last
> > changed"
> > > according to the help file & the book.

> > > Plus, theoretically, wouldn't I have to UPDATE the recordset
*first*,
> > before
> > > I could get the LastUpdated property? In other words:

> > > '***************
> > > With rs
> > >     !strField1 = Trim(Value1)
> > >     !numField2 = Trim(Value2)
> > >     !strField3 = Time_Record_Was_Added
> > > '    etc.
> > > End With

> > > rs.Update

> > >     !strField3 = Time_Record_Was_Added  ' <= Isn't this where the
code
> > > should be, in theory.

> > > rs.Close
> > > '***************

> > > I must be missing something. Somebody please help.
> > > Thank you.
> > > Tim

> > > Here is my code.

> > > Dim db As DAO.Database
> > > Dim rs As DAO.Recordset

> > > Set db = DBEngine.OpenDatabase(strDBPath & strDBName)
> > > Set rs = db.OpenRecordset("tblName")

> > > rs.AddNew

> > > With rs
> > >     !strField1 = Trim(Value1)
> > >     !numField2 = Trim(Value2)
> > >     !strField3 = Time_Record_Was_Added_Goes_Here
> > > '    etc.
> > > End With

> > > rs.Update
> > > rs.Close

> > > Set db = Nothing
> > > Set rs = Nothing

> > > End Sub

> --
> Perth, Western Australia
> Tips for MS Access users at:
>    http://odyssey.apana.org.au/~abrowne

Sent via Deja.com http://www.deja.com/
Before you buy.


Mon, 28 Apr 2003 14:07:03 GMT  
 Time stamping the time a record was added to a table
Set the default to Now() if you want a date and time.  Date() will give
you the date and midnight.
Quote:

> That's exactly what I was looking for. Thank you.



> > That should be the form's BeforeUpdate event.

> > If all you want is the record creation date (not the date the record
> > was last modified), just set the Default Value of the field to =Date()



Mon, 28 Apr 2003 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Insert into adds 64 times the records of the table

2. Error time-stamping a record -- sometimes.

3. Time stamping a record??

4. VB 5.0 Copy Error Adds 2 Seconds to time Stamp

5. A hidden time stamp for each new record?

6. Format date/time in DTPicker to display AM/PM or 24 hour time

7. need something like foreach record in table - add record to another table

8. Run Time vs Design Time - No Current Record

9. Cannot add record to Visual Foxpro table after deleting all records in table

10. Calculating elapsed time and adding time values

11. Add Time zone to time received field in message

12. get crystal time to agree with table time format

 

 
Powered by phpBB® Forum Software