Form SubForm question 1

[puforee]

I have a Form (Location) Sub-form (Customer) Sub Sub Form (Items Purchased)

On Items Purchased I have fieds of Quanity and Price...I also have a computed field =[Quanity]*[Price] named Total.

On the Customer sub form I want a text box (CustTotals) to compute the sum of all the "Total" fields on the Items Purchased form.

I used this in the CustTotals field of the Customers form

=Sum([Sales SubForm].[Form]![Total]) and I get #Error

I know I have done something like this before...what am I doing wrong

 

[MajP]

Do the sum on the items form. You can put this in a footer and make it invisible if you want.
call it "txtBxSumPurchases"

then on the customer form you can reference the textbox
=[subControlName].form.txtBxSumPurchases

 

[puforee]

OK...on the Items Purchased Form in the footer I put a text box with this as the source.. =Sum([Total]). When I open the Form it shows #Error.

By the way..I am runing Windows 7 64 with Office 7

 

[boblarson]

puforee:

Here's a sample of what MajP was talking about:

http://downloads.btabdevelopment.com/Samples/subforms/SAMPLE-SubformTotalOnMainForm.zip

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[boblarson]

Oh, and I forgot to mention. Sometimes, if you have troubles getting the exact syntax down for something like this, you can use something like this:

http://www.btabdevelopment.com/ts/refer2sfrms

to get the right syntax.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[MajP]

Yeah you cannot sum the unbound control.

so if you have Quantity and Price and an unbound control txtTotal = Quantity*Price. Then in the footer you have to reference the bound controls not the unbound total.

=sum([Quantity]*[Price])
not
sum([txtTotal]

 

[puforee]

Majp and all,

Thanks for your help. I have been away for a while and I can now get back to this project.

And Majp...you hit it on the head...that was exactly what I was trying to do.

 

[MajP]

http://support.microsoft.com/kb/207763

Here is a good explanation

 

[puforee]

I am still having issues. Review....I have a set of 3 forms... Location, Customer, and Sales. I have managed to show the Customer total by putting these calculated fields in the Sales Form footer...Customer Total =Sum([Quanity]*[Amount]). I also have a Tax calculation and a Grand Total calculation.

Now, I want to show the totals on the Location Form. This would give me totals for all Customers for the Location of the event. Since I cannot caculate against an unbound object, I am wondering how to proceed. I did place this computation in the Location Form Footer. =Sum([Customer Sub-Form].[Form]![Sales SubForm].[Form]![Amount]*[Customer Sub-Form].[Form]![Sales SubForm].[Form]![Quanity]) But this returns #Error. I am referencing two Bound Fields on the Sales Form...why won't this compute?

 

[MajP]

you may also want to look at using the dlookup and go directly to the underlying data. Make an aggregate query (group by location, and event and sums the quantity * amount) that contains the eventID, locationID, and the sales information. The query should be able to return data like
eventID LocationID TotalSales

event1 location1 100.00
event1 location2 150.00

...
eventN locationN 200.00

If you can make the query then you can use a dlookup on your form. I hate working with calculated controls because it is so easy to mess something up and you get no feedback. That is why I would prefer to do as much calculations in a query or code.

Your dlookup

=dlookup("TotalSales","qrySalesByLocation","eventID = '" & [txtBxEventID] & "' AND locationID = '" & [txtBxLocationID] & "'"

You can make it even simpler. If you get the query to work, then reference the form controls as your criteria. Something like

where locationID = Forms![frmLocation]![TxtBxLocationID] AND ...

The query builder can help get the naming correct.

Then you would return only one record and the dlookup becomes
=dlookup("TotalSales","qrySalesByLocation")

Doing it that way is a lot easier for me because I get immediate feedback from the query. If the query has the correct information, then I know a dlookup can show it on the form.

 

[puforee]

MajP,

Sounds interesting. It is obvious, after my first try, that I will need help. I tried setting a Unbound control to =DLookUp(([Sales Tbl]![Amount]*[Sales Tbl]![Quanity]),[Sales],[Sales Tbl]![Location]=[LocationID]) and I got #Name?.

So, can I attach the DLookUp to a control or were you speaking about doing it another way.

Thanks,

 

[MajP]

yes you can use a domain aggregate function in the control source on a form to pull data from any table or query.

Example in the control source;
=lookup("expression","domain","criteria")

The parmeters are string

=DLookUp(([Sales Tbl]![Amount]*[Sales Tbl]![Quanity]),[Sales],[Sales Tbl]![Location]=[LocationID])

Try

=DLookUp("TotalAmount","Sales","Location = " & [LocationID])

This assumes a query named Sales with field TotalAmount, and a control named LocationID on the form. If you have a query called sales then add the field totalAmount.

but you could probably do it in the dlookup

=DLookUp("Amount * Quanity","Sales","Location = " & [LocationID])

Check your spelling. Quanity or Quantity

http://wiki.lessthandot.com/index.php/Domain_Aggregate_Functions

 

[puforee]

I think we are closer. I have a query named "Sales Tbl Query" I have a calculated value in that query named Total.

I have used this in a contol located in the Form Footer of Location Form. =DLookUp("Total","Sales Tbl Query","Location = " & [LocationID])

I am now gettig values back...but not the sum of all the Totals for each Location. So I tried =Sum(DLookUp("Total","Sales Tbl Query","Location = " & [LocationID])) and now I am back to my #Error

 

[boblarson]

I am now gettig values back...but not the sum of all the Totals for each Location. So I tried =Sum(DLookUp("Total","Sales Tbl Query","Location = " & [LocationID])) and now I am back to my #Error

Are you sure that Location exists as the name of a field in the query? And, that Location in the query and LocationID are the same data/datatype?

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[puforee]

Yes, in the query Location is a field name. In the location table LocationID is an auto number. In the Salse table Location is a number.

So each location has Customers...each Customer has Items Purchased.

I need the total of all Items Purchase for All customers for each Location.

=Sum(DLookUp("Total","Sales Tbl Query","Location = " & [LocationID])) this is giving me an answer of $22.00. That is the total for Location 1 plus the total for location 2 first customer. For location 2 I have 7 Customers and 9 purchases. One Customer had 3 purchases. Everyone else had 1 each.

 

[boblarson]

why not include LocationID in your query and then you can use

"LocationID = " & [LocationID]

Which seems to me that it would get you the correct data.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[puforee]

boblarson. You suggestion did not change the results...but thanks for it anyway.

 

[boblarson]

If your query totals the values for that location, then it should bring back the correct results. Otherwise you may want

DSUM

instead of DLookup.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[puforee]

boblarson,

Fantastic...DSum worked like a champ.

I will put this to use for my other calculations.

MajP...I want to thank you for getting me on the right track also.

I have not used these D functions before.

Both of you have improved my education.

I want to appologize for not using stars...I just don't know how to attach them. But...:-D, :-D, :-D

John

 

[boblarson]

I want to appologize for not using stars...I just don't know how to attach them.

Just an FYI -

You click on the link at the bottom of the individual thread that says:

Thank xxxxk
for this valuable post

And that adds a star to that post.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com