auto increment

[will]

Hello,
I am working on a database for a rental agency and need to increment a 'Balance' text box(unbounded)field with the rental amount(say $300.00) on the 15th of each month. In pseudocode it would be something like:

balance.setfocus
if system date = XX/15/XXXX and date isbetween (lease dates) then
balance = balance + rent
endif

Can anyone help me with the VBA for this function?

I assume that the database must be open for the function to run, but can I schedule a windows task to open and then close the database at say 6:00 am on the morning of the 15th?
Thanks!

 

[Chris RR]

I can answer part of your question. Yes, you can certainly schedule Access to open using a variety of schedulers. I've used three different ones over the years. The basic idea is to set up a macro that will run your code, then (in this case) shut down Access. The batch scheduler then opens Access, with a /x option (look that up in Help).

One problem that I've run into when scheduling stuff off hours, is that the PC in question obviously needs to be up when the job is run. "Helpful" folks who shut it down, or a need to have the computers down for security reasons, needs to be addressed. We used to have a special PC in the computer room set up for this kind of thing.

You may also find it easier to run this daily, then have your code check to see if it's the 15th. If not, it ends nicely & that's it. If it is, then it goes ahead & does whatever.

Now, as to the other part of your question, I am going to try to talk you out of what you want to do. It would be much better to recalculate the balance whenever you need to display it, than to attempt to carry a running balance amount. It is more likely to be accurate, AND it really is not going to slow your processing down all that much. The general rule in any database is not to carry a field that you can calculate.