Hi, everybody. I have an Access database with some simple billing tables, and I want to add a payment form.
Right now, there is a charges table and a payments table, and today I made a credits table (which I don't know if I'll use or not).
I've been looking around for an example of what I want to build but have come up empty, so I'm hoping someone knows of a solution that's close.
What I have is a form that lists all the charges for that one customer. The form is a sub-form based on a query that filters based on the customer ID in the main form, so you're only seeing that person's charges. I added a checkbox in the charges table to mark if the database user wants to make a payment on that charge.
The way it's working now is the payments table (a form based on the table) is a subform for the charges, so you open one charge and then enter a payment for that one charge. The operator is complaining because she has to do the math herself, so she wants to: 1- enter the amount the customer paid; 2 - check the charges that the payment will be applied to; 3 - check for a credit balance (hence the separate table, which only has the customer id and the credit amounts); and then click a button and have the payment records created.
Enclosing a picture.
I've thought of two conceptual ways to do this -- one is to have a macro that assigns the values in the payment amount, each charge amount, and the credit balance to variables, and then creates a new record in the payments table as long as there's money left over. This is beyond my VBA moxie and I would need a good relevant example.
The other way I thought of is to (sorry, this is kludgy) have the macro create/append to a query or table with all the charges in it and the amount paid and somehow use that to create the new records.
Ideas will be greatly appreciated!!
(PS - I'm using "customer" in the thread, but it's a membership database, so in the pic you see the word "member")