Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    138

    Unanswered: Automatically deposit $ in account on 1st & 15th

    I have a database that keeps track of youth checking accounts. (For our campus store).

    It updates automatically whenever a deposit is entered or a purchase is made.

    Each youth automatically gets an allowance ($5.00) on the 1st & 15th of every month.

    Is it possible, to automatically apply a $5.00 deposit on each account so that I don't have to go in and manually add each one on those dates?

    Any suggestions?

  2. #2
    Join Date
    Feb 2007
    Posts
    348
    Someone may have a better method but you could have it run something when opened that looks at Now() and find the last 1st or 15th, then checks to see if the deposits are made and makes them.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you're not wanting to be dependant on Access opening, you can schedule something via Windows Scheduler. Perhaps executing a vb script which updates the database tables. This sounds complex but it's really not as bad as it sounds.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2003
    Posts
    138
    PKStormy, can you elaborate on your suggestion, I've never used that program before - at least I don't think so.

    Thanks!

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    What I would do is

    create a new database LINK the Tables to it

    create the querys to update the deposit each time

    create a autoexec macro
    call a Function

    check if day is 1 or 15
    them run the the update
    quit the database

    open Windows Scheduled task
    add a new job
    Running the newly created database
    on the 1 and 15 day of each month.
    @ 12.01 in the morning
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Dendalee,

    Does the updatge actually have to happen on the exact day? IF so, then windows schedule will probably be the way to go (but again, that depends on your environment - also assuming the workstation containing the job is on at the correct time.

    If not, then you can always just check to see when you last updated and compare that with todays date to determine if you need to update or not. This would allow you greater flexibility but would require you to log on every once in a while to kick start the process (but not necessarily on the same day at all!)
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you want to do a certain action at a certain date/time then the only way to go is to use windows scheduler (you'll find it in the Accessories | System Tools | Scheduled Tasks)

    you create a task to start up access to run a specific macro, function or whatever.. for that you need to look at the Access command line switches

    to do that you will also need to devlop your own query or process that does the crediting of the the student accounts


    personally I'd create a query to do this
    call it from a macro (if you are happy using macros)
    then call that macro as part of the access initialisation in the task scheduler

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    You're also going to want to add a timestamp flag SOMEWHERE in your database, so that you know when you last incremented the accounts. Else, if you happen to run the incrementer more than once on the same day, it will add the money once each time you run it...

    Suppose you have a configuration table of some sort. Add two fields - an increment amount, and the lastIncrementDate.

    Load the IncrementAmount (that way, you won't have to rewrite your code when you increase the amount from $5.00 to $7.50... THen check the current date. If it is the 1st or 15th, check the LastIncrementDate. If the LastIncrementDate is the same as the current date, don't do anything. Else, issue a SQL statement:

    Update Acounts Set Balance = Balance + IncrementAmount
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by loquin
    Suppose you have a configuration table of some sort. Add two fields - an increment amount, and the lastIncrementDate.
    I'd say you go with youthID and incrementDate as your compound key personally
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •