How to use DateCreated and LastUpdated?

[malcprice]

Hi,

I wish to add two fields to show when the record was created and the other to show when the record was last updated. I am sure this is fairly simple.

Can any one help!!

Many thanks

Malc

 

[cmmrfrds]

The create date is easy enough just make it the default value for the field in table design. The change date needs to be done any place the record is changed typically in the after update event at the form level. I usually add a 3rd field to my tables to capture the user that made the change.

In Access 2000 use Now() as the date.

 

[malcprice]

Is it the same for Access 97?

 

[cmmrfrds]

Now() should work in Access 97 also.

 

[malcprice]

So how do you set this up?

What & where do I place the code for this to work?

Many thanks

Malc

 

[cmmrfrds]

Assuming userID and changedDate are field names in your table. This catches a change to the record otherwise the creation date is set as a default value when the record is defined.
Default Value Now()

Private Sub Form_AfterUpdate()
Me.userID = CurrentUser()
Me.changedDate = Now()
End Sub

 

[malcprice]

Ok the creation date works but the Change date does not work. It returns an error of "method or data member not found"

What should I do?

Many thanks

Malc

 

[cmmrfrds]

Assuming you spelled the field names correctly there are 2 methods CurrentUser() and Now(). Now() worked as the default value so it is unlikely the problem. Comment out the line on the current user and see if it runs. If so, then look under help to find an alternative to currentuser().

 

[malcprice]

It works many thanks

 

[malcprice]

I now have a problem when the date is changed in the LastModified field.

Once you change something and then try and go on to another record the database seizes up and states that you are at the end of the recordset - yet the number of records in the database is 3000+

What do you do to fix this problem?

Many thanks

Malc

 

[cmmrfrds]

Are you in a Form tabbing through records? Are you in a vba code loop? There is not enough information to suggest a solution. Probably better to issue a new post defining the problem specifically.