Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Subform Values

Discussion in 'Information Technology' started by F1stman, Jul 28, 2006.

Tags:
  1. F1stman

    F1stman
    Expand Collapse
    Guest

    Hello Everyone,
    I have created a database with a number of subforms, each in form view,
    which display appropriate calculated sums from a query. My problem lies in
    that on one of my forms there are 6 tabs and to begin with all are empty.
    Only over time will all be filled with data which will produce the necessary
    sums. In the meantime I need to produce reports with the info from all six
    categories. How can I get those subforms to display a zero untill data is
    available. I can't make an Is Null argument work. Any thoughts? Thank you in
    advance. Adam
     
  2. Loading...


  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "F1stman" <F1stman@discussions.microsoft.com> wrote in message
    news:674A1E53-3D9F-4FF3-BC1D-7EE9F789D158@microsoft.com
    > Hello Everyone,
    > I have created a database with a number of subforms, each in form
    > view, which display appropriate calculated sums from a query. My
    > problem lies in that on one of my forms there are 6 tabs and to begin
    > with all are empty. Only over time will all be filled with data which
    > will produce the necessary sums. In the meantime I need to produce
    > reports with the info from all six categories. How can I get those
    > subforms to display a zero untill data is available. I can't make an
    > Is Null argument work. Any thoughts? Thank you in advance. Adam


    It's not clear from your description how you have these subforms set up
    and where you want the zeros to be captured or displayed, but the
    following link may put you on the right track:

    http://www.mvps.org/access/forms/frm0022.htm
    Forms: #Error when the Subform has no records

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  4. Sprinks

    Sprinks
    Expand Collapse
    Guest

    Set the report control source to:

    = Nz([YourSummaryExpression],0)

    Sprinks

    "F1stman" wrote:

    > Hello Everyone,
    > I have created a database with a number of subforms, each in form view,
    > which display appropriate calculated sums from a query. My problem lies in
    > that on one of my forms there are 6 tabs and to begin with all are empty.
    > Only over time will all be filled with data which will produce the necessary
    > sums. In the meantime I need to produce reports with the info from all six
    > categories. How can I get those subforms to display a zero untill data is
    > available. I can't make an Is Null argument work. Any thoughts? Thank you in
    > advance. Adam
     
  5. F1stman

    F1stman
    Expand Collapse
    Guest

    Thank you both for the help. Unfortunately neither method is working. I can't
    fiure out the proper place to put the code from Dirk and the nz statement
    from Sprinks isn't working either.
    To clarify I have a financial tracker with 6 tabs representing 6 accounts
    per project. Each projects individual sales are tallied via a query and then
    displayed via a subform on the appropriate tab. In each sub form only one
    object is displayed (the total). I have a report that uses the information
    from this form to create a financial exhibit. When there are no values, the
    form displays a blank area and the report an error. I would like to get both
    to display a zero. Any more help is appreciated or perhaps clarification on
    the coding instruction given before. VBA rookie here.

    "F1stman" wrote:

    > Hello Everyone,
    > I have created a database with a number of subforms, each in form view,
    > which display appropriate calculated sums from a query. My problem lies in
    > that on one of my forms there are 6 tabs and to begin with all are empty.
    > Only over time will all be filled with data which will produce the necessary
    > sums. In the meantime I need to produce reports with the info from all six
    > categories. How can I get those subforms to display a zero untill data is
    > available. I can't make an Is Null argument work. Any thoughts? Thank you in
    > advance. Adam
     
  6. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "F1stman" <F1stman@discussions.microsoft.com> wrote in message
    news:A69DAB0A-7CED-4D68-8A90-44CB977E77D1@microsoft.com
    > Thank you both for the help. Unfortunately neither method is working.
    > I can't fiure out the proper place to put the code from Dirk and the
    > nz statement from Sprinks isn't working either.
    > To clarify I have a financial tracker with 6 tabs representing 6
    > accounts per project. Each projects individual sales are tallied via
    > a query and then displayed via a subform on the appropriate tab. In
    > each sub form only one object is displayed (the total). I have a
    > report that uses the information from this form to create a financial
    > exhibit. When there are no values, the form displays a blank area and
    > the report an error. I would like to get both to display a zero. Any
    > more help is appreciated or perhaps clarification on the coding
    > instruction given before. VBA rookie here.


    You would paste the code from that link into a standard module. Then
    you would wrap references to the troublesome controls in calls to the
    nnz() function; e.g.,

    =nnz([Forms]![YourForm]![YourSubform]![YourControl])

    or
    =nnz([Forms]![YourForm]!![YourControl])

    depending on whether the control in question is on the subform or on the
    main form.

    But I still don't have a good understanding of the way you have this set
    up. If you're trying to display these results in a report, it makes
    little sense to refer to a form for the data -- that requires, for
    example, that the form always be open when the report is run. Normally,
    one would define the report's recordsource query so that it calculates
    and returns all the information you need to display on the report.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     

Share This Page