Adding a record 1

[Guest_imported]

Brian_C (Visitor)
Apr 4, 2002


How would I populate a date field with today's date after a specific
field has been changed to a finish status.

I have a record such as:

ID Status CompleteDate
2 In Work

When Status is changed to Completed I want it to automatically
populate the CompleteDate field with todays date such as:

ID Status CompleteDate
2 Completed 4/4/2002

Is there a trigger or something in Access 2000 that will do this for
me??

 

[GilesC]

Hi Brian,
This is quite straight forward as long as you have based a form on those tables.
In Design view of the form select the Status field and call up its properties (right click the field). In the poperties select the Events tab at the top and within that list find an event called AfterUpdate.
Click into AfterUpdate and a builder (...) appears to the right, click that and choose Code Builder.
In the code builder type in

If me!Status="Complete" Then me!CompletedDate=Date()

Close out of the builder, return to Form view and give it a go.
Hope this helps.
Giles

 

[Guest_imported]

thanks,

I tried and it still doesnt want to populate the date. Any more suggestions?

 

[larryww]

He needs more information, not knowing whether you need a full-blown coding tutorial.

Did the if statement execute? If so, have you verified the assignment contents?

 

[GilesC]

Sorry, with out looking at your system it's going to be difficult to say what is wrong. However there are a few things you can check, sorry if you've already done this.
Can you ensure that the CompletedDate field has been set up as a Date/Time data type.
Also I once had an error in a database where it didn't recognise Date() but did recognise Now(), you could try this.
Is the CompletedDate field in the form Bound to the CompletedDate field in the table?

Also you caould add the following lines in the code to trap any errors.

After Sub Status_AferUpdate() type

On Error Goto myError

then after the IF we typed earlier type the following 5 lines

myExit:
Exit Sub
myError:
MsgBox error$
resume myExit

This won't cure it but at least we will find out if what the problem is.
Giles

 

[Guest_imported]

Thanks it now works, it needed to be bounded correctly.

Please (if possible) how can I make the same thing happen with the database where the Complete Date will always fill in today's date when Status is changed to 'Completed' by way of direct entry into the database or by way of another form (besides Access we also use Cold Fusion to populate this database). Can this be done in the Table design area with properties etc..?

 

[GilesC]

Sorry Brian but there are no Events triggered when entering data directly into a table.
You could write an Update Query to say filter for all records that are "Complete" and CompletedDate IS NULL and Update CompletedDate =Date().
It's quick and it's simple and the query can be run via code behind a button, but it can't be triggered automatically by entering directly to a table.