APPLYING A PAYMENT (Access 2000)

Topic

New Reply

I’m trying to make an “Apply” screen for payments of invoices. I have a payment screen that has a button to the “Apply” screen. My invoices are made up of (sometimes) several lines. I need to be able to apply each payment to certain lines of the invoice because the payment either gets submitted to us (head office), or stays at the site according to what it is applied to.

My problem is…I don’t know how to set it up so that each payment that I apply gets added on to the previous payment. On the “Apply” screen for the second payment I want it to show under invoice “Amount” the amount left over after the last payment. How do I do that?

Jen

Reply | Quote

Replies

You’ve told us what your form is trying to do, but what do your tables look like? Is each of the “lines” in your invoice a separate record? What do you mean by “each payment that I apply gets added on to the previous payment”? Are you adding a record for the payment to a payments table?

Reply | Quote

I have a Student table and an Invoice table that just shows the invoice #, Student ID#, and date of the invoice, then there is an InvoiceDetails table that shows each line of the invoice (invoice #, type of fee (e.g. elementary, Jr high, etc.), and the amount of the fee). All these tables are linked to the Student table. Then I have a Payment table that has a payment # automatically assigned, Student ID#, date of payment, payment amount, cheque #, Notes, and check boxes for whether its a cheque or cash. This table is linked to the Student table.

I then want to have it so that when I enter a payment and it goes to the Payment form, it has a button to apply the payment. It then goes to the Apply form and shows the invoice line by line and you enter the amount of the payment you are applying to which line. E.G. If a student pays $10, and has an Elementary fee of $35 and a Local Levy of $5–you can apply $5 to the Local Levy and $5 to the Elementary fee. Then when you have another payment of, lets say, $15–you will see the amount remaining of each line of the invoice, and then be able to apply that next payment.

I hope you can understand what I’m trying to do. Maybe I’m even making it more complicated that I need to. If you can suggest another way to do it, I would appreciate any help.

Thanks,
Jennifer

Reply | Quote

That seems relatively straight-forward, but I’m still not clear on what you meant by “each payment that I apply gets added on to the previous payment”. Are you trying to add a number to the payment already in a line item? If you do that, you’re going to lose whatever history of payments applied that you might have had.

I’d suggest you have another table that holds the applied amount and the key of the invoice line item record. You could use a popup form or even a conditionally visible sub-subform (if the parent is a continuous form or subform, use a popup) to enter/apply the amount to a particular line item, creating a new record in the line item payment table. The control on the line item detail screen would be a calculated control that summed the total of any payments applied to that line item, and you would requery it when the popup closes. That way, you would see a total of payments applied to that particular line item and could use another calculated control to display any remaining balance for the line item.

Does that help?

Reply | Quote

