Schedule a Future Dated Transaction

[burrina]

I have a Check Register I am working on and of course the need arises to schedule future transactions. Here is my dilemma: I want the user to be able to enter the data if date is today, easy! If not then I need a way to store data until it is time to enter this into the Check Register then append to the register.

This is as far as I have got! Am I approaching this right? 2 Screenshots attached to help you understand. Store data into temp table until then ???


Date Criteria for schedulleddte is;
Daily
Weekly


BiWeekly
Monthly
Quarterly
Yearly




In other words, if user selects a Register Transaction to be entered into the Check register and if fields, frequency,scheduleddte, frequencyamount and freqpayee are NOT Null then transaction can be saved. If Date is today!
If scheduleddte is date() then save record Else use scheduleddte as criteria to save record for future date.

I created a new table to store data in until date criteria is met, it is named Tsavedtrans
It has the same filed names as the above.

Here is my code such as it is when I got stumped as how to proceed!
Private Sub Form_Current()
If IsNull([Bank]) Then 'Bank Account is required
MsgBox "A Bank Account For Register Must be Selected"
Me.Bank.SetFocus
End If

If Not IsNull(frequency) Or Not IsNull(scheduleddte) Or Not IsNull(frequencyamount) Or Not IsNull(freqpayee) Then
If MsgBox("prompt", vbYesNo, "Schedule a Future Transaction?") = vbYes Then
If (scheduleddte) = Date Then
DoCmd.Save
Else
If (scheduleddte) > Date Then


_____________________
FYI I created a new table to store data in until date criteria is met, it is named Tsavedtrans
It has the same field names as the tables being used for the check register.
I also created a append query named Qappendfuturetrans that appends the data from the QRegB query where TReg table and TFrequency tables are used and that is what the form CheckRegister is based on, the query QRegB. NOW this is as far as I have got.

[burrina]

Ok, here is my latest attempt at entering a New Record every day when the user has selected "Daily" as the ([frequency]) criteria.
([scheduleddte]) is a date field.

Private Sub cmdCopyRecord_Click()
If Me.NewRecord = True Then 'Checks For New Record
End If
Else
Exit Sub

If Not IsNull ([scheduleddte]) Then 'Checks For a Scheduled Date
End If
Else
If ([scheduleddte]) < Date Then 'Date must be a New Date To Continue
End If

Dim v1 As Variant 'Variable 1
Dim v2 As Variant '""
Dim v3 As Variant '""
Dim v4 As Variant '""

v1 = Me!frequency.Value 'Sets Variable Values
v2 = Me!freqpayee.Value
v3 = Me!frequencyamount.Value
v4 = Me!scheduleddte.Value

RunCommand acCmdRecordsGoToNew 'Saves New Record

Me!frequency = v1 'Copies New Variables To New Record
Me!freqpayee = v2
Me!frequencyamount = v3
Me!scheduleddte = v4

End Sub

[June7]

What is the issue - error message, wrong results, nothing happens? Have you Debug>Compiled? The If Then Else structures are all wacky, starting from the very first one. You have an If Then End If then follow with an Else that doesn't go with anything. And the same for the second structure. Maybe this is the logic you want:

Private Sub cmdCopyRecord_Click()
If Me.NewRecord = True Then 'Checks For New Record
    If Not IsNull([scheduleddte]) Then  'Checks For a Scheduled Date
        If ([scheduleddte]) < Date Then 'Date must be a New Date To Continue
            Dim v1 As Variant 'Variable 1
            Dim v2 As Variant '""
            Dim v3 As Variant '""
            Dim v4 As Variant '""
            
            v1 = Me!frequency.Value 'Sets Variable Values
            v2 = Me!freqpayee.Value
            v3 = Me!frequencyamount.Value
            v4 = Me!scheduleddte.Value
            
            RunCommand acCmdRecordsGoToNew 'Saves New Record
            
            Me!frequency = v1 'Copies New Variables To New Record
            Me!freqpayee = v2
            Me!frequencyamount = v3
            Me!scheduleddte = v4
        End If
    End If
End If
End Sub

[burrina]

Tried what you just posted, no errors as I was getting none either but record was NOT duplicated and entered into check register! Code compiles with NO errors. Nothing happens.

[June7]

Odd, compiler should error on the first Else in your original code.

Have you step-debugged my suggested code? Does the Me.NewRecord method return True when you expect? Or maybe that line should be = False? I am not sure which is correct for your needs. Same for the other If Then lines. Are the conditions correctly specified?

[burrina]

If Not IsNull([frequency]) And Date > ([scheduleddte]) Then
MsgBox "You Have Scheduled Transactions To Enter"
End If


If Me.NewRecord = True Then 'Checks For New Record
MsgBox "Hmm, A New Record Has Been Found"
Else
MsgBox "Nope, A New Record Was Not Found"
End If


Code seems to work ok, no errors. NO new Record.It tells me when I am on a New Record or not! That part works. Just to test and see if it did. I remmed out all other code to test this. Here is complete revised code:


Private Sub Form_Current()
If Not IsNull([frequency]) And Date > ([scheduleddte]) Then
MsgBox "You Have Scheduled Transactions To Enter"
End If


If Me.NewRecord = True Then 'Checks For New Record
MsgBox "Hmm, A New Record Has Been Found"
Else
MsgBox "Nope, A New Record Was Not Found"
End If
If Not IsNull([scheduleddte]) Then 'Checks For a Scheduled Date
If ([scheduleddte]) < Date Then 'Date must be a New Date To Continue
Dim v1 As Variant 'Variable 1
Dim v2 As Variant '""
Dim v3 As Variant '""
Dim v4 As Variant '""

v1 = Me!frequency.Value 'Sets Variable Values
v2 = Me!freqpayee.Value
v3 = Me!frequencyamount.Value
v4 = Me!scheduleddte.Value

RunCommand acCmdRecordsGoToNew 'Saves New Record 'IT ERRORS OUT HERE

Me!frequency = v1 'Copies New Variables To New Record
Me!freqpayee = v2
Me!frequencyamount = v3
Me!scheduleddte = v4
End If
End If



End Sub



Run-time error '2046':
The command or action 'RecordsGoToNew' isn't available now.

[June7]

This latest code is not quite what was posted earlier. The first code sample would error on the Else line.

Instead of RunCommand, I use:
DoCmd.GoToRecord , , acNewRec
However, think you will encounter the same error and I am not sure why.

Your 'checks' don't make sense with respect to the code that follows. Doesn't matter if the 'checks' result in true or false, the following code executes regardless. You either need to nest the If Then clauses or use Exit Sub. Why tell users they have scheduled transactions? Don't they already expect some? Just tell them if there aren't any.

If IsNull([frequency]) Or Date > ([scheduleddte]) Then
MsgBox "You Have No Scheduled Transactions To Enter"
Exit Sub
End If

[burrina]

With Me.RecordsetClone 'Find Scheduled Transactions
.FindFirst "scheduleddte= <=Date" 'ERRORS OUT HERE I need it to find that value in the date field

[June7]

.FindFirst "scheduleddte <= #" & Date & "#"

[burrina]

Thanks, NO errors it just does not go to that record. Anyway I can live with that. I can do a setfocus instead I think!
I now need to update my totals if a user clicks on the checkbox entertrans

Currently my calculated totals are; This is on a field with it's control source set to the below.(field name is RunBal) I now need to ADD the value of the Scheduled Transaction to it.
this amount is stored in the field frequencyamount . I have a calculated field for the totals of frequencyamount and it is named TransAmnt with its control source set to
=Sum([frequencyamount])

=RunSum([Form],"RegID",[RegID],"Amount") 'ADD frequencyamount to it.

[June7]

I am totally lost. Why would you want to save an aggregate value? Why is the frequency amount increased by scheduled transaction?

What is RunSum - a custom function?

I just don't know enough about this new process you are designing.

[burrina]

I can't figure out a way to save into the check register!!! RunBal is a calculated sum of the Balance. The frequencyamount is the amount being scheduled at some future date to be entered into the check register and it needs to be added to the running sum of the balance so the check register reflects an actual correct running balance. If a transaction is scheduled for Weekly and that day rolls around it then needs to be entered into the check register and the balance adjusted accordingly!

Here is a screenshot with a scheduled transaction entered into the check register.

[June7]

Again saving aggregate data instead of calculating when needed is at the root of this issue. Don't think I can help you with this.

Also, reinventing the wheel. There are so many existing 'check registers' out there, even one offered in your other thread https://www.accessforums.net/forms/c...sum-31006.html

[burrina]

That's ok. When you can't do something one way then you are left with no options. The raw data thing did not work. Hey, Thanks for your help, I do appreciate the assistance you give me.Maybe a light will go off later and I will think of a way. I also did download and look carefully at the other check register db but it does NOT do what I want it to do or at least the code has been removed. No way to do future date transactions.

[alansidman]

I do future date transactions all the time. If I want to see only the information through the current date and not the future, then when I run the report, there is a parameter that requests a date time frame. I use the current date as the end date and no future information is shown. I guess I don't understand what your issue with the future is. Use queries and reports with time frames to get the desired results.