Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    calculation based on number of records (2002)

    Hi, I would like to perform a calculation to return the percentage of responses received, based on the value in a field NumberReturned, divided by the number of records in a sub-form. And display this in a control on the main form. Could someone outline how to do this? I would be fine doing it based on 2 separate controls, I am stuck on how to determine the number of records in the subform.

    Cheers, Roger

  2. #2
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculation based on number of records (2002)

    In the footer of your subform:
    put a textbox whose controlsource is =Count(*)
    Call it txtCount and set its height to 0 and its visible property to False

    On your main form:

    Create a textbox and use the expression builder to point the controlsource to txtCount on the subform

    Your controlsourse will look something like this: =[subformcontrolname].[Form]![txtCount]
    where subformcontrolname is the name of the subform control NOT the subform itself.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculation based on number of records (2002)

    Hi, thank you, this calculation is working well. My problem now is that I can't get the txtCount text box to update when new records are added to the sub-form, it will only update when the main form moves to a new record (and back again). How can I get this control to refresh on the insertion of a new record? I am trying requery and refresh but not having success.

    Roger

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculation based on number of records (2002)

    When you move to a different record on the sub-form it should update. It only updates when Access saves the new record which happens when you move away from the newly created record.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculation based on number of records (2002)

    I thought that should happen too. Unfortunately I do not see any updating on moving to a new record on the sub-form. It will update if I delete a record on the sub-form or move record on the main form. Hence the idea to add some code to refresh the control on inserting a new record...

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: calculation based on number of records (2002)

    Try me.recalc in the after insert of the subform.
    This does the job for me.
    Regards
    John



  7. #7
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculation based on number of records (2002)

    thank you, this works for me to.

    Roger

Posting Permissions

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