473,320 Members | 1,859 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Applying payments to invoices

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.
Nov 7 '08 #1
6 9386
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
Nov 7 '08 #2
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
Nov 8 '08 #3
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 AmountOfPayments 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
Nov 8 '08 #4
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
Nov 10 '08 #5
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
Nov 11 '08 #6
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
Nov 12 '08 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: tom | last post by:
Hi, I have built an Access DB for a child care business. Family table stores the Family info. Room the different room names, capacity etc. Price stores the various price codes and amounts. The...
9
by: StevenH | last post by:
I stumped Can someone assist me with the query for "invoices from 2002 & not from 2004" thanks
1
by: totoringo2004 | last post by:
Hello, I’d need some help guys... I know how to do it in Excel, but can’t find a way in Access... here’s a simplified overview of the problem: Let’s say I have Invoices as follows: Jan-04 $110...
5
by: astro | last post by:
I have a report/invoice that lists volume, price per unit, and extended price. My problem is being able to get the extended price by multiplying the x . With just 2 decimal places on both...
1
by: ilaiyaraja | last post by:
Hi all, One of my client want e-payments for them products.. so I need to make Online payments for my Online-project any one know about online payment service pls help me.. thanks in advanse.
0
by: israelekpo | last post by:
phpPaypalPro version 0.2.0 Released The second version of phpPaypalPro has been released. phpPaypalPro is an object-oriented framework developed in PHP5 to integrate easily with the Website...
31
by: sajitk | last post by:
Good morning, I am making a database to keep track of payments made by villagers on account of purchasing solar lanters. the rule is the payment can be made in small amounts each month. now wat i...
1
by: Paul H | last post by:
The database I am planning will manage the hire of equipment. The hire period and the weekly hire rate are defined when the contract is created. Typical examples will be: 1. Hire of a digger...
3
by: samatair | last post by:
Has anyone done website payments pro direct payments (recurring) with pay pal? I need to implement the same in my server. Could anyone help in this regard? I need to know whether I need to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.