Advance Payments

Here is an other demo.
What did I change ?
You need to run the macro once and it will make all the correct lines in the futur.

So if Dave paid in advance only 2000, you would get one line of 1200 in the current month and a new line with paid = 800 for the next month.
That way you can make a report for each month and see who has fully paid and why needs to pay more.

I guess the demo of arnelgp is just for demonstration only. You still need to make forms, input fields, reports.
 

Attachments

Grumm said:
Here is an other demo.
What did I change ?
You need to run the macro once and it will make all the correct lines in the futur.

So if Dave paid in advance only 2000, you would get one line of 1200 in the current month and a new line with paid = 800 for the next month.
That way you can make a report for each month and see who has fully paid and why needs to pay more.

I guess the demo of arnelgp is just for demonstration only. You still need to make forms, input fields, reports.
Click to expand...

Hmm no now it is producing so:banghead: many records ??
 
mr.syedadnan, you only run the macro each month. no need to put in append query the function. the function that the macro is calling is already adding (inserting) new voucher for advance payment.
 
Let's say you start with these 2 students. First one pay 6000 in advance and the other one 7000 in advance.
attachment.php


After the macro you get this :
attachment.php


For all the months the students have already paid, you get a record for that specific month.
John still needs to pay 200 in July to meet the correct monthly fee. But he can pay in advance again for the next months.

There are no many records... instead of clicking the macro each month, you already done that job and just needs to check who paid enough. If not, send a reminder that they still need x amount for the next month.
 

Attachments

  • start.png
    start.png
    5.4 KB · Views: 219
  • end.png
    end.png
    32.9 KB · Views: 222
Grumm said:
Let's say you start with these 2 students. First one pay 6000 in advance and the other one 7000 in advance.
attachment.php


After the macro you get this :
attachment.php


For all the months the students have already paid, you get a record for that specific month.
John still needs to pay 200 in July to meet the correct monthly fee. But he can pay in advance again for the next months.

There are no many records... instead of clicking the macro each month, you already done that job and just needs to check who paid enough. If not, send a reminder that they still need x amount for the next month.
Click to expand...

Thanks you both Gentlemen WORK Done with quality ...:D
 
arnelgp said:
mr.syedadnan, you only run the macro each month. no need to put in append query the function. the function that the macro is calling is already adding (inserting) new voucher for advance payment.
Click to expand...

Thanks you both Gentlemen WORK Done with quality ...:D
 
Grumm said:
Let's say you start with these 2 students. First one pay 6000 in advance and the other one 7000 in advance.
attachment.php


After the macro you get this :
attachment.php


For all the months the students have already paid, you get a record for that specific month.
John still needs to pay 200 in July to meet the correct monthly fee. But he can pay in advance again for the next months.

There are no many records... instead of clicking the macro each month, you already done that job and just needs to check who paid enough. If not, send a reminder that they still need x amount for the next month.
Click to expand...

Could you please check the insertvoucher as i think sometimes it is going wrong and producing so much records and getting hang
 
Ok sorry. Here is the correct one.

You had so many records because the "PAID" field was 0. I changed that.
Of course you can use the file from arnelgp if you just want it done month by month.
 

Attachments

Grumm said:
Ok sorry. Here is the correct one.

You had so many records because the "PAID" field was 0. I changed that.
Of course you can use the file from arnelgp if you just want it done month by month.
Click to expand...

Everything is going fine but there is problem that if a voucher generated in Feb 2016 and payment is adjusted there, then after new month voucher the system is calculating previous paid amount as well and to me this is not correct please check through attached image.

As you see in the month of Feb 16 the monthly fee was 500 and paid 500 so things equal there and in new month if same child paid 500 for current month i.e. for March 16 and 1000 for upcoming 2 months i.e. Apr & May 16 then after inserting advance there i am calling function but no action and the reason i got is the total if u see the total is from previous which is not correct
 

Attachments

  • Voucher.png
    Voucher.png
    10.4 KB · Views: 78
Grumm said:
Ok sorry. Here is the correct one.

You had so many records because the "PAID" field was 0. I changed that.
Of course you can use the file from arnelgp if you just want it done month by month.
Click to expand...



One more thing which is also too important that all those students who paid in advance suppose if Dave paid till Sep 2016 and we are now processing vouchers for Mar 2016 with append query so Dave to be not listed till Sept 2016 yes in Oct 2016. I think a field named "issuedate" will work here if you people share more time to solve this.. :rolleyes:
 
syedadnan said:
One more thing which is also too important that all those students who paid in advance suppose if Dave paid till Sep 2016 and we are now processing vouchers for Mar 2016 with append query so Dave to be not listed till Sept 2016 yes in Oct 2016. I think a field named "issuedate" will work here if you people share more time to solve this.. :rolleyes:
Click to expand...

Awaited help
 
arnelgp said:
i created a public function InsertVoucher, that takes care of Advances. you can run it manually on the immediate window or use the Macro InsertVoucher. only tested it with one student.
Click to expand...

Waiting for your help on my last thread ..:rolleyes:
 
Grumm said:
Here is an other demo.
What did I change ?
You need to run the macro once and it will make all the correct lines in the futur.

So if Dave paid in advance only 2000, you would get one line of 1200 in the current month and a new line with paid = 800 for the next month.
That way you can make a report for each month and see who has fully paid and why needs to pay more.

I guess the demo of arnelgp is just for demonstration only. You still need to make forms, input fields, reports.
Click to expand...

Waiting for your help on my last thread :rolleyes:
 
i have done overtime for this, but still need real-world testing, btw, i added another field in Voucher table, (Processed as Boolean), it is set by our function to True after each voucher creation. normally you would'nt set the value, just let the function set it for you.
Code: 
Public Function fnInsertVoucher2()
    
    Dim dblAdvance As Double
    Dim dblMonthlyFee As Double
    Dim dblAmountToPay As Double
    Dim datIssueDate As Variant
    Dim rs As dao.Recordset
    Dim db As dao.Database
    Dim strSQL As String
    Dim lngRecordToProcess As Long
    Dim lngCounter As Long
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM Voucher " & _
            "WHERE NOT (Processed);", dbOpenDynaset)
    With rs
        If Not (.BOF And .EOF) Then _
            .MoveLast: _
            .MoveFirst: _
            lngRecordToProcess = .RecordCount
            lngRecordToProcess = IIf(lngRecordToProcess = 0, 1, lngRecordToProcess)
            Call SysCmd(acSysCmdInitMeter, "Processing Voucher", lngRecordToProcess)
        While Not .EOF
            lngCounter = lngCounter + 1
            Call SysCmd(acSysCmdUpdateMeter, lngCounter)
            If ((!Advance.Value) = 0) Then
                ' no advances, just mark it as processed.
                .Edit
                !Processed.Value = True
                .Update
            
            Else
                
                dblAdvance = 0
                dblMonthlyFee = 0
                dblAmountToPay = 0
                ' loop until we exhaust the advances
                dblAdvance = !Advance.Value
                ' get monthly fee from student table
                dblMonthlyFee = !MonthlyFee.Value
                datIssueDate = Nz(!Issuedate.Value, 0)
                dblAmountToPay = dblMonthlyFee
                .Edit
                !Processed.Value = True
                If datIssueDate = 0 Then
                    datIssueDate = Date
                    !Issuedate.Value = datIssueDate
                End If
                .Update
                While dblAdvance > 0
                    If dblAdvance < dblMonthlyFee Then
                        dblAmountToPay = dblAdvance
                        dblAdvance = 0
                    Else
                        dblAmountToPay = dblMonthlyFee
                        dblAdvance = dblAdvance - dblAmountToPay
                    End If
                    ' (1). uncomment below line if you want IssueDate to always start at 1 day of the month
                    ' datIssueDate = DateSerial(Year(datIssueDate), Month(datIssueDate) + 1, 0) + 1
                    '
                    ' (2.) comment out this line if you want to use the above code (1).
                    datIssueDate = DateAdd("m", 1, datIssueDate)
                    strSQL = _
                    "INSERT INTO Voucher (" & _
                    "GR, StudentName, MonthlyFee, IssueDate, [Father Name], Class, [Mobile #], " & _
                    "[Date Of Birth], [Date Of Admission], [Paid], [Discount], PaidDate, Processed) "
                    
                    strSQL = strSQL & "VALUES (" & _
                    !GR.Value & "," & Chr(34) & !StudentName.Value & Chr(34) & "," & !MonthlyFee.Value & "," & _
                    "#" & Format(datIssueDate, "mm/dd/yyyy") & "#," & _
                    IIf(IsNull(![Father Name].Value), "NULL", Chr(34) & ![Father Name].Value & Chr(34)) & "," & _
                    IIf(IsNull(!Class.Value), "NULL", Chr(34) & !Class.Value & Chr(34)) & "," & _
                    IIf(IsNull(![Mobile #].Value), "NULL", Chr(34) & ![Mobile #].Value & Chr(34)) & "," & _
                    IIf(IsNull(![Date Of Birth].Value), "NULL", "#" & Format(![Date Of Birth].Value, "mm/dd/yyyy") & "#") & "," & _
                    IIf(IsNull(![Date Of Admission].Value), "NULL", "#" & Format(![Date Of Admission].Value, "mm/dd/yyyy") & "#") & "," & _
                    dblAmountToPay & "," & _
                    IIf(IsNull(![Discount].Value), "NULL", ![Discount].Value) & "," & _
                    IIf(IsNull(!PaidDate.Value), "NULL", "#" & Format(!PaidDate.Value, "mm/dd/yyyy") & "#") & "," & "True);"
                    
                    db.Execute strSQL
                    DBEngine.Idle dbFreeLocks
                Wend
            End If
            Call SysCmd(acSysCmdUpdateMeter, lngCounter)
            .MoveNext
        Wend
        .Close
        Call SysCmd(acSysCmdRemoveMeter)
    End With
    Set rs = Nothing
    Set db = Nothing
            
End Function
 
arnelgp said:
i have done overtime for this, but still need real-world testing, btw, i added another field in Voucher table, (Processed as Boolean), it is set by our function to True after each voucher creation. normally you would'nt set the value, just let the function set it for you.
Code: 
Public Function fnInsertVoucher2()
    
    Dim dblAdvance As Double
    Dim dblMonthlyFee As Double
    Dim dblAmountToPay As Double
    Dim datIssueDate As Variant
    Dim rs As dao.Recordset
    Dim db As dao.Database
    Dim strSQL As String
    Dim lngRecordToProcess As Long
    Dim lngCounter As Long
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM Voucher " & _
            "WHERE NOT (Processed);", dbOpenDynaset)
    With rs
        If Not (.BOF And .EOF) Then _
            .MoveLast: _
            .MoveFirst: _
            lngRecordToProcess = .RecordCount
            lngRecordToProcess = IIf(lngRecordToProcess = 0, 1, lngRecordToProcess)
            Call SysCmd(acSysCmdInitMeter, "Processing Voucher", lngRecordToProcess)
        While Not .EOF
            lngCounter = lngCounter + 1
            Call SysCmd(acSysCmdUpdateMeter, lngCounter)
            If ((!Advance.Value) = 0) Then
                ' no advances, just mark it as processed.
                .Edit
                !Processed.Value = True
                .Update
            
            Else
                
                dblAdvance = 0
                dblMonthlyFee = 0
                dblAmountToPay = 0
                ' loop until we exhaust the advances
                dblAdvance = !Advance.Value
                ' get monthly fee from student table
                dblMonthlyFee = !MonthlyFee.Value
                datIssueDate = Nz(!Issuedate.Value, 0)
                dblAmountToPay = dblMonthlyFee
                .Edit
                !Processed.Value = True
                If datIssueDate = 0 Then
                    datIssueDate = Date
                    !Issuedate.Value = datIssueDate
                End If
                .Update
                While dblAdvance > 0
                    If dblAdvance < dblMonthlyFee Then
                        dblAmountToPay = dblAdvance
                        dblAdvance = 0
                    Else
                        dblAmountToPay = dblMonthlyFee
                        dblAdvance = dblAdvance - dblAmountToPay
                    End If
                    ' (1). uncomment below line if you want IssueDate to always start at 1 day of the month
                    ' datIssueDate = DateSerial(Year(datIssueDate), Month(datIssueDate) + 1, 0) + 1
                    '
                    ' (2.) comment out this line if you want to use the above code (1).
                    datIssueDate = DateAdd("m", 1, datIssueDate)
                    strSQL = _
                    "INSERT INTO Voucher (" & _
                    "GR, StudentName, MonthlyFee, IssueDate, [Father Name], Class, [Mobile #], " & _
                    "[Date Of Birth], [Date Of Admission], [Paid], [Discount], PaidDate, Processed) "
                    
                    strSQL = strSQL & "VALUES (" & _
                    !GR.Value & "," & Chr(34) & !StudentName.Value & Chr(34) & "," & !MonthlyFee.Value & "," & _
                    "#" & Format(datIssueDate, "mm/dd/yyyy") & "#," & _
                    IIf(IsNull(![Father Name].Value), "NULL", Chr(34) & ![Father Name].Value & Chr(34)) & "," & _
                    IIf(IsNull(!Class.Value), "NULL", Chr(34) & !Class.Value & Chr(34)) & "," & _
                    IIf(IsNull(![Mobile #].Value), "NULL", Chr(34) & ![Mobile #].Value & Chr(34)) & "," & _
                    IIf(IsNull(![Date Of Birth].Value), "NULL", "#" & Format(![Date Of Birth].Value, "mm/dd/yyyy") & "#") & "," & _
                    IIf(IsNull(![Date Of Admission].Value), "NULL", "#" & Format(![Date Of Admission].Value, "mm/dd/yyyy") & "#") & "," & _
                    dblAmountToPay & "," & _
                    IIf(IsNull(![Discount].Value), "NULL", ![Discount].Value) & "," & _
                    IIf(IsNull(!PaidDate.Value), "NULL", "#" & Format(!PaidDate.Value, "mm/dd/yyyy") & "#") & "," & "True);"
                    
                    db.Execute strSQL
                    DBEngine.Idle dbFreeLocks
                Wend
            End If
            Call SysCmd(acSysCmdUpdateMeter, lngCounter)
            .MoveNext
        Wend
        .Close
        Call SysCmd(acSysCmdRemoveMeter)
    End With
    Set rs = Nothing
    Set db = Nothing
            
End Function
Click to expand...



Thanks a Million n Billion just tell me how to call this should i remove first "InsertVoucher" with this newone ?
 
yes remove the old one, and paste this one. also insert another field in Voucher table (fieldname: Processed, Boolean).

modify our InsertVoucher macro, on runcode, instead of InsertVoucher(), replace it with fnInsertVoucher2()
 
arnelgp said:
yes remove the old one, and paste this one. also insert another field in Voucher table (fieldname: Processed, Boolean).

modify our InsertVoucher macro, on runcode, instead of InsertVoucher(), replace it with fnInsertVoucher2()
Click to expand...

Thanks will reply you if found any problem..

Also please guide what could be the field nature like text or number that i have to enter in voucher table with processed and boolean
 
Last edited:
arnelgp said:
yes remove the old one, and paste this one. also insert another field in Voucher table (fieldname: Processed, Boolean).

modify our InsertVoucher macro, on runcode, instead of InsertVoucher(), replace it with fnInsertVoucher2()
Click to expand...

Ok i have made changes and system working fine but still the problem is same means i want if Dave paid in advance for 2 months so his name to be skipped for the next two months when i run append query and this is not happening actually
 

Attachments

  • Untitled.png
    Untitled.png
    12.2 KB · Views: 72
  • Demo.zip
    Demo.zip
    53.3 KB · Views: 88
you dont need to run any append query, just run the macro, alone, and it will do the appending of voucher for you.
 
arnelgp said:
you dont need to run any append query, just run the macro, alone, and it will do the appending of voucher for you.
Click to expand...


Ok but if i donot run the append query then it is difficuilt to know the exact data of students for that specific month and then this becomes difficuilt to know the un-paid students.
 
You must log in or register to reply here.

Similar threads

S
Solved Getting "No Current Record" on query that previously worked
Replies
6
Views
486
Gasman
Gasman
D
Payment in Recurring Transportation System
Replies
5
Views
859
plog
P
D
Query will not display fields on form
Replies
9
Views
617
LarryE
LarryE
D
Allocating payments to line items
2
Replies
20
Views
1,702
Pat Hartman
P
T
Query to find records not between two dates
2
Replies
24
Views
3,420
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom