date stamping a record

[KLR]

I'm not sure if this is possible or even practical to do, but I have a
database which logs customer enquiries and has a field for the status
of the enquiry i.e. Received, Approved, Job start, Job Finish.

I need to be able to date stamp so that when a record is received, the
status date is set at Date() - no problem. However, the user will
continually update the enquiry status field and when they do, I need to
write to a date-stamp table like this:-

Enq number 1234
Received Date() - this date remains as is
Approved - 01.02.06
Job start - 02.02.06
Job finish - 03.02.06

So that for every action a user takes on the Enquiry status field, a
Date() record is made of it - for that particular enquiry number.

I hope someone understands what I am driving at and can point me in the
right direction.

Many thanks

 

[Douglas J. Steele]

Assuming you're using a form to do the updates, put code in the form's
BeforeUpdate event to write to your date-stamp table.

If you're not using a form, you're out of luck.

 

[Arvin Meyer [MVP]]

If all you need is the timestamp of the very last change, simply use the
BeforeUpdate event of the form to write an update to the field:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.txtUpdated = Now()
End Sub

If you need a running audit of the changes, you'll need a separate table
that you will write a record to. Here's some simplified code for that:


Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Handler

Dim strSQL As String
Dim db As DAO.Database
Dim rstItems As DAO.Recordset
Set db = CurrentDb

strSQL = "INSERT INTO tblItemHistory ( ItemID, LastUpdate )"
strSQL = strSQL & " VALUES (" & rstItems!ItemID & ", Now & "');"
db.Execute strSQL


Exit_Here:
' DoCmd.Hourglass False
rstItems.Close
Set rstItems = Nothing
Set db = Nothing
Exit Sub

Err_Handler:
MsgBox Error$
Me.Undo
Resume Exit_Here

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Allen Browne]

Douglas and Arvin have already explained how to do this, but earlier I
posted this response to the same question you asked in
comp.databases.ms-access:


Seems like you want to do more than just track the current status of the
job. You want to track each stage.

Since, one job will have multiple stages, you might create a related table
to track the various stages of each job. The related table would have
fields:
JobID relates to the pirmary key of your Job table
StatusID what status the job has just entered.
StatusDate the date this job entered this status.

Your Job form would have a subform where you can enter each stage as
it happens, one per row. The StatusDate can have a Default Value of:
=Date()
so the user doesn't have to type the date, just choose the Status in a
drop-down list.

If you don't want to do that, you could add several date/time fields to your
Job table for ReceivedDate, ApprovedDate, StartDate, and FinishDate, and
use the AfterUpdate event of the Status drop-down to write today's date to
the appropriate date/time field. This approach would be inferior for several
reasons:
a) It is less queryable, since there are several places you have to look to
find the date.
b) It is less reliable: if the user accidentally changes the status back to
a previous stage, it over-writes the true date for that stage with today's
date.
c) It is less flexible: if other stages are added in future, you have to
redesign the table (add more fields and change the code.)

 

[KLR]

I think the first option is what I want to go for - I have built a
subform and set the default (todays date) - my other problem is that
for each Job record created, the top status field will need to default
to Received, with the date field displaying when the record was created
and then giving option underneath with drop-down to add additional
status dates.

On slightly different note, on the same form I need to add code to the
on the AfterUpdate event of a field that will look in another field
(purchase order), and if purchase order is Null, prompt the user for a
value in that field (and not allow user to save/exit record unless a
value is added.

Can you help with this too?

Super thanks!

Kim

 

[Allen Browne]

You could use the AfterInsert event of the main form to AddNew to the
RecordsetClone of the subform. (You will need some experience with VBA to do
that.)

If the PurchaseOrder field is in the same record, it's easy enough to assign
a value to it. Use InputBox() to get a value from the user, and test that
value with IsDate().

 

[KLR]

Thanks for the PurchaseOrder field advice - worked great.

I have some VBA experience but very limited. Is the code I need to add
as follows?

Private Sub Form_AfterInsert()
[Subform].AddNew
End Sub

 

[Allen Browne]

More this kind of thing:

With Me.[Sub1].Form.RecordsetClone
.AddNew
!SomeField = 999
!MyForeignKeyField = Me.[ID]
!AnotherField = Date()
!MyOtherField = "Hello world"
.Update
End With

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the PurchaseOrder field advice - worked great.

I have some VBA experience but very limited. Is the code I need to add
as follows?

Private Sub Form_AfterInsert()
[Subform].AddNew
End Sub

You could use the AfterInsert event of the main form to AddNew to the
RecordsetClone of the subform. (You will need some experience with VBA to
do
that.)

If the PurchaseOrder field is in the same record, it's easy enough to
assign
a value to it. Use InputBox() to get a value from the user, and test that
value with IsDate().

 

[KLR]

I've had another think about what I need for this 'date stamp
function'. Ideally, there needs to be just one control on the form for
status, with code behind the AfterUpdate of the control to perform 2
functions:-

1) Change the date status field to current date.
2) Write a record to a table with following fields from form
Job; Status; Status Date

