Adding a record.

[Robert Harris]

I want to have auto completed history files.

When a user completes a form for a new task request and
saves the entry into the forms table I would like a record
entry added to the history table indicating the date and
time the new entry was created.

The history files will contain the following automated
information:

Date
Time
Comment

Examples of comments: Created, Reviewed, Changed,
Completed.

The comment will be based on the action taken.

The question is how when I save the form data a record can
be created in the history table.

 

[Graham R Seach]

Robert,

I'd modify the history table to have only two fileds; DateTime (Date
datatype), and Comments (Text datatype).

Then add the following function to the form's class module:
Public Function AddHistory(sComment As String) As Bollean
Dim sSQL As String
sSQL = "INSERTY INTO tblHistory (DateTime, Comments) VALUES (#" &
Now() & "#, """ & sComment & """)"
CurrentDb.Execute sSQL, dbFailOnError
End Function

Then add the following code to the form's AfterUpdate property (the
property, not the event):
=AddHistory("Amended")

Then add the following code to the form's AfterInsert property (the
property, not the event):
=AddHistory("Added")

It is also advisable to record *who* made the changes, so the following code
might help:
http://www.pacificdb.com.au/MVP/Code/GetLoginName.htm
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia