Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    =SUM etc (A2000)

    I am creating a form which will summarise totals from many other forms.

    I'm getting a little stuck with the syntax.

    All the Summing controls are in the footers of the forms and calculating just fine.

    A little help I'm sure will start the ball rolling.

    e.g

    sbfEstimateDetails is a subform on frmEstimateDetails, in the footer is an unbound text called [SUMnew]

    I need to collect this calculation on frmSummary. I've created an unbound on this form which states,

    =[sbfEstimateDetails].form![SUMnew]

    I get Error#

    Dave

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =SUM etc (A2000)

    The form you are referencing from frmSummary needs to be open. If you don't want it to be seen, you can open it (e.g., from the frmSummary's FormOpen event -- remember to close it in the FormClose event) and set its Visible property to False.

    Hope this helps.

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

    Re: =SUM etc (A2000)

    You are referencing a control on sbfEstimateDetails which is a subform of frmEstimateDetails.

    This should work as follows:

    =Forms![frmEstimateDetails]![sbfEstimateDetails]![SUMNew]

    As Tom remarked, frmEstimateDetails needs to be open.

    Note: as you move from record to record in frmEstimateDetails, the text box in frmSummary won't be updated. If you open frmSummary from frmEstimateDetails as a modal form (i.e. the user has to close it before returning to frmEstimateDetails), this is not a problem. Otherwise, if you want the text box to update, you must put a command button on frmSummary that requeries the text box.

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =SUM etc (A2000)

    Hmmmmmmm

    Well I really didn't want to have the form open, I wanted the Summary form as a stand alone.

    Perhaps I need to look along other ways to do this, SQL maybe.

    Thanks any-way.

    Dave

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

    Re: =SUM etc (A2000)

    Hi Dave,

    You can't expect Access to refer to values in a form that hasn't been opened - the value just doesn't exist.

    Besides, if sbfEstimateDetails is linked to frmEstimateDetails by Child/Master fields, the value of SUMNew depends on which record is displayed in frmEstimateDetails. So if frmEstimateDetails is closed, what should SUMNew be?

    You'll have to rethink what you actually want. Perhaps you can do something with the DSum function:

    DSum("expression", "domain", "criteria")
    Expression is the name of the numeric field you want to sum (or an expression that returns a numeric result)
    Domain is the name of a table or query
    Criteria is optional; it is the WHERE part of a SQL statement (without the word WHERE itself).

    Example:

    =DSum("EstimateAmount", "tblEstimateDetails", "Year(EstimateDate) = 2002")

    returns the sum of the EstimateAmount field in tblEstimateDetails for the records whose EstimateDate is from the year 2002.

    Regards,
    Hans

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =SUM etc (A2000)

    Thanks again Hans,

    I'll give it some thought, your way seems to in the direction I need.
    Basically take the caldulation direct from the table instead of the form. Its a bit clearer now.



    dave

Posting Permissions

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