Therefore I will have another table called, say, 'Audit' containing a
record for an job number each time the status field is changed.

Theoretically, a user could change the status any number of ways, but
regardless of what changes are made, a 'stamp' of the date is always
made (behind the scenes).

So, if I had a job number 1234, the audit table would look as follows:-

1234;Received;Todays date (as default for each new record)
1234; Approved; 02.08.06
1234; In progress; 03.08.06
1234; Pending reply; 04.08.06
1234; In progress; 05.08.06
1234; Completed; 05.08.06

So my thinking is that some kind of code needs to go behind the
AfterUpdate control that will write the data needed to an audit table.
Trouble is I have no idea what code to use for this function.

The multiple field for different job status is not going to work
because a job could enter a status at more than one point in the cycle.

I hope you can advise me on this or point me to a website that can.

Many thanks - all help is gratefully received!

KLR

 

[Allen Browne]

Use the AfterUpdate event of the *form* to execute an Append query statement
to create the record in your Audit table.

You cannot tell if the status changed in Form_AfterUpdate, so you will need
to read it in Form_BeforeUdpate when the OldValue is still available, and
set a flag so you know whether to write the record in Form_BeforeUpdate.

1. In the General Declarations section of the form's module (at the top with
the Option statements):
Private mbNewStatus As Boolean

2. In the BeforeUpdate event procedure of the form:
Private Sub Form_BeforeUpdate
With Me.Status
If .Value = .OldValue Then
mbNewStatus = False
Else
mbNewStatus = True
End If
End With
End Sub

3. In the AfterUpdate event procedure of the form:
Private Sub Form_AfterUpdate
Dim strSql As String
If mbNewStatus Then
strSql = "INSERT INTO ...
Debug.Print strSql
dbEngine(0)(0).Execute strSql, dbFailOnError
mbNewStatus = False
End If
End Sub

To get the SQL statement, mock up a query using any literal value, and
switch it to SQL view to see the statement you need to produce. Remove the
Debug.Print line once you have it working: it is just so you can see your
output in the Immediate Window (Ctrl+G.)

 

[onedaywhen]

Theoretically, a user could change the status any number of ways, but
regardless of what changes are made, a 'stamp' of the date is always
made (behind the scenes).

I agree. Authorized users may not always use your form to connect to
the database (e.g. Excel, VBA, etc) so implementing the 'timestamp'
mechanism in only one front end application is unsafe (especially if
this is for audit purposes) i.e. it should be implemented at the engine
level.

Consider a simplified example. Say you wanted to maintain a 'date
inserted' column for a table:

CREATE TABLE MyTable (
key_col INTEGER NOT NULL UNIQUE,
data_col INTEGER NOT NULL,
effective_date DATETIME DEFAULT NOW() NOT NULL
);

Obviously you don't want the effective_date column to be written to
directly e.g. a user could accidentally (or otherwise) enter a far
future date; let's also say this metadata need not be exposed either,
so you wrap it in a view/query:

CREATE VIEW MyFakedTable
AS
SELECT key_col, data_col
FROM MyTable;

Revoke all permissions from the table and grant them to the view/query.


Users can now insert into the view/query and the default 'timestamp'
gets automatically applied:

INSERT INTO MyFakedTable (key_col, data_col) VALUES (1, 1);

Of course, you are more likely to want a 'last modified' date i.e. the
effective_date column is additionally maintained for updates. For
Access, a stored proc/parameterized query is required e.g.

CREATE PROCEDURE SetMyTableData (
arg_key_col INTEGER,
arg_data_col INTEGER
) AS
UPDATE MyTable
SET data_col = arg_data_col,
effective_date = NOW()
WHERE key_col = arg_key_col;

Obviously you would now revoke update permissions from the view/query.

Jamie.

--