Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    APPLYING A PAYMENT (Access 2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: APPLYING A PAYMENT (Access 2000)

    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?
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: APPLYING A PAYMENT (Access 2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: APPLYING A PAYMENT (Access 2000)

    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?
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: APPLYING A PAYMENT (Access 2000)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: APPLYING A PAYMENT (Access 2000)

    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?
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: APPLYING A PAYMENT (Access 2000)

    <P ID="edit"><FONT SIZE=-1>Edited by JenniferS on 21-Aug-01 22:38.</FONT></P>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.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: APPLYING A PAYMENT (Access 2000)

    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.
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: APPLYING A PAYMENT (Access 2000)

    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?

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: APPLYING A PAYMENT (Access 2000)

    <hr>How would I read the values directly from the subform? Do I use the same query that I used for the subform? <hr>
    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.

    <hr>Did you find it being very hard to learn?<hr>
    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.
    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: APPLYING A PAYMENT (Access 2000)

    <P ID="edit" class=small>Edited by JenniferS on 23-Aug-01 20:08.</P>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.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: APPLYING A PAYMENT (Access 2000)

    You're asking questions that are very difficult to answer without being able to see what you're doing.
    <hr>On the screen you see; LineNo InvID Amount Applied Outstanding PayNo<hr>
    Is this on the ApplyAmountForm or one of the others?
    <hr>a related record is required in tblInvoiceDetails. What does this mean? Am I missing something? <hr>
    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.
    <hr>how do I get the oustanding amount to now show in the Amount column after each payment?<hr>
    What Amount column on which form?
    <hr>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<hr>
    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.
    Charlotte

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: APPLYING A PAYMENT (Access 2000)

    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.

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: APPLYING A PAYMENT (Access 2000)

    <hr>...a related record is required in tblInvoiceDetails. What does this mean? Am I missing something? <hr>
    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.

    <hr>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.
    <hr>
    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.
    Charlotte

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: APPLYING A PAYMENT (Access 2000)

    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. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •