How can I provide my database with a "this clients service date is due" message?

[Paul Dudley]

Thanks for taking an interest in this issue. I need my Staff to be able to load our Active Client Form and be told which Customers Service date is due, these Clients are on a weekly or monthly service etc. Should I be looking at the Recordset option or something else? I need directions.

Regards Paul

[preeti]

Hi,

How do you know if the client has a weekly or monthly service? Do you have a type field, date field (date user signed on) etc...

For weekly service clients, when would you like your staff to be informed? Is there a specific day for all weekly clients or does it depend on when the client signed on?

More information is required.

Preeti

[Paul Dudley]

Preeti - Clients have a commencement date and thereafter their service date falls on a regular basis, either daily,weekly or monthly from that commencement date. So I'm wondering about a message prompt but it needs to be related to a certain Client or record number,not a message that pops up with the Form loading. Do you get a sense of what I'd like to achieve? - Paul

[preeti]

Hi,

I don't exactly understand what you are asking for but...

I think what you want is kind of like a todo list, where all the clients whose service date have expired will be displayed in some kind of list so that your staff can look at these clients accounts.

Now my question is, you have the date that the service started, but how do you know when the service ends? Is it another date or a code etc...

If this is not what you are asking please correct any misinterpertations.

Preeti

[Paul Dudley]

Preeti - what about an alert that asks you if a Clients Service date has been attended to and if the answer is "yes" then it programs another service date according to the service frequency data placed on file when the job began eg,daily,monthly,bi-monthly etc. So you would have; a commencement date field, a service frequency field and an alert that works off these dates. Do you think these two(2) fields would be enough to work from ? Can you indicate the most efficient code to use ?

Regards - Paul

[preeti]

Hi,

That would be a good idea, but you could take it one step further. Let's say, that a client pops up in the list of expired service dates, then your staff does whatever they have to do, and automatically the new commencement date gets updated, so your staff does not have to think about the client twice, but only once when his service date expires. Once the client is cleared to recommence, then everything is taken care of manually.

Your code could be something like:

BEGIN OnUpdateClient
SSQL="UPDATE TABLENAME SET COMMENCEMENTDATE="
SELECT CASE FREQUENCT
CASE "WEEKLY"
SSQL = SSQL & DATE + 7
CASE "MONTHLY"
SSQL = SSQL & DATE + 30 '(YOU CAN CODE THIS TO TAKE INTO ACCOUNT THE LENGHTS OF DIFFERENCT MONTHS
CASE "BI-MONTHLY"
SSQL = SSQL & DATE + 14
END SELECT
SSQL = SSQL & "WHERE CLIENTID=" & CLIENTBEINGWORKEDON
END

HTH,

Preeti

[Paul Dudley]

Preeti - Thanks heaps.

Paul