Schedule a Future Dated Transaction

[burrina]

When dealing with a check register I need to enter a transaction let's say on a weekly schedule. How then do I proceed? Can't save the record into my check register it's not due yet so what do i do with it? Where do I put it? The user wants to enter a weekly transaction from Today, then what happens? How do I hold this and enter later? Does this make sense to you? I don't get how it's done! i am missing something here I guess.

[June7]

What you want reminds of what I do with on-line banking. I set up payments to be processed against my account by my bank. I have one transaction (rent) that is set up for the same amount for the same date (20th) every month. Other payments (credit cards) I manually select and schedule the amount and date each month. I have no experience coding something like this but I expect it will be complicated.

If you want to allow for various setups for scheduling (monthly, weekly, one-time), then I expect code will need to deal with the items according to the category. Have a form that will show all payees set up for automatic posting that can be reviewed when required (each morning, once a week, at will?). For the 'monthly' and 'weekly' items just ignore unless want to inactivate. For others, enter amount and date for posting to check register. Code will post transactions as appropriate for each category.

Google: Access database recurring events

[burrina]

I will have to wrap my head around that one.I have all the steps in place except for the If Date statements. Have to do some behind the scenes stuff to make it work. be fun trying. Some interesting date queries as well. Thanks for the link. I will try that after getting some rest.

[June7]

Thoughts on logic for this process:

1. table with info: PayeeID, Amount, DateStart, Frequency

2. When do you want the procedure for posting transactions to register triggered - by code in open event of default open form? Database must be closed each night then reopened each morning. Unfortunately, still depends on someone pushing a button. If this event does not occur because of absence (sick, vacation, holiday), the postings will not take place. I have one recurring event in my database (send an email) that is triggered in default form open event. I have a VBScript that opens database. I use Windows TaskManager to run the VBScript each day before 8am. Create a VBScript with Notepad and change the extension from txt to vbs. This is my script:

Option Explicit
Dim objWMIService, objProcess, colProcess
Dim strComputer, strProcessKill 
strComputer = "."
strProcessKill = "'msaccess.exe'" 
If (Month(Date()) >= 6 And Month(Date()) <= 9) Or ((Month(Date()) <= 5 Or Month(Date()) >= 10) And Day(Date()) < 8) Then

Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2") 
Set colProcess = objWMIService.ExecQuery ("Select * from Win32_Process Where Name = " & strProcessKill )
For Each objProcess in colProcess
 objProcess.Terminate()
Next 

Dim objFSO, oShell
Set objFSO = CreateObject("Scripting.FileSystemObject")
'open the Access file
Set oShell = CreateObject("WScript.Shell")
oShell.Run """C:\Program Files\Microsoft Office\Office12\msaccess.exe"" ""\\server\path\filename.accdb"""
End If

3. pseudocode for retrieving and posting payees to register - perhaps an INSERT SELECT sql action for each category:
monthly payees where Day(DateStart)=Day(Date())
only run weekly payees on each Thursday so IIf Weekday(Date()) = 5
one-time payees where StartDate = Date()

4. print checks

[burrina]

Man, that is cool. Never thought of that. I would love to know how those guys do it, Quickbooks,Quicken,MyCheckBook,etc.. all have a way and some sell for $20.00 Sheeesh!
Anyway, Thanks I will give this some serious thought. I may not be doing it right or making a lot of progress, but I sure am having a lot of fun NOT doing
it!

[June7]

I was not aware those apps had that functionality. I just go to my bank's website and schedule payments. Most are EFT, just the rent is a physical mailed check. I haven't bought checks in over 10 years.

I expect each of those apps still has to be open for the recurring event to take place. Access does have a timer capability but supposed to be a processor hog. Each form has an OnTimer event. I have never used but you might explore.

[burrina]

I have used the timer event pretty effectively to close unattended db's, i.e. front ends and to make stuff flash and music play,etc... Mt db is getting too complex thought and I am going to have to be realistic and live within my means and scale back the check register and just make it basic. I don't want to a whole accounting system, I am not qualified for that and dont have the training. Althugh back in the day I did write a cool piece of software that did payroll,invoices and so on. Still have it! Since 1999