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

    Problem with Payment section! Still!!! (Access 2000)

    Alright...I'm tackling this problem again after a long well-needed break from it. It was really making me crazy. Well, I'm at it again, and I still need some help.

    I've made up a Payment Form that shows each line on an invoice. Whenever you go to enter a payment I need to be able to apply amounts to the lines on the invoice. When you go to enter another payment, the lines on the invoice should show the amount of each line less the last payment(s).

    I'll give you an overview of my database. This is a database to record Student's school fees. I go into the main form and select a Student, I then press a button called Invoices and go in to enter their school fees. For example, I might pick Elementary from the drop-down list and enter $49.00 and then on the next line I pick Local Levy from the drop-down list and enter $10.00. I then press the Print Invoice button and it prints an invoice. I then close the Invoice form. Now lets say they come in and make a payment of $20.00. I now go into that Student. Then I press the Payments button which goes into a Payment form. It lists the items on the invoice (Elementary Fee and Local Levy). I now enter the Payment information (date, amount, whether cheque or cash). Then I need to be able to go and apply the amount of the payment ($20.00) to each item on the invoice. So if I choose, I apply $10.00 to the Elementary Fee, and then $10.00 to the Local Levy. I then print out a receipt.

    Now lets say the Student came in and paid another $15.00. I go into the Student's form and press the Payments button and enter another Payment for $15.00. The items of the invoice should show on Payment screen the Elementary Fee less the first apply of $10.00 which would now be $39.00. And the Local Levy should now show zero. Then I would apply the $15.00 to the Elementary Fee. Do you understand what I'm getting at?

    So I have the payment form set up, but I can't get the "Applying" part to work. Can anyone help me with this? I've been having such a hard time with it. Every time I try to work it over again I just end up going in circles. I don't know where to put the Payment Form. Should I have it coming off of the Invoice Table or the Invoice Details Table or the main Student Table? HELP!!! Just to let you know...I don't think I fully understand Queries. Someone suggested cascading Queries, and I'm not sure what that is.

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Problem with Payment section! Still!!! (Access 2000)

    If I understand you correctly, the unique item will be the student or student ID. I am guessing that you have a table that Stores the Student ID and student information. I am also guessing that you have an invoice table and perhaps a payment table. The Invoice and Payment table are linked to the Student table as a one to many relationship (i.e, a student can have many invoices if invoices are generated quarterly) and a student can make many payments against an invoice.

    I may be over simplifying this, but if the Student ID is linked to the Invoice as well as Payments tables, then it should be easy to create a query that gets the total invoiced amount and total amount paid by student.

    Perhaps if you posted the database, it would be easier to look at it and help you.

    How have you set up the tables storing the data?
    Regards,

    Gary
    (It's been a while!)

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

    Re: Problem with Payment section! Still!!! (Access 2000)

    I'm working at my database again. Sorry for the delay in getting back to you. I can't attached my database because it is too big--even zipped. Let me know if I can e-mail it to you. I hope you can help me, this has been driving me crazy for a long time. No one can seem to help.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Problem with Payment section! Still!!! (Access 2000)

    Hi Jennifer,
    Try mailing it to me, my email address is in my profile.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Problem with Payment section! Still!!! (Access 2000)

    Please make sure you post any revelations to the thread.
    Charlotte

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Problem with Payment section! Still!!! (Access 2000)

    That's if I find any revelations ??
    There's no point keeping it secret, it does not serve anyone that way.

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Problem with Payment section! Still!!! (Access 2000)

    Try compacting the database before zipping it to see if the size is reduced. How big is the file zipped? You can email it to me if you like at the email address in my profile.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Problem with Payment section! Still!!! (Access 2000)

    Upon reviewing the DB, the problem appears to exist on the relationships of tables. You have a Student table that contains student data. Invoice and Payment Tables as well. The Invoice and Payment tables are linked to the student table such that one student can receive many invoices and submit many payments. You also have an invoice detail table to store details of each invoice sent. (No problems yet).

    The issue is the tblApplied table that is trying to track payments against the Invoice / Invoice Detail tables. You have linked the tblApplied to the Invoice table without any link to the payment table. You need to link to both tables to apply payments to the invoices as required. (See attached file)

    You will still need to apply some code to ensure you don't apply more then the payment received to the invoice as well as more then an invoiced line item.

    This should set you in the right direction or give you some ideas.

    HTH
    Attached Files Attached Files
    Regards,

    Gary
    (It's been a while!)

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

    Re: Problem with Payment section! Still!!! (Access 2000)

    The more detailed problem that I'm having is...I have just started learning code. So I get really confused about that, and working with formulas. My problem is that I don't know how to go about storing outstanding balances of each line of an invoice after a payment is made. I don't want to affect the original amount, so how do I keep track of what is left on each line of an invoice? Do I set up another table? I am really having trouble with this.

  10. #10
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Problem with Payment section! Still!!! (Access 2000)

    Jennifer,

    You can do this with a query linking the invoice detail and applied tables. (See attachment)

    The query will get the invoiced amount, apply payments per line item and fee type, and calculate a remaining balance. (Also see the db I emailed back to you, number 3)

    HTH
    Attached Files Attached Files
    Regards,

    Gary
    (It's been a while!)

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

    Re: Problem with Payment section! Still!!! (Access 2000)

    So to build a subform in the Payments screen for the applying of each payment, I would pull in the InvoiceDetails and the Applied tables, correct? The InvID field in the Applied table--does the link to the InvoiceDetails table by way of the LineNo automatically pull in the InvID number as well? I mean did you type that invoice ID in for the Applied table? When you link up to another table and use the same field names, does it go to that linked table for all the data when you have the same field name? I hope you know what I'm talking about.

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Problem with Payment section! Still!!! (Access 2000)

    Hi Jen,
    I'll leave you and Gary to work this out.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Problem with Payment section! Still!!! (Access 2000)

    Okay, I'm back at it. I'm trying to fix up my payment screen. But I am just going around in circles. I want to be able to put the total amount of the payment in, but then apply it on that same screen to the different lines on the invoice. I want it to first show the invoice details on the screen as I'm entering the payment, then apply the whole payment to one/or different lines of the invoice. I can get the lines on the invoice to show up on the payment screen when I enter the first payment, but do I make a button that says APPLY next to each line on the invoice? Then go into a form that is the Applied Table? Then how would I get the payment screen to now go to the TO BE PAID QUERY to show the changed amounts after I entered the applied amounts. That is where I have the biggest problem. Can you help? I have to have this done by TUESDAY!!!!!

  14. #14
    Lounger
    Join Date
    Nov 2001
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Payment section! Still!!! (Access 2000)

    Why not just have a comparison between the RunSum due vs RunSum paid, it'smuch easier than trying to assign specific payments

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Problem with Payment section! Still!!! (Access 2000)

    I just couldn't stay away.
    What you probably need is a Payments form that is independant of the Invoice form. This Payments form can accept payments as well as apply the payment against various Invoice Detail lines (which would be a subform on the Payments form).

    A problem you will have to address is what to do about a payment which has not been fully applied.
    You will need this form to select a payment and apply amounts to any Invoice Detail lines.

    You will need some checks to firstly ensure that a Payment has not been over applied or that you don't over apply an amount against an Invoice Details line. You may have to have a check on the close of the form to check if you have totally applied the payment, else you will leave unapplied payments in the system.
    This is no problem as long as you make the Payments form one where you can select any payment and unapply amounts as well as apply amounts.

    Hope all this makes sense.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Page 1 of 3 123 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
  •