Automatically updating status

[infocusweb]

I may have been going at this all wrong, but I'm not sure.
I have a data table that stores user info including
staus: yes/no
activation date: date/time
activation end: date/time

These three fields are set when the administrator reviews account info and sets the staus to active through a form.

Is there a way I can have the status field set to no (not active) automatically after the activation_end date has been passed?

Sorry if this wasn't real clear. Thanks for your help.

 

[GunJack]

It depends on the database your using. I'm not a database admin but I believe what you are looking for is called a "trigger". If I remember correctly, a trigger is code that is executed when a particular event happens. You could probably write a trigger to update the status when the activation end field is updated. Unfortunately, I believe you have to have a db server such as MS Sql Server or Oracle to do triggers so you wouldn't be able to do this with something like Access.

Hope this helps,
GJ

 

[infocusweb]

Thanks GJ. Unfortunately I am working with MS Access. Perhaps you can save me some time. I'm thinking about alternatives. One, today I was reading about cfschedule. Is it possible to schedule a loop through these records on a daily interval, identifying any newly expired accounts and then performing an update which will set the status to "no", or will I still have to have the administrator perform the execute of this on a daily basis?

Thanks.

 

[SidianConsulting]

You could schedule a cfm template to run at whatever interval you like; then you could execute a database query like:

<cfquery...>
UPDATE mytable
SET Status = 0
WHERE ActivationEndDate >= #Now()#
</cfquery>


-Tek