Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculated fields (Access 97)

    Ok, here's my next problem!

    I have a form which calculates a quotation value after the costs and profit figures are entered. It works fine.

    Now I want to display the calculated quotation value on a separate sub form but all I get in the text box is "#Name?"

    The sub form is not a sub form of the quotation form.

    All thoughts gratefully received!

    Graeme

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculated fields (Access 97)

    The link I posted in <post#=253116>post 253116</post#> to the tips on referring to controls on (sub)forms should help here too. If you want more help, please give a more detailed description. Just "on a separate subform" doesn't say much.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated fields (Access 97)

    More information as requested:

    The first option of the Switchboard opens a form displaying company details
    The company details form includes a sub form displaying a summary of all the addresses of that company
    A command button on the addresses summary sub form opens the address details form for that company
    The address details form includes a sub form displaying a summary of all the contacts at that address
    A command button on the contacts summary sub form opens the contacts details form for that address
    The contacts details form includes a sub form displaying a summary of all the communication with that contact
    A command button on the communication summary sub form opens the communication details form for that contact
    The communication details form includes a sub form displaying a summary of all the quotations associated with that communication
    A command button on the quotations summary sub form opens the quotation details form associated with that communication
    When the costs and the profit figures have been entered onto the quotation detail form the calculated field "tendertotal" is displayed. This figure is not stored but only exists when the form is opened.
    What I would like to be able to do is display the "tendertotal" figure on the quotations summary sub form.
    Following is the code (which doesn't work!) in the tendertotal text box on the quotations summary sub form
    "Forms![ForTabQuoteDetail]![TenderTotal]"

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculated fields (Access 97)

    Thanks for the explanation. From your description, I assume that the ForTabQuoteDetail form is a main form, not a subform. The Control Source property of the TenderTotal text box on the quotations summary sub form should be

    =[Forms]![ForTabQuoteDetail]![TenderTotal]

    Note the = sign at the beginning (this signals a calculated value) and the absence of quotes.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated fields (Access 97)

    Thanks Hans

    What you suggest is what's there!

    I put the quotes in just before I posted the message 'cos the code disappeared when I did your spell check! The = sign must have got over written.

    And this is the frustration of the problem in that the code was entered according to the book and yet it still doesn't work!

    Any more thoughts?

    Regards

    Graeme

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculated fields (Access 97)

    A Name# error indicates that Access can't find what you refer to. So check very carefully whether the exact name of the form is ForTabQuoteDetail and whether the exact name of the control on this form is TenderTotal. Also, the ForTabQuoteDetail form must be open, otherwise Access doesn't recognize the expression either. This means that when you first open the Communication Details form, the value will be unknown. You will probably have to force a recalculation when you return from ForTabQuoteDetail.

  7. #7
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated fields (Access 97)

    In an effort to put this problem to bed I've knocked up a sample db to show what the problem is.

    I need know how to replace the #NAME? error message on the FrmTblNumber subform with the Sum Of Numbers calculated field on the corresponding FrmTblNumber form.

    Check the attached and perhaps this will make sense!

    Regards

    Graeme
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculated fields (Access 97)

    It's a combination of factors I mentioned in my previous reply.
    <UL><LI>Spelling difference: you refer to the other form as TblNumber, but it is called FrmTblNumber.
    <LI>You don't prefix the form name with Forms!
    <LI>Since FrmTblNumber is not open when FrmTblName is opened, the reference to FrmTblNumber is not recognized, even if it is set up correctly.[/list]You must change the Control Source of the Text4 text box on the subform to

    =[Forms]![FrmTblNumber]![SumOfNumbers]

    You can update it when FrmTblNumber has been opened. You might even set its Visible property to False in design view, so that it starts out hidden, and make it visible when FrmTblNumber is shown.

    Private Sub Command6_Click()
    ...
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Me.Text4.Requery
    ' Next instruction is optional
    Me.Text4.Visible = True
    ...
    End Sub

  9. #9
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated fields (Access 97)

    Thanks for the reply

    I think we're there now!!!

    My reference to TblNumber was an attempt to get the data from the table and not the form. (I should have said!)

    The root of the problem is, as you say, that the data is not available until the form is opened. But it will never be open at the stage I want the total figure displayed! So I've put the fields with the data onto the subform no longer looking for them on a closed form. They are not visible to keep the sub form as a means of displaying a summary of the data. As per the attached.

    Thanks for the inspiration!

    I'll use your code to hide the value text box when there is no figure to display.

    Thanks again!

    I'll be back!

    Graeme
    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
  •