Programmable Data Entry

[Stuart]

Ok third time lucky on here.

i am working as a volunteer for the british red cross and helping them with
there public events database, i managed to sort out the conditional formating
in the training section by adding expiration date field for each of the 23
qualifications and setting the conditions so that if the value was greater
than Date() show with red background and bold white text and if value was
Date() +30 meaning current system date plus 30 days then go orange with bold
white text.

so during the last 30 days before a volunteers qualification expires it will
turn orange and once its expired it will turn red.

what i now need to do is the following which i need help with.

1. when i enter a date in a qualifications acheived field for example
voWWDate which is the field for when a volunteer acheived there Welcome
Workshop certificate then it will automatically follow a preset rule (the bit
i need help with) to enter the appropriate expiration date in field
voWWDateExp

2. if all the core qualifications for say event first aid are met meaning
those relevant field have a date in them and the corresponding expiration
fields show the qualification to still be valid then it will automatically
tick the checkbox for event first aid

its important the database knows who the first aiders are as theres another
form in the database which is used to book events and you need to drop down
the first aiders and ambulance crew people and add them to a list on that
event form so as to assign them to said event, currently this process doesnt
work for some reason.

if you need to understand the inner workings of the database please let me
know and i can provide a copy of the file if necesarry.

 

[Mike Painter]

Ok third time lucky on here.

i am working as a volunteer for the british red cross and helping
them with there public events database, i managed to sort out the
conditional formating in the training section by adding expiration
date field for each of the 23 qualifications and setting the
conditions so that if the value was greater than Date() show with red
background and bold white text and if value was Date() +30 meaning
current system date plus 30 days then go orange with bold white text.

so during the last 30 days before a volunteers qualification expires
it will turn orange and once its expired it will turn red.

what i now need to do is the following which i need help with.

1. when i enter a date in a qualifications acheived field for example
voWWDate which is the field for when a volunteer acheived there
Welcome Workshop certificate then it will automatically follow a
preset rule (the bit i need help with) to enter the appropriate
expiration date in field voWWDateExp

Create a table if you need to with the training offered and the time it is
valid for.
Get rid of the expiration date and base your forms and reports on a query
which includes a calculated field and the above table.

The calculated field on the query will be ExpiresateAdd("yyyy",
YourTrainingTable.YourTrainingexpiresField, YourDatePassed field)

That's probably the best way.
You could also use dLookup to get the date and fill the field with it.

2. if all the core qualifications for say event first aid are met
meaning those relevant field have a date in them and the
corresponding expiration fields show the qualification to still be
valid then it will automatically tick the checkbox for event first aid

I'd need to play with this but individuals have certificates and
certificates have requirements.
So individuals are in a 1 to many relationship with certificates and
certificates are in a 1 to many relationship with requirements.
All you have to do is build a query that checks for any of that set of
records that are not valid.
It could be done with looping through records (bad), dCount (better) but if
you have a large number of people it will slow you down. Queries are always
faster.
It is also a good learning step if you have done a bit with code and
queries.