Need help with payment procedure to apply a payment to a specific charge

[karininwinnipeg]

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")

[Gina Maylone]

Good morning!

Is there a chance that the member would make more than one payment against a charge?

Thank you!
Gina

[karininwinnipeg]

Good morning!

Is there a chance that the member would make more than one payment against a charge?

Thank you!
Gina

Yes, sure. If someone overpays and then there's another charge, that overpayment would be applied to the new charge. It's also pretty regular that people make installment payments for their dues.

[ssanfu]

My observations/comments:

You shouldn't use spaces, punctuation or special characters (exception is the underscore) in object names. You have spaces and the period in the field names as shown in the image ("Charge ID" - has space, "pmt. ID" - has space and punctuation; a period) .

You are using a table in the sub form. I never use tables as the record source of forms/reports, only queries. With a table, you cannot control which fields are displayed, harder to alias fields, harder to control the order.
I always use queries for form/reports record sources. (Just the way I code )

Should never display an autonumber field. See:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers


As far as the tables, does it really matter which payment was made to which charge?

You have charges that are a certain amount, one or more payments reduce that amount. If you overpay, there is a positive balance (credit) displayed.
I would use one table, with a field to record if a charge or payment. Summing all of the charges and payments for a specific member will give you a balance.
Unless you are charging interest, which payment applies to which charge really shouldn't matter. BUT........... I don't know your requirements (business rules).
It is harder to manage 3 tables, rather than just one..........

How familiar are you with VBA code?

[karininwinnipeg]

Thanks for your reply. FYI, the sub-forms are based on queries, and the column headings are NOT field names, but captions. The field names are like "curChargeAmount". I don't use spaces, punctuation, etc. in my field names, and I use naming conventions. As for the "ID", yes they are showing. This is mostly for testing and training purposes. The admin uses them sometimes, and knows what they are in this case, so we leave them in sometimes.

I think it's important to track the payment to the charge specifically.

I am familiar with VBA, and I know conceptually what I'd like to do but need an example to get me started, which was the purpose of this post, really.

I made a form that has the member's outstanding items on it, each with a check box so the admin can check if that's the charge she wants to apply the payment to. The form also has an unbound control for the admin to enter the amount of the payment. I'd like the macro to create a new payment from the amount, and then apply the payment to all the checked charges.

Here's how I envision the logic, in regular English:

1 - check to be sure that all checked charge amounts are not more than the amount paid. If they are, return an error message and exit the macro.
2 - if charge amounts do not exceed amount paid, then make a new record with a date (could be entered in a message box or use today's date), member ID, amount, and ask the admin to choose any other info in an input box.
2 1/2 - at some point in the beginning, the macro would check to see if there is a credit balance, and if there is, ask the admin if she wants to apply that credit to the payment. If yes, the credit record in the credit table would be set to zero, or maybe another field could be populated with "used on (date)", and the amount added to the payment amount.
3 - then the payment amount would be applied to the charges that were checked. "applied" in this context means that the charge is marked "paid" and the payment ID (from step 2) would be added to that charge's "payment ID" foreign key field.
4 - every time a payment is applied to a charge, the amount of that charge is subtracted from the amount paid (I imagine all of this needs to be handled by at least one variable).
5 - when the payment variable is zero (no mo money), or if all charges are paid, then the macro sees if there's any money left in the payment (again, probably in a variable), and if there is, dump that into another table as a credit.

So, I sort of do know what I'm doing, and then again, I'm having trouble getting the code off the ground.

Thanks again for your help,

Karin

[orange]

karin,

Can you post a copy of your tables and relationships (jpg)?
I agree with Steve re naming conventions generally (no spaces nor special characters).

It seems to me that you are adding things to your database, and it isn't clear that the underlying tables and relationships were designed with those features in mind. I suggest you work from a data model based on your business rules. You can implement what you need based on Priorities, but always working from your 'data model'/blueprint.

Good luck with your project.

[karininwinnipeg]

Here's a capture of my tables and relationships. Just for the record, again, I'm NOT using spaces or characters in my field names. What you are seeing are captions.


Thanks

Karin

[ssanfu]

No offense meant. All I had to go on is the picture (about the naming convention/ sub-form).

I think it's important to track the payment to the charge specifically.

Fair enough - it is your project.

I am familiar with VBA,

You only said "macro".... to me, that means scripting. "Code/VBA" is totally different (to me).

Here's how I envision the logic, in regular English:

You seem to have a good grasp on what you want. This is also called pseudo code. My next step would be to create a flow chart. Then start coding.
I swapped 2.5 to 1. This is what I used to create a flow chart. Of course, your flow chart would different, but would look similar.....

pseudo code:
1 - at some point in the beginning, the macro would check to see if there is a credit balance, and if there is, ask the admin if she wants to apply that credit to the payment. If yes, the credit record in the credit table would be set to zero, or maybe another field could be populated with "used on (date)", and the amount added to the payment amount.

2 - check to be sure that all checked charge amounts are not more than the amount paid. If they are, return an error message and exit the macro.

3 - if charge amounts do not exceed amount paid, then make a new record with a date (could be entered in a message box or use today's date), member ID, amount, and ask the admin to choose any other info in an input box.

4 - then the payment amount would be applied to the charges that were checked. "applied" in this context means that the charge is marked "paid" and the payment ID (from step 2) would be added to that charge's "payment ID" foreign key field.

5 - every time a payment is applied to a charge, the amount of that charge is subtracted from the amount paid (I imagine all of this needs to be handled by at least one variable).

6 - when the payment variable is zero (no mo money), or if all charges are paid, then the macro sees if there's any money left in the payment (again, probably in a variable), and if there is, dump that into another table as a credit.

Flow chart:
Attachment 22852

Now start writing code, adding declarations, variables and calculations.

I still think one table would be better, but that is your decision.

Good luck

[karininwinnipeg]

Wow, thanks for the cool flowchart, Steve, I appreciate it. I didn't take offense at your comments -- I realize you just had the pic to go on. I would have said the same things. I just wanted to clarify that so you all wouldn't feel compelled (as I would) to continue helping me with that, ha.

Anyone have an example of how to do something like this in Access? I'm just stuck on the actual code writing. And I did use "macro" -- bad habit left over from teaching and referring to all VBA code in the context of Office applications as "macros."

I'll keep working on this, and if I can cobble some code together I'll repost here. I have 3 other projects that suddenly are due Monday so I have to focus on them.

Cheers,

Karin

[Gina Maylone]

Here's an example for you.helpdatabase.zip

HTH
Gina

[karininwinnipeg]

Just had a look at that article about keys -- that's a good one. Thanks for sharing.

[karininwinnipeg]

Hey, Gina -- thank you! That looks really interesting. I appreciate you taking the time to post it. (Maybe create it?) I really appreciate it. I'll have a look when I get back from my biz trip and let you know if I have any questions.