Getting a sum from inside a subform and displaying in the main form

[bennyB]

Displaying a value from a subform in the main form! its fun++

Hi

i have a text box in my subform which i have calculating the sum of a few other values in the subform.

how can i then display this value on the main form???

thanks

 

[Rusty]

Hi Benny,

You need to set the control source of the textbox on your Main Form to:

=[subformname].[Form]![fieldname]

Change the blue bits to match the name of the subform and the fieldname of the textbox on that subform.

Hope that helps,

Rusty

 

[RussG]

Hi - I am passing a total from a sub form to the main form, however if there are no values in the subform (i.e. no entries so far) the text box is showing an #error. How can I hide the error.
Cheers
RussG

 

[boblarson]

Use the NZ function

=Nz([subformname].[Form]![fieldname],0)

 

[RussG]

Thanks for your reply, that doesn't seem to work? Totals are still passed but I am still getting the error.
Cheers
RussG

 

[agehoops]

try using Me!Subform1.Form!ControlName but change Subform1 to the name of your subform and ControlName to the name of the field you want the value from. Also if that fails try using .value after the control name.

 

[RussG]

No that doesn't work at all.

The strange thing is this is the same setup as on the orders form in Northwind. No errors show in the 'subtotal' text box on the orders form when there are no orders on the sub form but I can't see any extra 'bits' to stop an error showing.
(so why am I getting the error)

If anyone has any other ideas they would be much appreciated.

Cheers
RussG

 

[agehoops]

Tell you what, put a copy of the database on here and the fields that you're having problems with and I'll take a look at it

 

[RussG]

Will do but I will need to clean it up a bit first , I am testing stuff at the moment to see the best techniques to use and the test DB is a bit of a mess. I will try and do it over the next couple of days.

One thought did occur to me earlier when I had another look at the Northwind DB. In Northwind creating a new order automatically creates a new record in the sub form. If I create a new record there is no data in the subform , hense the #error? Would this be the answer? If so how would I create a new record in the sub form when I go to a new record on my main form?

Thanks for your help.
RussG

 

[agehoops]

If there is nothing at all in the field in the sub form then you will get the error, as it is trying to set a value that doesn't exist. Does it work when there is a value in there? Your system should automatically be creating a new record in the sub form however a new record is a blank record so everything will be blank, unless you have default values (which you'll have to specify)

If it is working when there is a value, you could put a clause in which it checks to see if there is a value in the field in the sub form, if there isn't do nothing, and if there is, make the field that value.

If that makes sense.

 

[RussG]

Thanks for that, yes all makes sense. I had tried setting defaults on the sub form but it didn't work, for some reason the system isn't creating a new record on the sub form when I create a new record on the main form. I will have to check how I have set this up, something not right somewhere.
Cheers
RussG

 

[RussG]

Sorted the problem, I had allow edits, deletions and additions set to NO.
Thanks for your help chaps, got there in the end.
RussG

 

[Simon_MT]

I find that it is good practice to find a numeric field on the subform and test for isnumeric. Having allow edits, deletions and additions if a use sits on "New Record" and you want to examine the information on the subform in more detail you can ensure that a record exists and open another form using that subforms record.

Simon