Solved Expiry date for many certificates (1 Viewer)

[Sam Summers]

Hi,

I have a continuous form that displays records for a selected certificate based on a combo box of a parameter form.
For each certificate selected i want to display the expiry date for each persons certificate on the continuous form.
Is this possible?

Many thanks in advance

 

[arnelgp]

create a query that will display the Person name, certificate, certification date, (certification date + duration of certificate) as expiry date.

 

[Sam Summers]

Hi, thank you for replying. I was trying to display it in the list of people who have this certificate but problem is that each persons date is different?
I was trying to find a way to do it in vba but it displays the same date for everyone

 

[The_Doc_Man]

The problem is usually in the way you gather or compute the data. You need a query that picks up the person and either picks up or computes the expiration date from that information. THEN use that query as the basis for your display. Do not refer to the table.

 

[Sam Summers]

Yes its a tricky one for me as i have an intermediate table to create a many to many link as employees may have many certificates each with a different expiry date

 

[Gasman]

Yes its a tricky one for me as i have an intermediate table to create a many to many link as employees may have many certificates each with a different expiry date

So would not the issue date of the certificate be stored in those records?

 

[Sam Summers]

Yes it is

 

[Sam Summers]

So would not the issue date of the certificate be stored in those records?

yes it is. I'm going to try and see if i can add code to add each persons certificates expiry date

 

[theDBguy]

yes it is. I'm going to try and see if i can add code to add each persons certificates expiry date

How do you know when each certificate expires? If a certificate was issued today, when does it expire?

 

[Sam Summers]

How do you know when each certificate expires? If a certificate was issued today, when does it expire?

Each type of Certificate is valid for so many years. Some 2, some 5 etc.

 

[Sam Summers]

I'm trying something like this:

If Me.CertName.Value = "CSCS Gold" Then Me.ExpiryDate.Value =DateAdd("y",+1095,[Effective Date])

 

[theDBguy]

Each type of Certificate is valid for so many years. Some 2, some 5 etc.

And how do you keep track of that information? Do you have a lookup table for that data?

 

[Sam Summers]

And how do you keep track of that information? Do you have a lookup table for that data?

At the moment i only store the effective date but i have now created an expiry date field to try and get it working i used the above code in the After Update of the EffectiveDate Text box but the ExpiryDate Text box remains blank at the moment?

 

[theDBguy]

At the moment i only store the effective date but i have now created an expiry date field...

I don't think that's a good idea. Queries are the workhorse of database applications. As @arnelgp was trying to tell you earlier, you only need to create a calculated column in a query to display the expiry dates. The missing information was how do you calculate the expiration date for each certificate type. If you have the type and duration in another table, you simply join that table to your issued certificate table to calculate the expiry dates.

 

[Gasman]

I would have thought the certificate duration would be with the certificate record?
How on earth can Me.EffectiveDate.Value be = "CSCS Gold ?

 

[Sam Summers]

I would have thought the certificate duration would be with the certificate record?
How on earth can Me.EffectiveDate.Value be = "CSCS Gold ?

Ah that was a mistake should be If Me.CertName.Value = "CSCS Gold"

 

[Sam Summers]

I don't think that's a good idea. Queries are the workhorse of database applications. As @arnelgp was trying to tell you earlier, you only need to create a calculated column in a query to display the expiry dates. The missing information was how do you calculate the expiration date for each certificate type. If you have the type and duration in another table, you simply join that table to your issued certificate table to calculate the expiry dates.

Thank you everybody for your input and help - amazing!
Another forum member has just helped me solve it and put me in the right direction

 

[Gasman]

Thank you everybody for your input and help - amazing!
Another forum member has just helped me solve it and put me in the right direction

Well. do come back and post your solution please, as you never know when someone else has the same requirements.

 

[Sam Summers]

Well. do come back and post your solution please, as you never know when someone else has the same requirements.

Of course

 

[theDBguy]

Thank you everybody for your input and help - amazing!
Another forum member has just helped me solve it and put me in the right direction

Good luck with your project.