Solved Auto-update weekly wages

[Kayleigh]

Hi
I'm sure there's a quick answer to this but cant find a suitable solution.

I'm looking to automatically append records to a table which stores transactions - some staff have standing orders set up weekly. I would like this recorded in the database automatically. I have the weekly amount stored and can also store day of week although i think it can be calculated?
So how would I design the query?

Kayleigh

 

[The_Doc_Man]

We might need a little bit more information. For example, will these orders ONLY be based on day of the week? And if that happens to be a holiday, will the update have to occur? Further, do you have to do the updates on the day that they are due or can they be either scheduled ahead or scheduled behind the actual due dates? Need to know the rules.

There is also the wrinkle that if the transaction has to be stored on the exact due date, could it ever occur that there would be no one present to run the updates due to it being a holiday or some other reason to not have the right people there at the time?

 

[arnelgp]

the Title of the Thead does not matched with the request (Wage or Order?)

 

[ebs17]

Adding one or more records in a table is easy. But in order to do this AUTOMATICALLY and promptly, you need information and events to be able to react. How do you monitor the account receiving the payments, how is the account monitored by the Access app?

You can only program something that you can do logically and practically by hand. The program only has the intelligence that the developer puts into it.

 

[The_Doc_Man]

There are ways to run something automatically in Access, but you need to decide on an invariant rule about HOW it will be activated. Without that rule, you will always worry about "double-dipping" i.e. running the rule twice. Do you then get TWO records when that happens? I'll state this in a very different way. What you ask is technically not that hard. Where the problem enters is that OPERATIONALLY you have some pitfalls. We could suggest a macro that would do this any time, either with a couple of action query runs or a RunCode step. The individual steps for this macro would be fairly simple. But who will trigger the macro? When and how is it to be triggered? Can you define it in a way to make it safe from being triggered more than once on a given day, perhaps by accident? Whenever you talk about automating something, there has to be a "safety" on the trigger. Otherwise you would shoot yourself in the foot.

 

[Pat Hartman]

In a multi-user application, anyone logging in will trigger an auto process unless you restrict it. What I do is to create a log table that logs the last day the task ran. Then in the first form that opens, usually the login or menu, I run code in the form's open event to check the last run date in the table and if it is >= 7 days since the last run, I update the table and run the query to generate the records.

Using this method, anyone opening the database triggers the generation of the records and because of the table, it only happens once.

 

[Pat Hartman]

@Kayleigh while I appreciate the like, it doesn't actually help anyone who finds this thread. Does the like mean that you will use the method I outlined or are you just thanking me for participating? It is far more helpful to someone who happens on this thread if you close it and tell us what worked Thanks

 

[Kayleigh]

Hi and apologies for the week late response. I did intend to respond in detail as soon as I resolved the issue.

So I wrote a simple script to iterate through the active staff and append to the transaction table so long as it was over a week since the last transaction (of that type) was recorded. It would also automatically iterate for previous weeks not filled in.
This script would run as soon as a user logs onto the system - without their knowledge.

This seems to be working but obviously will only know for definite when it has gone into production.

Thanks for all the pointers above

 

[Pat Hartman]

"Iterate" - sounds like you wrote a VBA code loop instead of using a query and a log table. It is also running for every user, EVERY time they log in. Full table scans get progressively slower over time as the row count grows. The log table I suggested, eliminates all that "iterating"