Help me!! Realy HARD CODE INVOLVED

[DaniBoy]

Hello, I have a big problem!! I have a database that keeps track of invoices and payments recieved for the invoices.

"Invoice"
First of all I have a calculated field "Total" on my invoice form that is the sum of all items on the invoice detail subform. This field is an unbound and so the value is not saved anywhere. I also have an Amount Due field "I dont know if it should be bound or unvound?!, and I have a Paid checkbox field that is bound.

"Payment"
I have a form where I pick the ClientID from a dropdown and enter an amount of the payment recieved on a field. I also have a Applied checkbox field.

What I need!!

I need to run a code that will go and apply this payments. What I need it to do is the following:

1) I need it to find only does Invoices with the Paid Checkbox = false.

2)Find only the Payments with the Applied checkbox = false.

3)Find the all invoices with the same ClientID as the Payment ClientID.

4)Grab the "Amount due" amount and compare it to the "Payment" amount. If the amount of the "Payment" is greater than the amount of the "Amount due", subtract the "Amount Due" from the "Payment" amount, update the "Payment" amout to the difference and update the "Amount due" to "0", and update the Paid Checkbox to true, goto the next Invoice.

if the "Payment" amount is less then the "Amount due" subtract the "Payment" amount from the "Amount due" and update the Amount due to the difference and the payment to "0". Once the Payment has been applied update the Applied checkbox to True.

I just dont know how to do openrecordset and that VBA stuff yet


Thanks for helping me,

DaniBoy

 

[Rich]

This would be better handled via queries, not vba.

 

[DaniBoy]

How Can I Do it Via Queries

I had tried doing it via queries, but I cant seem to get it to work.

this is what I did:

1) I first created a querie that gives me a detail of all the products the client bought. I have the "ClientID" and "DetailInvoiceID" and the "Quantity Field" and the "UnitPrice". I then created a Expression that gave me the "Total" = total:"Quantity field" * "UnitPrice".

2) I then created another query from the results of the above query: I have the "ClientID" and the "Total" and added the InvoiceID from the "Table Invoice". I then grouped it and sum the "Total" to get the total amount for the invoice.

3) Then I created a query from my Payments Table: I have the "PaymentID" and the "ClientID" and "AmountPaid" and I grouped them and sum the AmountPaid to get a total amount of all the payments.

"From both queries I get the overall amount owed and amount paid."

4) Then I created a query from both of the above queries: I related then with the "ClientID". I have the "CleintID" an the "SumOfAmountPaid" and the "SumOfAmountDue" i then created an Expression that gives me the new amount new that is = "SumOfAmountDue" - "SumOfAmountPaid"

"I hope am doing a good job of explaining"

Here is the problem:

It works GREAT right? Yes!!! I thought I had it made!!! Well nope!! jejeje.

What happens is that I created an Invoice for $50.00 and made a Payment for $25.00. I then ran the query and it worked!!! YYEEAHHH!!!. I then cleared the database and started to input all the invoices when I had like 25 of them I thought I see all the amount due and ran the query. To my surprise it was blank!!!!

Well I found out that it was blank because I had no payments and query does not work when I have no payments. And dont have any payments, not yet so I cant make a report with all the amounts due.


Thats why I though of doing it with the VB code and thought about adding the fields "Paid" "Applied" "AmountDue" to the tables.

But if you can help me to do it with the query and make it work it would be great.