Professional Web Applications Themes

Total amount in form footer - Microsoft Access

Hi, I have a subform with a form footer that I want to display the count of records on the subform. Is there a way to do this on the form itself without using the query the form is based on? The record source of the subform changes depending upon what data mode the parent form is opened in. I can make a new query from the record source of the subform to return the totals or count but it won't let me refer to it in the sub-form footer. There are dog and cat records displayed on the subform ...

  1. #1

    Default Total amount in form footer

    Hi,
    I have a subform with a form footer that I want to display the count of
    records on the subform. Is there a way to do this on the form itself
    without using the query the form is based on? The record source of the
    subform changes depending upon what data mode the parent form is opened in.

    I can make a new query from the record source of the subform to return the
    totals or count but it won't let me refer to it in the sub-form footer.
    There are dog and cat records displayed on the subform and in the footer I'd
    like to display the total number of dogs and the total number of cats. Each
    record in the subform has a field called [SpeciesCode] with either a "D" for
    dog or a "C" for cat. How can I write the control source for TotalDogs so
    that it displays the count of the D's in the [SpeciesCode] column of the
    subreport?

    Joan


    Joan Guest

  2. #2

    Default Re: Total amount in form footer

    Joan,

    A couple of ways to do this. Here is one...

    On your subform put a textbox for each species that you need
    and mark them visible = False. This will hide them and you
    can make them small enough that they won't get in the way.
    Name them something like, CountDogs, CountCats, etc... For
    each put in code in this fashion for the ControlSource...

    For CountDogs...
    =If(Me!SpeciesCode="D",1,0)

    This will fill the box in with a one if that record is a
    dog. If it is a "D" give me a one, else give me a zero.

    Now in your subform footer's TotalDogs ControlSource put
    in...

    =Sum([CountDogs])

    ....and this will add those hidden controls up.

    In a report this is much easier as you can group by Dogs,
    Cats, etc... and just use a count() in the group footer. No
    grouping in your subform.

    Gary Miller
    "Joan" <net> wrote in message
    news:e$phx.gbl... 
    the count of 
    form itself 
    source of the 
    form is opened in. 
    subform to return the 
    sub-form footer. 
    in the footer I'd 
    number of cats. Each 
    with either a "D" for 
    for TotalDogs so 
    [SpeciesCode] column of the 


    Gary Guest

  3. #3

    Default Re: Total amount in form footer

    Thanks so much, Gary!

    I followed your instructions and it works great! I guess I will have to
    learn to be a bit more imaginative.

    Thanks again,
    Joan



    "Gary Miller" <com> wrote in message
    news:uX26PJ%phx.gbl... 
    > the count of 
    > form itself 
    > source of the 
    > form is opened in. 
    > subform to return the 
    > sub-form footer. 
    > in the footer I'd 
    > number of cats. Each 
    > with either a "D" for 
    > for TotalDogs so 
    > [SpeciesCode] column of the 
    >
    >[/ref]


    Joan Guest

Similar Threads

  1. Calculating % based off Total in footer.
    By Alvin Bruney [MVP] in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: February 15th, 04:35 AM
  2. Total Function in a Form
    By John in forum Microsoft Access
    Replies: 2
    Last Post: September 6th, 10:59 PM
  3. Sum Calc Field In Form Footer
    By Steve Schapel in forum Microsoft Access
    Replies: 3
    Last Post: July 27th, 06:15 PM
  4. Total ink amount VS readout
    By Adam Bilinski in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 1
    Last Post: July 15th, 08:21 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139