Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Posts
    5

    Post Unanswered: Calculated fields

    I have a calculated value on a form that i want to display in a subform on another form. Does anyone have any thoughts as to how this can be done??

    Any advice would be greatly appreciated
    Last edited by Troopa; 12-23-03 at 13:32.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Calculated fields

    Originally posted by Troopa
    I have a calculated value on a form that i want to display in a subform on another form. Does anyone have any thoughts as to how this can be done??

    Any advice would be greatly appreciated
    If that calculate value is in a control on the form, just grab it within the form that you wish to use it on. If it's in a variable, then that variable will need to be global otherwise no dice.

  3. #3
    Join Date
    Dec 2003
    Posts
    5
    The calculated value is called GrandTotal and is stored in a textbox but when i bind the textbox to a control on a subform on another form, it just returns £0.00 as it isn't stored in the source table.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by Troopa
    The calculated value is called GrandTotal and is stored in a textbox but when i bind the textbox to a control on a subform on another form, it just returns £0.00 as it isn't stored in the source table.
    So you're saying that :

    MyTextControl.Value=Forms!MyOtherFormNameHere!MyOt herTextControl.Value

    Doesn't work?

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    mytextcontrol = [subformname].Form![controlname]

    it also depends from where you are trying to call or get that value
    if you are on the subform it would be

    me.parent!mytextcontrol = me!controlname

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Re: Calculated fields

    Originally posted by Troopa
    I have a calculated value on a form that i want to display in a subform on another form. Does anyone have any thoughts as to how this can be done??

    Any advice would be greatly appreciated
    There's a few ways you can go about this. The first question is whether all the forms will be open at the same time. If so, then you can reference the value directly by calling forms!yrForm!yrtxtBox.value on all subsequent forms. Other options include passing the intial value as a recordsource variable.

  7. #7
    Join Date
    Dec 2003
    Posts
    5
    Thanks for your replies

    The problem is that the forms won't be open at the same time so i won't just be able to reference them

  8. #8
    Join Date
    Dec 2003
    Location
    indonesia
    Posts
    9

    Re: Calculated fields

    Have you tried using QUERY???
    This query seem like calculate any table.. and then linked to your subform...

    Maybe help!


    Originally posted by Troopa
    I have a calculated value on a form that i want to display in a subform on another form. Does anyone have any thoughts as to how this can be done??

    Any advice would be greatly appreciated

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the forms won't be open at the same
    so you need to store the value someplace.

    a (used to be called "global") module will do.
    global myCalculatedValue as Double 'or whatever type is appropriate

    you can then save the value before killing the first form
    myCalculatedValue = GrandTotal

    and retrieve it elsewhere
    subformControlName.subformTextBox = myCalculatedValue

    make sure you don't leave spurious values in myCalculatedValue

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Dec 2003
    Posts
    5
    Thanks for your replies

    izyrider.........the problem is that i want to return multiple values so your method won't work as it only stores the value of the record that has the focus. Can you think of a way to pass multiple values from one form to another??

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    yes: use a temporary table.
    you need to post some details if you want a more detailed answer.

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Dec 2003
    Posts
    5

    Talking

    Thanks for your reply.....your advice is appreciated

    OK I have attached the database itself so that you can see what I am talking about.

    Details on customers are stored in the form (frmCompany). These customers can make many orders and details of these orders are entered on to the form (frmSales).

    I have created a subform (tblSales subform) on the form (frmCompany) which displays a summary of all the details made by each customer. It displays the DocumentNumber, the OrderDate and the GrandTotal of each order made.

    The DocumentNumber and the OrderDate are simply bound to the source table (tblSales) but, as the GrandTotal value is calculated on the form (frmSales) it can't be bound to the source table and so can't be looked up and displayed. This value consequently needs to be passed from the form (frmSales) to the subform on the form (frmCompany). Also........... as if this wasn't a big enough problem as it already is, some customers may have made multiple orders and therefore the function will need to return multiple values.

    Hope that explains it all....................if you need any more information then just ask.

    Thanks again
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •