Applying payments to invoices (1 Viewer)

[jdcallejas]

Hello Everyone,
Ok, this is going to be long, I am going to try to explain what I want to do, here it goes;
I have a table invoices and a table payments, what I want to do is somehow take the payment amount of client and if the payment amount is larger than the client invoice amount, update invoice cancel to yes and subtract the invoice amount from the payment amount, goto next invoice and do the same.
Once it goes through all records, if there is not enough money to pay the last invoice than it will only pay what is available and leave that invoiced not cancel. Next time the client makes a payment it would start where it left off, finish paying that invoice and goto the next one.
I know that it has to be like an if statement with an EOF loop… But I have no idea how to begin to do this or in this case since is code I am lost, so I come to you for help…

 

[pono1]

I know you're asking about handling over- and under-payments...but do you have a junction table (InvoicePayments) for your invoices and payments? You might want to consider creating one if the following is true: One invoice can have many payments and one payment can be made against many invoices -- and you care about tracking payments at this level...

Regards,
Tim

 

[jdcallejas]

Thanks Pono1 for your response,

Well, I am not sure how to deal with this issue, I do not have a juction table on this program but I will put one if is necesary. What i have is a table called payments that it only relates to the client who is paying, I then thought I would run a code or query where it would select all invoices not canceled for this client and apply the payment.

Anyway, I will test by doing a junction table from my payment table to my invoice table and see if I can make it work. If I get stuck I will write you for some more help..

I think if I do it this way might be able to make it work like quickbooks does, are you familiar with quickbooks? The way they do it is they have a form for payments where once you select your client it shows all pending invoices, then once you enter the amount on the parrent form, you can select the invoices that you are going to pay with that amount, if the amount does not cover all invoices it will only apply the amount left of the payment to the invoice and everything else becomes a balance due.

This is kind off I want to do....

DaniBoy

 

[georgedwilkinson]

You need a junction table, as pono1 pointed out.

You will need to manually code the payment sub-routine. Are you going to give the user the option to override? You should not cancel an invoice because it is paid, just mark it paid (which you can do through the junction table).

 

[jdcallejas]

Ok guys, I have tried to do this but am lost, I put my db back to where I have been stuck... I have an invoice and an invoicedetail tables and I have my payment table, my payment table has no relations at the moment, this is where you come in, I AM SO LOST please look at my db and help me figure out how to apply payments to invoices....

I am greatfull for you help..

Daniboy

 

[jdcallejas]

sorry here is the db

 

[georgedwilkinson]

I'm looking at it now. It needs some restructuring.

 

[georgedwilkinson]

OK, this was a real quicky so you need to double check that all your rules are in place before using it.

As far as the algorithm for applying payments to invoices, that is for a different time.

 

[georgedwilkinson]

Did this work for you?

 

[jdcallejas]

Hello Mr.Wilkinson,

This helped a lot, thanks and sorry I hadnt reply to give thanks but i was still modifying this on the DB...

I created the sunform based on the juctions table you created for me. I have the invoices come up from a combobox and then I manualy apply the payment. So far this is working well. I have a little problem displaying the right invoices for that client, I had done this before but for some reason the code I use is not working on this form.

I have created a combox on my subform that gets the invoive number, on my control I set it so it would only pick the inv with clientid that matches my clientid on my parrent form. For some reason is not working..


Mr.Wilkinson, how do you think I can apply the payments automaticaly?

I thought about doing a query that just looks and the invoice amount and the payment and if they are equal or greater than the invoice amount update paid to yes if not stays unpaid.

But doing this will not keep a balance due for me.

What do you think? I have attached my DB with the changes I did.

Thank you Mr.Wilkinson,

DaniBoy

 

[georgedwilkinson]

I'll think about it. Right now I have a couple of chores to attend to in preparation for the hurricane.

I'm thinking there may be a simple way to automate. Initially, I was thinking it would require a program loop with a result set.

 

[georgedwilkinson]

OK, I looked it over and have some confusion. It's a bit late at night and I may not be thinking straight.

Your invoices are all associated to the same lease which is associated to the same 3 tenants. Additionally, one of your tenants is associated with a second lease.

I'm thinking that won't work right. Wouldn't you think that? Could be what's causing your confusion. In fact, now that I look at it closer, I'm sure that's why it's not filtering by tenant.

I'm also a bit confused about the subform...it shows invoice amounts that are much larger than the invoice amount in the invoice...looks more like the total lease amount.

So, the way you apply the payment is by manually typing in the "Payment Amount" into the "Amount Applied", correct? And I would assume that if you were to do it automatically, you'd pay off older invoices first, correct?

Pending a suitable answer to these questions, I'll try to look at it again tomorrow.

Do you live in the Gainesville area? I grew up near there and spent most of my life in the area (Jacksonville and Live Oak and a little Tallahassee).

 

[jdcallejas]

Hello Mr.Wilkinson

Yes I live in Gainesville, "The Swamp" jejeje, I also have family in Live Oak, what a small world!!!.

Ok, I deleted all my leases and just created two leases with one tenant on each lease. I checked my combox to make sure everythig was written correct and still have the problem. Once I select an Apartment Number="LeaseID" it should only show me invoices for that Apartment Number="LeaseID" and the subform but is not doing that. The way I relate to everything is by LeaseID, the tenants could always change. So I am filtering the invoices by LeaseID.


You hit the nail right on the head!!! This is exactly how I want to do the apply payment method!!! So if someone owes $500 on that month and only pays $400 that invoice still pending 100 dollars so its not maked paid. I would also have a past due amount of 100 dollars for this lease. So now he has another invoice of $500 and he pays $550 the payment would payoff the past due amount on the old invoices and only apply what is left on the newest invoice.

I hope I explained this correctly.


Something else that is driving me crazy!!! On the forms Security Deposit and Payments I have a field call Paid By or Payment by, on this field I am able to pick the tennat that is making this deposit or payment, if there are two tenants on the lease I could pick the one that is making the payment. The way they have it setup here is that both tenants can pay with diferent checks to comple the rent amount. Thats why I have that like this.

Now I dont know if is access 07 or what? If you create a payment or deposit you can pick the tenant that is making the payment or deposit, but if you navigate through the records the name disapears!!! It was working fine, then it started doing this, I played around with it and started working fine again. Now is doing it again, is not displaying the tenants name and its a bound field on this form. If I insert the same field on the form it shows me the tenantID on all records but it will not display the name.

Mr.Wilkinson I realy appretiate your help..

DaniBoy

PS I have attached the DB with only two leases and one tenant per lease.

 

[jdcallejas]

Ok!!! I give up!!! jajajajaja

I have been going crazy with this invoice issue... I think am not going to use INVOICES!!!!...

I think I am going to just input payments with out invoices... More like receits... I am thinking of linking my payments directly to my leases. Then the user will pick on the lease and since on the lease I have the amount of the rent fee I will use that to create a payment. I will also have extra fields for late fees, NSF fees ect. I think I will use a field where I add all items together on the payment form and have a field where I will input the amount paid.

This way I will not need to create invoices... I will just do reciets... I also think I will make a pastdue balance field on this form that will look up a balance due on a query that will add all payment amounts and all fees fro that lease, the dif will be the balance due this will be addes to all the fees... All this guys need is to see who paid that month and who didnt, what is the total of all payments that month ect. Seems like I was doing to much work.

Offcourse I need to run this by you guys the EXPERTS..

What do you guys think about this...????

DaniBoy

 

[georgedwilkinson]

It sounds like a valid approach. If you don't need them (no Sarbanes Oxley or auditing requirements), there is absolutely no reason you need to create them.