Selecting a Record once system date is matched

[majid.pervaiz]

Dear Experts,

I am here once again to seek your support.

I have a database that has a list of processes for my organisations. Each process should be reviewed after 3 years.

Here are the fields:
Process Name (Type - Text)
Last Review Date (Type - Date)
Next Review Date (Type - Date)
Process Due for review (Type - Yes/No)

So for example, "Last review date" is 25/10/2019 the next review date will be 24/10/2022. What I want is that once the system date reaches to 24/10/2022, automatically, all the records which have this date, will be marked as "Yes" which is the field "Process Due for Review".

I really need your support in this, I have tried to put this on the different form events but it is not working.

Kindly guide me through detailed steps as I am not so expert in this.

Thank you in advance.

 

[theDBguy]

There is really no need to have a field to say "Yes." You can just use a query to tell you if a process is due.

 

[arnelgp]

add a Public function in a Module:

Public Function fnDueForReview()
Currentdb.Execute "Update [yourTableNameHere] Set [Process Due for review] = True Where [Process Due for review] =  False And [Next Review Date] = Date();"

now create a Macro (name it "Autoexec" (without quote)) that will run this function everytime you open your database:

RunCode
    Function Name    =fnDueForReview()

 

[plog]

Not only dont you need a [Process due for review] field, you don't need both [Last] and [Next] review date fields if everything is on a 3 year cycle.

Perhaps if you gave us the big picture, ultimate aim of what you really are trying to do with your data we can help you create the most efficient solution to whatever the actual issue is.

 

[majid.pervaiz]

Actually, we have 13 branches and each branch has procedures or SOPs for them. Each process has a different date of last review.
Once they enter this information in their database, they need to generate a report on a daily basis to check if there are any new procedures due for review.

Its a requirement for us to have this "Last Review Date" and "Next Review Date" to be visible to all the stakeholders.

 

[majid.pervaiz]

Dear All - any possible solution on this please.

 

[arnelgp]

i gave you my answer on post #3, but you did not try?

 

[Pat Hartman]

The experts told you your solution was incorrect and to use the date in your query to pick up the records. You don't need to populate a redundant field. Your next question will be about how to change the "yes" to "no". Then you were given a function. But what I think you were really asking was to how to run the update process, not how to write a query or a function.

You do not need to do this but I'll tell you how anyway because you still need to do this to select the records at the right point in time even though you are not adding the duplicate field

You have options.
1. Create a macro that runs the query that selects the records and do your "process". On a PC that is on 24/7, create a Windows task that opens the database specifying the macro to run
2. In your startup form, check the date. If it is the right date, open a form showing the selected records. You probably need to use a date range to accommodate the date falling on a weekend or holiday. You may need a table to log when the process was done so you don't run it every time.

 

[majid.pervaiz]

i gave you my answer on post #3, but you did not try?

Yes Arnelgp, I have tried it but it did not mark those as "yes". When I executed the macro it gives me an error "the expression you entered has a function name that microsoft access can't find"

 

[majid.pervaiz]

The experts told you your solution was incorrect and to use the date in your query to pick up the records. You don't need to populate a redundant field. Your next question will be about how to change the "yes" to "no". Then you were given a function. But what I think you were really asking was to how to run the update process, not how to write a query or a function.

You do not need to do this but I'll tell you how anyway because you still need to do this to select the records at the right point in time even though you are not adding the duplicate field

You have options.
1. Create a macro that runs the query that selects the records and do your "process". On a PC that is on 24/7, create a Windows task that opens the database specifying the macro to run
2. In your startup form, check the date. If it is the right date, open a form showing the selected records. You probably need to use a date range to accommodate the date falling on a weekend or holiday. You may need a table to log when the process was done so you don't run it every time.

Please apologies for my ignorance, I am not expert into this. Plus the database is divided into front end and back-end.
Front-end if given to all 13 branches which contains list of their procedures, they open their front end daily to check which processes are due for review today.

The back-end is with me to ensure database is working fine and no corruption etc.

And based on this "Yes" and "No" a report is generated at front-end side to determine the list of processes due for review.

 

[arnelgp]

see this demo.
make sure you "Enable macros" when you first open the db.
now check Table1 first.
notice field [Next Review Date] is set on Oct 28, 2022.
also notice field [[Process Due for Review] is "not set".

now change this date to your current date.
exit the db and re-open it.
now check [Process Due for Review].
is it set to Yes?

 

[Pat Hartman]

And based on this "Yes" and "No" a report is generated at front-end side to determine the list of processes due for review.

OK, there is logic to determine on how to set the value to yes/no. We get it. Why does that same logic not work to produce the report? The RecordSource query for the report can have criteria:

Where TheFlag = "yes"

Or, it can have the criteria:

Where NextReviewDT >= Date() + 21

The second selects the rows where the NextReviewDT is three weeks in the future. It is a moving criteria. Every time you run it, it looks three weeks into the future. The day after you run the update query to set the flag to yes/no, the values are out of date. That is why we do not store flags like this. We use the actual date criteria to select data .

But hey, do whatever you want. Arne has very kindly loaded your gun and pointed it to your head for you

 

[majid.pervaiz]

see this demo.
make sure you "Enable macros" when you first open the db.
now check Table1 first.
notice field [Next Review Date] is set on Oct 28, 2022.
also notice field [[Process Due for Review] is "not set".

now change this date to your current date.
exit the db and re-open it.
now check [Process Due for Review].
is it set to Yes?

Dear Arnelgp,

many thanks for your assistance dear.

It seems it worked but one thing is that macro is not running by itself when the database is opened but I have to run it manually.
Just to give you more details, 13 branches are using front end and the code which you have guided me I did it in the main database which is back-end.
Do I need to do this in the front end ? So when any user will open front end which will involve the database and run the macro automatically ?

thanks heaps once again

 

[arnelgp]

you put your db in Trusted Location.
or you need to Enable the Macro (yellow macro warning).

of course, you need to add the Macro to the Front-End.

 

[majid.pervaiz]

you put your db in Trusted Location.
or you need to Enable the Macro (yellow macro warning).

of course, you need to add the Macro to the Front-End.

Yeah I did it just now and it worked. I have now put a test record for tomorrow to see how this will execute tomorrow.
Sincere thanks and really appreciate your support dear and apologies for my ignorance on this since I am not a tech guy.

 

[arnelgp]

apologies

there is nothing to apologize, we all are learners.
it so happened i started early.

 

[KitaYama]

there is nothing to apologize, we all are learners.
it so happened i started early.

I've always loved the way you encourage those who are new to this world.

 

[majid.pervaiz]

see this demo.
make sure you "Enable macros" when you first open the db.
now check Table1 first.
notice field [Next Review Date] is set on Oct 28, 2022.
also notice field [[Process Due for Review] is "not set".

now change this date to your current date.
exit the db and re-open it.
now check [Process Due for Review].
is it set to Yes?

Dear Arnelgp,

it worked finally, thanks heaps for your patience with me
One additional thing, this macro will run once we open the database. And in case it’s a weekend and any procedure fall on that date where I couldn’t open the database in such scenario the records will not be marked as due for review.
What could be the possible thing we can do about it.
Thank you once again

 

[arnelgp]

you Google "Windows Task Scheduler With Micosoft Access",
like this one: HOW TO: Run Scheduled Tasks with Microsoft Access (nolongerset.com)