I’m a bit confused. I’m not sure how to do a “pop-up” window for each line in the invoice. I gathered from your post that I should add a field to my Invoice Details table to make an individual number for each record. So I added a field called “LineNo” to the Invoice Details table and made that the primary key. I then made another table called Applied that has the fields: LineNo, InvID, and Applied. Now I’m not sure where to go from there. I have my Payments form that has the fields from the Payment table (StudID, Date, Payment amount, ChequeNo, Notes, and check boxes for cheque or cash. I got rid of the subform that I had there. So do I make another subform?

Im just not sure what to do next. Thanks for your help.

Reply | Quote

Let’s go back to your tables for a moment. The Invoice details should be linked to the Invoice table, not to the Student table. It should have a line number field if you want the lines to occur in a particular order, but otherwise that isn’t important. However, it does need a unique key of some sort, but if line number will be repeated in different invoices, then you can’t make line number a primary key. You can make line number plus Invoice # a unique key though, and that’s what I would recommend. You can use an Autonumber as the primary key or use line number plus invoice# as a multi-field primary key for that table.

Your Applied table also needs a field for Payment #. I assume that the Applied field holds the amount applied, right? The addition of the key from the Payment table will allow you to see how much was applied to that line item from a particular payment.

I’m not sure how your applied form is intended to work. You have to find some way to pass the payment # to the applied form so that it can be inserted into the record that applies an amount to a line item. If the Applied form shows an invoice, with each line item as a separate record, then how you pass the payment # to that form depends on whether the applied form is just a subform on the main form or an entirely separate form being opened when you click a button. If it’s a subform, you can simply refer to the control that holds the payment # directly. If it’s a separate form, you can pass the payment # in the OpenArgs argument of the DoCmd.OpenForm statement. Then in the Open event of the Applied form, you can get the value in the OpenArgs statement and assign to to a variable that can be used later to populate the payment # in each line item you apply the payment to.

Does that help?

Reply | Quote

Edited by JenniferS on 21-Aug-01 22:38.

That sure did help, but…

I have made up the Payment Form with an Applied Subform. I’m not sure how to take the Amount of the fee less the Applied and show it as the Amount the next time another payment is entered. Here’s an example:

(This is for a $10 Payment)
FEE AMOUNT APPLIED OUTSTANDING
Elementary $35 $5 $30
Local Levy 5 5 0

Now when I go to enter another payment, like for $15…

FEE AMOUNT APPLIED OUTSTANDING
Elementary $30 $15 $15
Local Levy 0 0 0

Is there any way to do this?

—————————————————————-

Okay, now I have changed it around a little, but still having problems. I have the Payment Form setup so that it has a subform that displays StudID, LineNo, InvID, Fee Name, Amount, and then a button that says “Apply” that opens a frmApply Amount form. But when it does this it won’t pull in the LineNo and InvID. Why won’t it do this??? I wanted it to show the line number (because the “Apply” button is after each line of the invoice) so you could then enter the amount you wanted to apply.

Reply | Quote

You have to calculate the remaining outstanding based on the original amount less the sum of the payments. Then to calculate the current outstanding (after payment), you have to calculate the same value less the current amount applied. Does that answer that question? You can do it in part in the query behind the firm by using a subquery that returns a sum of applied payments for each line item. That less the current amount applied would give you the remaining outstanding to carry forward.

When you open the frmApplyAmount (don’t put spaces in your form names, they force you to use square brackets in code and queries and are generally a nuisance), you can do one of two things: 1) pass it all the values you need to pass in the openargs using something like a semicolon to separate the values in the string. Then you can parse out the values using the instr function. 2) read the values directly from the subform. The values you’ll get that way will be those in the current record of the subform.

Reply | Quote

How would I read the values directly from the subform? Do I use the same query that I used for the subform?

I’m a little confused. I’ve just been teaching myself Access as I go along. This is the toughest program I’ve ever had to learn. Did you find it being very hard to learn? I just don’t understand what all the controls mean and do, and how the program reacts to certain changes. If there was a book that explained what each property meant and what it did that would sure help. Do you know of any such book?

Reply | Quote

[indent]

---

How would I read the values directly from the subform? Do I use the same query that I used for the subform?

---

[/indent]No, you actually get the value from the open subform. The syntax to use from another form is Forms!ParentForm!SubformControl!ControlName. So if you had a form named “MyForm”, with a subform control on it named “MySubform”, and that subform contained a control called “txtValue”, you could return the value in the control txtValue by doing something like this:

Dim strValue as String

strValue = Forms!MyForm!MySubform!txtValue

If the subform is continuous, the reference will return the value from the current record, whichever that may be.

[indent]

---

Did you find it being very hard to learn?

---

[/indent]I was a dBase programmer before Access was introduced, and I’ve been working with Access ever since, so for me it was a case of learning how to do it differently in a graphics-oriented program, rather than learning entirely new concepts. Of course, I have new stuff to learn with every new version that comes out, but that isn’t exclusive to Access.

There are loads of books on Access, so I’d suggest you visit your local Borders or Barnes & Noble (in person, preferably) and leaf through some of the books you find there. Access books range from the “dummies” type to things like the Access 2000 Developer’s Handbook, which is 2 volumes and 3000+ pages. Each book usually has an indication on it of the skill level involved, ranging from Beginning to Advanced, so make sure you check that. You need to see which one “speaks” to you the best rather than just accepting someone else’s recommendation anyhow.

One good learning tool is the Microsoft Step-by-Step books (there have been books for each recent version of Access and other Office apps), which will walk you through creating the basic elements of an application so that you can really understand what each object does and how to use it. A good quick reference on database design is “Access Database Design & Programming”, by Steven Roman, published by O’Reilly (ISBN 1-56592-626-9). Its companion volume “VB & VBA In a Nutshell”, by Paul Lomax (ISBN 1-56592-358-8) is a good reference for both Access/Office and VB6 programming.

Most of us have our particular favorites when it comes to books, and you might want to ask that question again in the Books forum to see what others come up with.

Reply | Quote

Edited by JenniferS on 23-Aug-01 20:08.

