Auto-Update Checkbox on AuditExpiry

[RowanG]

Hi guys,

First post to the forum, I have just started developing a database for work, and its been about 5 years since i've even touched the program. I need some incredibly basic help ha!

I want to Auto-Check a CheckBox when an audit expires. I have the controls:

ExpiryDate (Date), Expired? (Checkbox)

What would be the best way to Auto-Update the [Expired?] control on expiry of the audit? (i.e. IF ExpiryDate >= [Date] THEN Expired? = True) - Thinking about it, would this work?

The reason I want this to happen is to make filtering Expired audits out of reports etc. easier. If there is a better way of doing this I would be quite open to it.

Thank you for any help in advance, I'm sure there will be lots more needed.

Rowan

 

[pbaldy]

I wouldn't have the field. A query criteria similar to your IF should work:

SELECT...
FROM...
WHERE ExpiryDate >= Date()

 

[hk1]

It's true it would probably be better to avoid the checkbox since you really need to try to keep the checkbox and the expirydate synchronized. This means you are doing your work twice and taking up extra space in the database.

However, you choose to keep the checkbox, then the answer to your question is that it depends when and how you want this checkbox to get updated. Do you want it to happen on a form when the user does something? Do you want to update all records at one time with an SQL statement? If so, are you going to have someone manually do this or will you create a form with a timer running to handle this?

 

[RowanG]

Hi Gents,

Apologies for not coming back to you, how rude, I completely forgot I had posted! The database was actually put on hold briefly but I am back working on it now. Thank you for the tips, I have disposed of the checkbox and will be using a query / report to highlight expired audits to the user.