form with subform

[Kelly@AllenEnt]

Hello everyone,
I have a form with subform created to track payments against a particular job. I have a field on the main form that is labeled "OUTSTANDING". What I need is the payments to subtract from the outstanding amount automatically. Is there a way to do this? A copy of what the form looks like with fictitious information added just for show of the problem. I do not have a subtotal on the query that pulls this information because it is normally done on our report. I need the "Outstanding" to store in the "ORDERS' table. I am working in Access 2007

 

[bob fitz]

I need the "Outstanding" to store in the "ORDERS' table.

I'm not sure if you are talking about the word "Outstanding" or the actual amount, but both are really calculations and they should not normally me saved to a table but calculated whenever and wherever they are needed. See: http://allenbrowne.com/casu-14.html

 

[Kelly@AllenEnt]

We have a set amount of commission that is to be paid to us. That is typed in the "COMMISSION' column. "OUTSTANDING' needs to be the "COMMISSION" minus all of the "PAYMENT AMT" for that order. I think I should store it is a query and that would be okay.

I guess I need to create a query that some how adds the payments for a particular job then subtracts it from the "outstanding" column. How do I do this?

 

[The_Doc_Man]

In general, you store transactions in a table that includes the job number. Your transactions might include things like "Charges" (with the amount shown with one sign) and "Payments" (with the amount shown as the opposite sign). There would be a pot-load of possible transactions, but the end analysis is that they are either credits or debits. A simple summation query that selects for a specific job would then tell you the instantaneous balance of the job's account.

 

[bob fitz]

If I have understood your requirements correctly, I would create a text box in the footer section of the subform that gives the total amount of "PAYMENT AMT". This text box can be hidden if you don't want to see it. Put an expression as the Control Source property of the "OUTSTANDING" text box. Something like:
= [COMMISSION] - Forms!MainformName.SubformName.Form.NameOfTotalsTextbox
You would need to substitute the names in red for the names you use.

 

[Kelly@AllenEnt]

I have added the text box in the footer and it works perfectly. I placed the following in the control source of "OUTSTANDING" and I have error message "#Name?" appearing.

= [COMMISSION] - Forms![ORDERS ENTRY FORM WITH NAMES, RATE, AND PAYMENTS].[PAYMENTS Query Subform].Form.[PAYMENT SUBTOTAL]

I copied and pasted the names of each field so I could not make a mistake. I am not sure why it is not working.

 

[bob fitz]

I don't know why it is not working either but the error message suggests to me that access does not understand the reference to the totals text box on the sub form. I'm wondering if that is because of the comers in the name of the main form. I believe access gets upset when they are used in field and table names. Can you remove the comers from the form name and the expression and see if that works.

 

[bob fitz]

Attaching a copy of you db was going to be my next suggestion, but I can only use it if it is in A2003 mdb format. If you can attach a copy I am more than happy to have a play with it.

 

[bob fitz]

Ok, thank you. I'll take a look and post back when I have something to offer.

 

[bob fitz]

OK. I think it is now working as required.
I have added a text box to the main form that references the totals textbox on the subform. This new text box is now referenced in the expression used in the COMMISSION text box. The new text box can of course be moved and hidden if required.

 

[Kelly@AllenEnt]

It works beautifully. Thank you for taking the time to help me. I greatly appreciate it!

 

[bob fitz]

It works beautifully. Thank you for taking the time to help me. I greatly appreciate it!

You're welcome. Glad to help. Please post back if you need any clarification on its workings.

 

[Kelly@AllenEnt]

I hate to bother you again but I have a question. The information is in the form and that is great. But my query that the form is created from "ORDERS W/NAMES & RATE Query" is not saving the information in the "OUTSTANDING' column.This is how my boss will view the information. Also, I will need to create a report based on that query so he can see the jobs that are still awaiting payments (OUTSTANDING >0). Until I can correct the original query, I cannot create the report. Are you able to help with this?

 

[bob fitz]

As I said in my first post, OUTSTANDING is a calculation and calculations should not normally be stored in a table. If you need this information in a report, then the calculation should be done in the report or in the reports query.
The Control Source property of the OUTSTANDING text box is no longer bound to a field in the underlying table/query which is why it is not being saved in the table.

 

[Kelly@AllenEnt]

I didn't say stored in the table, I said in the QUERY. I have stored calculated fields in queries before. I need the "OUTSTANDING" calculation to appear when the Query is ran.

How should I update the "ORDERS W/NAMES & RATE Query" so it will show the "OUTSTANDING" calculation?

 

[bob fitz]

Perhaps this is what you are seeking:
SELECT ORDERS.[CUSTOMER #], CUSTOMERS.[CUSTOMER NAME], ORDERS.[ADB ORDER #], ORDERS.[ORDER DATE], ORDERS.[ORDER AMOUNT], ORDERS.COMMISSION, [COMMISSION]/[ORDER AMOUNT] AS RATE, ORDERS.OUTSTANDING, ORDERS.[EXPECTED SHIP DATE], ORDERS.[LAST PAID DATE], ORDERS.[COMPLETED?], ORDERS.[PAID?], ORDERS.CODE, [Commission]-Nz(DSum("[PAYMENTS]![PAYMENT AMT]","[Payments]"," [PAYMENTS]![ADB ORDER #] = '" & [ADB ORDER #] & "'"),0) AS CommissionBalance
FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.[CUSTOMER #] = ORDERS.[CUSTOMER #];

 

[bob fitz]

BTY I would advise you to make some changes in the way you have named the objects in your DB. Take a look at the link below, in particular, the chapter headed "Names"
http://allenbrowne.com/binary/Access_Basics_Crystal_080220_Chapter_03.pdf

 

[Kelly@AllenEnt]

A copy of the database is already attached. Is there a way to revise the one field in the already created query "ORDERS W/NAMES & RATE Query" so it will show the "OUTSTANDING" calculation without making a completely different query?

Also, how do I add the payments subtotal to that query?

I will work on the naming structure based on the chapter you attached earlier.

 

[bob fitz]

If the SQL I suggested in post 16 gives the desired results you could you not use that to replace the existing SQL in the query "ORDERS W/NAMES & RATE Query".

 

[bob fitz]

I have made the changes for you. See attached DB.