Okay, I got the LineNo and the InvID to show up on my ApplyAmount form. But now I still can’t get the totals to work properly. Here’s what I’ve setup:

On the screen you see; LineNo InvID Amount Applied Outstanding PayNo

The Outstanding amount has a control– =[Amount]-[Applied].

When I go to close the window it says that I can’t save the record because a related record is required in tblInvoiceDetails. What does this mean? Am I missing something?

And…how do I get the oustanding amount to now show in the Amount column after each payment? And I would then have to transfer it to the Applied Subform so the Amount there shows the same as the AppliedAmount form.

______________________________

I now added the InvoiceDetails table in the query builder, and added in the columns LineNo from Applied Query, InvID from Applied Query, and Amount from InvoiceDetails. Did I do this wrong…because now it says that the Microsoft Jet database engine cannot find a record in the table “tblInvoiceDetails” with key matching field(s) ‘LineNo, InvID’. But those fields are in the InvoiceDetails table.

Reply | Quote

You’re asking questions that are very difficult to answer without being able to see what you’re doing.
[indent]

---

On the screen you see; LineNo InvID Amount Applied Outstanding PayNo

---

[/indent]Is this on the ApplyAmountForm or one of the others?[indent]

---

a related record is required in tblInvoiceDetails. What does this mean? Am I missing something?

---

[/indent]What exactly are you doing in the ApplyAmountForm? What does the query underlying it look like? Post the SQL because that error can occur when you try to create a record in a query that doesn’t have the necessary key fields from both tables in it.[indent]

---

how do I get the oustanding amount to now show in the Amount column after each payment?

---

[/indent]What Amount column on which form?[indent]

---

added the InvoiceDetails table in the query builder, and added in the columns LineNo from Applied Query, InvID from Applied Query, and Amount from InvoiceDetails

---

[/indent]But where did you put the query? You’ve said what you added to it, but you didn’t explain where you were using it. If this is the query behind your ApplyAmountForm, then post the entire SQL for it so we can see what you’re doing with it. And what is the Applied query you’re referring to? You’ll need to post that SQL as well.

Reply | Quote

Sorry about that. Here’s the answers to your questions:
———————————-
On the screen I see; LineNo InvID Amount Applied Outstanding PayNo
———————————-
This is on the ApplyAmount form.

———————————-
…a related record is required in tblInvoiceDetails. What does this mean? Am I missing something?
———————————-
Okay, in the SQL–Query Builder I have the following:

The tables that are showing are the tblInvoiceDetails, and the tblApplied. Under the tblInvoiceDetails I have the fields–LineNo, InvID, FeeID and Amount. There are two key fields–LineNo and InvID. Under the tblApplied I have the fields–PayNo, LineNo, InvID, PaymentID and Applied. LineNo and InvID have a one-to-many relationship with ONE being the InvoiceDetails table.

In the query area I have the LineNo field from tblApplied, InvID field from tblApplied, Amount field from tblInvoiceDetails, PayNo field from tblApplied, PaymentID field from tblApplied, and Applied field from tblApplied.

On the ApplyAmount form itself I went into properties for the boxes: LineNo; InvID; and PaymentID, and I used the formula you gave me (Forms!FormName!SubformName!Control) to pull in the data from the Applied Subform and the Payments form. I hope this is the information you need.

————————————
how do I get the oustanding amount to now show in the Amount column after each payment?
————————————
Okay, on the Payments form I have the payment info (StudID, PaymentID, Date, Payment amount, ChequeNo, and check boxes for whether cash or cheque). Then below that is the Applied Subform which consists of: LineNo, InvID, FeeName, and Amount. Next to each line I have a “Apply” button that goes to the ApplyAmount form.

On the ApplyAmount form I have: LineNo (from the Applied Subform); InvID (from the Applied Subform); Amount (from the Applied Subform); Applied (from the Applied table); Outstanding (just a text box with a formula to deduct Applied from Amount); PayNo (an autonumber field from the Applied table); and PaymentID (from the Payments form).

When I go to apply a payment in the ApplyAmount form, I enter the amount to apply under Applied and then the Outstanding amount shows up (e.g. the Amount is $35, I enter $5 to apply and the Outstanding says $30). Now what I want to happen is when I close out of the ApplyAmount screen, that line I just applied to should show the Outstanding amount on the Applied Subform. Do you know what I mean? It should amend the Amount column on the Applied Subform. OR maybe I shouldn’t be doing it like that. I don’t want to change the amount that is in the InvoiceDetails table.

