Applying payments to invoices

[jdcallejas]

Hello,

I am creating a database for my apartment complex. I have been able to desing it up to invoices... Now I am wondering how to make payments for the invoices.

I wanted to have it like quickbooks has its make payment form. On QB you select the client and it shows all pending invoices for that client. what I like is that if there are 3 invoices pending #1= $20 #2= $50 #3= $100 it shows you all the and gives you the total at the botom, in this case the total is $170.

In this case if I make a payment of only $25 and apply the payment, QB looks at each invoice and if there is enough money to pay it, it pays it and marks ir as paid and chnages the total. So this means that I have 5 dollars left, I cannot pay any other invoice so QB just subtracts the amount from the total.

Now how can I do something like that?

It does not have to be the same way but something where I can apply payments, mark invoice as paid is applicable if not just subtract from total due.

I hope this made since.

[garyb2008]

Hi

I was thinking about this a while back for a project that never went ahead. At the time i was going to look at the Northwind sample database that comes with access to see if there was anything in there that could be used.

What is your table structure at the moment.

Gary

[jdcallejas]

Hello,

Thank you for your prompt reponse, you can see the image to see what I have right now, I have nothing on payments... everything works around the Leases table. The income table is basicaly the "products" table and income type is the "Category" table. I know what I want to do it has to be done with code, the problem is that I dont know VBA to good..

Table Structure image

Thanks

Daniel

[garyb2008]

I suppose youll have to decide the table structure for handling payments then

You could have something like

Table: Payments

Fields:
PaymentID
TenantID
PaymentDate
PaymentType
PaymentAmount
PaymentNotes

Table: InvoicePayments
PaymentID
InvoiceID
AmountofPayment

So when a payment comes in its Recorded in the Payments Table. The process of applying payments to Invoices will add a record to the InvoicePayments table, which records which invoices a payment goes toward. This means you can have one payment go to multiple invoices.

You will have to link Invoicepayments to Invoices and Sum the AmountOfPayment to see how much of an invoice is paid.

You will also have to link Invoicepayments to Payments to Sum the AmountOfPayment s to see how much of A payment is not allocated.


You could make it simpler, as below

Table: Payments

Fields:
PaymentID
TenantID
InvoiceID
PaymentDate
PaymentType
PaymentAmount
PaymentNotes

Here you can only have a payment go to one invoice, but you can still have multiple payments per invoice.

Finding unallocated payments is easy, filter for InvoiceID = Null.

You will have to link Payments to Invoices and Sum the PaymentAmount to see how much of an invoice is paid, and find unpaid invoices.

Theres quite a bit of leg work to do before the facility is implemeneted. Have you looked to see how Northwind does it?
Also, take a look at Access databases you find on the internet for download, even just trials of commercial products and see how they do it. Try google, the microsoft marketplace etc etc for some tips.

Regards

Gary

[jdcallejas]

hello gary,

I got the invoice payment working just need to figure out some other stuff for it. For example i need to figure out how to be able to see if there is a balance due, but I think i will do this with queries.

Now How can I make a filter on the payment form so that when I select a tenant to only show me this tenats invoices on the subform?

Thanks

Daniel

[garyb2008]

When you add a subform to a form it gives you an option to set the subform to be filtered by a field on the parent form during the wizard, just choose the relevant fields on the forms.

Alternatively, to filter (any) form in VBA code, you set the forms filter property to a filter expression, and then set the forms FilterOn property to True.

eg.

Say you had an unbound combo box (named combo1) which lists all customer ids in the header of the payments form, you could use this to filter the form.

Go into the properties of the combo box, and in the On Click event property select [Event Procedure]. This takes you to the VBA page were you would enter the code:

Me.Filter = "[Customer ID] = " & Me.combo1.Value
Me.FilterOn = True

Hope this helps

[jdcallejas]

Thank you Gary,

I have done two things, I have created an InvoicePayment and I have created a query called InvoiceList to do the payments.

On the first one I use a combobox to list the Invoices not paid for the Customer, I can select the invoice to pay and input an amount of payment like you said. I have also created an update query that will check the amount paid with the invoice amount and if is they are the same than it updates InvPaid to Yes.

The only problem I see is that if is not the same amount when I make another payment the invoice will show up with the original invoice amount.

On the second one I just created a subform for the query InvoiceList and made a filter to show all the Pending Invoices for the customer.on the parrent form.

The thing here is applying the payment, there would have to be a code that takes a look at the total amount of the invoice, if the payment amount is larger than the invoice amount than it would pay that invoice and subtract the invoice amount form the total payment amount and mark the Invoice as paid.

Lets say I have two invoices with a total amount of $50, I make a payment of $40, So the code would pay the oldest invoice first and mark it paid and then it would check to see if it can pay the next Invoice if it can than it will pay it if not it will save the whats left of the payment amount on a field called unused payment. This unused payment would be addes to the new payment once on is created.

Now my question is how dificult is it to do this?

Daniel