Problem With Displaying Subform Field in Parent

[pangono]

Hope you can help. Having problems displaying a calculated field in a parent form.

I have a form Stock and a subform StockSubform2. In StockSubform2 I have a field (txtMaxUnitCost) calculating the highest cost of a number of stock purchases. The expression to calculate the highest cost is:

=Max([txtUnitCost])

. This is the name of the control not the table field.

Then in the parent form, Stock, I link to this control with:

=Forms!Stock!frmStockSubform2!txtMaxUnitCost

.

I get an #Error message and I'm not sure what is wrong.

 

[DrSnuggles]

Which version of Access is it?

Try:
=Forms!Stock.frmStockSubform2.txtMaxUnitCost.value

or

=frmStockSubform2!txtMaxUnitCost.value

or

=Me.frmStockSubform2!txtMaxUnitCost.value

 

[pangono]

I'm using Access 2002 but the file is 200 format so that they can use it on their computer.

I tried each version but no luck. Still get #error or #name? error.

 

[Wiz47]

Hope you can help. Having problems displaying a calculated field in a parent form.

I have a form Stock and a subform StockSubform2. In StockSubform2 I have a field (txtMaxUnitCost) calculating the highest cost of a number of stock purchases. The expression to calculate the highest cost is:

=Max([txtUnitCost])

. This is the name of the control not the table field.

Then in the parent form, Stock, I link to this control with: [CODE]=Forms!Stock!frmStockSubform2!txtMaxUnitCost [/CODE].

I get an #Error message and I'm not sure what is wrong.

Change it to the below and it should work.

=frmStockSubform2.form!txtMaxUnitCost

* I need to add that the reason it didn't work before is that you have to reference the subform control so the form knows where the data is coming from. That's why you add the ".form" in the code.

 

[pangono]

Sorry, tried that as well and I still get an error.

 

[Wiz47]

Sorry, tried that as well and I still get an error.

Hmmm. I use this method to do exactly what you are requesting - pull data from a subform to a main form. Can you put a stripped down version of your db here so we can have a look? Obviously something else is going on that we're not aware of - and we want to help.

 

[pangono]

Appreciate the assistance, this is driving me crazy. I've probably made some basic error elsewhere.

The relevant bits of the db are:

Tables
Stock
StockID PK
SupplierID
CategoryID
LocationID
Name
Brand
Size
ReorderLevel

Inventory
InventoryID PK
StockID
Date
UnitCost
UnitsPurchased
UnitsSold
Wastage

Forms
Stock
Record Source: Stock

frmStockSubform2
Record Source: Inventory

I've attached an image of the relationships.

If you need more let me know.

 

[Wiz47]

Appreciate the assistance, this is driving me crazy. I've probably made some basic error elsewhere.

If you need more let me know.

In order to help, I need an actual stripped down version of the db. Just remove any proprietary/personal information (if any), throw in some "fake" data and then we'll have a look. I need to be able to see where you put the fields in the form and subform, what you named them, and how they relate to one another in the forms.

 

[pangono]

Problem with Displaying Subform Field in Parent

Here it is, nothing personal in it.

 

[Wiz47]

Here it is, nothing personal in it.

Problem one:
Remove the "txt" from the fieldname in the = code in the footer of the subform. You are trying to sum the name of the textbox and not the value.

I'll look at it some more later, but this should point you in the right direction. BTW, that's a very nice looking form.

 

[Wiz47]

I added an unbound textbox to the footer called txtTotalCost - did the calculations there (then referenced it in the main form) Very important - Remember to do all of your calculations in the subform, then just call the name of the unbound box in the subform (from the main form) and it will carry those calculations to the textbox.

I'm not sure you want to use the Max(Unitcost) for that calculation because it really doesn't give you the true total cost. I would use a sum of the unitcost * itemsordered to get the true total.

 

[pangono]

Wiz,

Thanks for the help and the feedback. You're a lifesaver.

 

[pangono]

Problem With Displaying Subform Field - Quantity Wrong

Ok, new problem though ...

Sometimes the quantity is correct, other times it's wrong.
Eg.

• The first 3 records work fine.
• Number 4 has 4 units purchased and a total of 2 units sold so should be quantity of 2 but actually shows 6.
• Number 5 has 5 units purchased and a total of 2 sold so should be a quantity of 3 but is showing 1.

How can it be right for some batches and not for others (and in different ways)?

 

[pangono]

Ignore that, I am an idiot and didn't scroll.

 

[Wiz47]

Glad I could help.