I hope this is enough information. If you need any more, just let me know.

Reply | Quote

[indent]

---

…a related record is required in tblInvoiceDetails. What does this mean? Am I missing something?

---

[/indent]In order to insert a new record into a multiple table query in Access 2000, you must have *all* the key fields and required fields from all the tables on the grid. This is just the opposite of the way it worked in Access 97, by the way. So you need LineNo and InvID from *both* tables in the query in order to add a record to either table. You can assign an alias to the fields that belong to the table you aren’t adding a record to in order to avoid having to change your form to fully reference the fields by table name.

[indent]

---

Now what I want to happen is when I close out of the ApplyAmount screen, that line I just applied to should show the Outstanding amount on the Applied Subform. Do you know what I mean? It should amend the Amount column on the Applied Subform. OR maybe I shouldn’t be doing it like that. I don’t want to change the amount that is in the InvoiceDetails table.

---

[/indent]You shouldn’t be changing any amounts anywhere except by entering the applied amount. I’m getting lost in all these forms and subforms, so I’m not sure what amount you want to see and where you want to see it. If all the applied subform shows is a single amount, what does that represent? Is it what has been applied so far? If so, that isn’t really enough information. You should probably show the original amount in one control, then the total applied in another control, and finally, an outstanding amount. Then all you need to do is requery the controls from your ApplyAmount form in order to show the new totals in Applied and Outstanding after you apply a new amount.

Reply | Quote

EEGADS!!! I just can’t seem to get this to work!
————————-
…a related record is required in tblInvoiceDetails. What does this mean? Am I missing something?
————————-
I don’t think I’m understanding you correctly, ’cause I just can’t get it working. I still get the same message. In my Payments form I have the Applied Subform that shows the following:

StudID LineNo InvID FeeName Amount Total Applied Outstanding

Now, from StudID to Amount, I get from a query. Is that right? I’ve pulled from the Invoices table–StudID, InvID. From the InvoiceDetails table–LineNo, InvID, and Amount. From the Fees table, just the FeeName. I don’t know if this is where I’m going wrong, or what.???

Then for the ApplyAmount form that you go to in the Applied Subform (there’s an Apply button), I used the Applied table (I pulled it in). That consists of fields: PayNo, LineNo, InvID, and Applied amount. I then used syntax in the controls for the LineNo and InvID. I also made a text box in the footer of that form to calculate the sum of the applied.

I get the “related record is required in tblInvoiceDetails” error when I am in the ApplyAmount form. I’m just going around in circles here! I hope you can help.

Reply | Quote

Try dragging *all* the fields from all the tables onto the query grid. The run the query from the query window. Try to change a field. Will it let you? OK, then try to add a record to the subform table. What happens? It’s very difficult to diagnose problems remotely without knowing exactly what you’re seeing, so you need to test it yourself. If the query is updateable, then you can look at your forms. If it isn’t, then the problem is in the query and we can talk about that.

One thing to watch out for is a form and subform based on the same query. That will cause all sorts of locking conflicts with no way out. Base the parent form on a recordset that will return only the fields that are necessary or required (including the key fields). Base the subform on a *different* query that return on the records you need for the subform and to link to the parent form. You can only create new records in the subform that already have a parent record, so make sure you know what happens in your parent form recordset when you try to add a record.

Reply | Quote

Okay, I dragged all the fields onto the query grid. I tried to change each field, and I got three fields that could not be changed. It was tblInvoices.InvID, LineNo (from tblInvoiceDetails), and tblFees.FeeID.

When I go into the Applied Subform and try to add a line, it won’t let you. It won’t let you enter the tblInvoiceDetails.InvID OR the Amount (from tblInvoiceDetails). When you try to enter something in the Amount field, it says “join key of table tblInvoiceDetails not in recordset.” What does this mean?

Reply | Quote

I would suspect that those fields are primary keys and participate in a relationship which prevents them from being changed. If they’re autonumbers, you can’t change them anyhow, even without a relationship. The idea was to see if the recordset was updateable at all, which it apparently is.
[indent]

---

When I go into the Applied Subform and try to add a line, it won’t let you

---

[/indent]Are you talking about the subform itself ro the query underlying it? In either case, the error means what it says. Whatever key field you’re using to join tblInvoiceDetails to the rest of the recordset isn’t one of the fields in the query. Without that key, there is not way for the query engine to add a record. You must have all the key fields in the recordset, even if you don’t use them on the form, before you’ll be able to add records to any of the tables involved.

Reply | Quote

Oh Boy! I’m just going around in circles! I added in all the fields for both the InvoiceDetails table and the Applied table when I did my ApplyAmount Query. And when I do that I just keep getting a message saying “The Microsoft Jet database engine cannot find a record in the table ‘tblInvoiceDetails’ with key matching field(s) ‘tblApplied.LineNo’.

Am I getting all these problems because I’m using the wizard to do the Apply button? When I use the wizard, it asks which fields have matching data for lookup. I noticed that the other LineNo field on the Applied table which I did not match is the one that the Microsoft Jet… message refers to. Help!

Am I doing the wrong kind of query? I’m just doing a regular query. It seems to do okay with the InvoiceDetails table in the query, but when I add the Applied table, it just gives me trouble. Is it because I have no data in the Applied table?

Reply | Quote

[indent]

---

Is it because I have no data in the Applied table?

---

[/indent] Not necessarily, but that depends on the kind of join. If you have a unijoin (all the records that match in both tables), then not having any records in one table will eliminate all records in the query.

The fact that it is specifying ‘tblApplied.LineNo’ suggests that you may need to alias one of the fields. If you have two fields with the same name in a query, you need to alias one of them or the query will use the full reference, including the table name, for each of them. Forms that normally reference a field called LineNo won’t recognize tblApplied.LineNo. I’m not saying that’s the problem, but it’s one thing to look out for.

Reply | Quote

Well, I finally am not getting that “Microsoft Jet database engine…” message anymore. It ended up being the join properties that were the problem.

But now, I can’t pull in the Total Applied into the Applied Subform from the ApplyAmount form. I put the following in the Control Source line of the ASTotalApplied field of the Applied Subform:

=[Total Applied]

That is the name of the field in the ApplyAmount form. But it just gives me an error. I also tried using:

=Forms![ApplyAmount]![Total Applied]

but it didn’t like that either. What am I missing? Boy this section is a real nightmare!

Reply | Quote

You have to use the following syntax:
= Me![NameOfTheSubFormControl].[form]![NameOfControlOnSubform]

Watch the . before [form] and not a ! or you will have a #Name? error

NameOfTheSubFormControl is not the name of the form you see in the database window but the name you see in the property when you select the subform in the design view of the main form

Reply | Quote

But its another form that I’m referring to from a Subform. I’m trying to pull the totals from my ApplyAmount form which is a button on the Applied Subform. From the Applied Subform there is an Apply button that opens the ApplyAmount form. So when I close the ApplyAmount form, I want the totals to go to the Applied Subform. I hope you know what I mean.

Reply | Quote

The ApplyAmount form would have to be open for you to reference it from another form, so you can’t do it the way you want to. What you can do is use a DSum() to calculate the value you want to see in the Applied Subform control.

Reply | Quote

It has taken me a while before I could get back to working on this program…anyway, I don’t understand how I would use the DSum. I read the help file, and I just don’t understand what kind of data I’m suppose to put in there. And…can I pull things from another form? Do I put the DSum on the Applied Subform and pull data from the Apply Amount form? Remember, I have a Payments form that has an Applied subform and a button that goes to the Apply Amount form. This program is really driving me nuts!!!

Reply | Quote

I’m not sure what you’re trying to do. I thought you had a control where you wanted to show the total sum of payments applied. If you have a control on either your form or subform (it will need to be in the form header or footer if your subform is a continuous form and you want it there), you would use the same DSum formula in the controlsource for the control. It would be something like:

=DSum(“[Applied]”,”tblApplied”,”[Invoice#]=” & [txtInvoice#] )

This assumes that your Applied table is named tblApplied and that it contains a numeric Applied field that holds the amount applied for a particular Invoice# . Dsum will add all the records in the Applied table that have that Invoice#.

Does that help? You would need to requery that control when certain things happen, like the Current event of the parent form and the Current event of the subform, at the least.

Reply | Quote

I don’t think I really understand your problem.
When you say ” I want the totals to go to the Applied Subform”, do you mean to unbound controls or in fields ? If you want them in fields you’ll have to update the fields by a function in the AplyAmount form, then requery the Applied Subform and close the AplyAmount form.

I little difficult to give some explanation when you don’t see the application.

Reply | Quote