Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tab Control Page: Totals (2002)

    I have tab control pages set up in my form. On the tab control pages, I have a number of fields for data entry ... to enter dollar amounts. ie: permit fees, reinspection fees, water meter fees, etc... I used a text box on the tab control page to total all of these fields:
    =[Building Fee]+[Plumbing Fee]+[Re-Inspection Fee]+[Water Connection Fee]+[Sewer Connection Fee]+[Water Meter]+[Entrance Fee]+[Pin Fee]+[TownshipDCA]+[EDCP]+[EDCS]

    This works great... however, I need to get that total into a field in my table:

    Main Form: "PERMIT TRACKING"
    Table: "TRACKING"
    Field: "Permit Fees"


    Anyone know how I can do this?

    Any help would be greatly appreciated.

    Thanks!

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

    Re: Tab Control Page: Totals (2002)

    The total is a calculated value. If the contributing elements (building fee, plumbing fee etc.) are stored in the table, also storing the total is redundant. If you don't store the individual elements, you can use the After Update event of each of the contributing text boxes to update the Permit Fees field:

    Private Sub Building_Fee_AfterUpdate()
    Me.[Permit Fees] = Me.[Total]
    End Sub

    where Total is the name of the text box calculating the total. Alternatively, you could update it in the Before Update event of the form:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.[Permit Fees] = Me.[Total]
    End Sub

  3. #3
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Control Page: Totals (2002)

    Hans,

    The contributing elements are stored in the table. The problem is, that when I run a monthly report, I need the "Permit Fees" Total to show in the report... not the contributing elements. Any ideas?

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

    Re: Tab Control Page: Totals (2002)

    Create a query based on your TRACKING table.
    Add TRACKING.* to the query grid.
    In the next empty column, enter

    Total: [Building Fee]+[Plumbing Fee]+[Re-Inspection Fee]+[Water Connection Fee]+[Sewer Connection Fee]+[Water Meter]+[Entrance Fee]+[Pin Fee]+[TownshipDCA]+[EDCP]+[EDCS]

    Save the query as (for example) qryTracking. Set the Record Source of the form to this query and set the Control Source of the Total text box to the Total field. Use the query as Record Source for the report too.

  5. #5
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Control Page: Totals (2002)

    Hans,

    I'm doing something wrong. I'm attaching a word file that has pics of what I'm doing. I think something is wrong with the text box. I'm a little confused with your instructions about the "Total text box to the Total field". I have a text box that displays a total but doesn't actually store data ("Total"). I have a field in my table where I want to store the totals called "Permit Fees". See attached file and let me know what you think.

    Thanks again for all your help.

  6. #6
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Control Page: Totals (2002)

    Here's one more file with pics....

    Thanks

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

    Re: Tab Control Page: Totals (2002)

    You have built the query correctly. If you open the query in datasheet view, you should see all fields from the TRACKING table plus an extra column Total.
    You should set the Record Source property of your form to TOTALSqry (the name of your query) and the Control Source property of the Total Permit Fees: text box to Total (the name of the extra column in the query). It should then correctly display the total fees.

  8. #8
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Control Page: Totals (2002)

    Hans,

    Thank you for all the help. It's working perfectly. However, it seems to have opened up a can of worms with my form. (changing the data source that is)
    I've attached the code from my "before update" event procedure. I'd really appreciate any assistance you can offer.

    Thanks.

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

    Re: Tab Control Page: Totals (2002)

    Umm, you post a lot of code and mention "a can of worms", but you don't state a specific problem or pose a specific question, so I don't know what to do with it.

  10. #10
    New Lounger
    Join Date
    Feb 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Control Page: Totals (2002)

    Hans,

    My apologies, I was a little frustrated yesterday. Today I'm thinking a little clearer and have some questions.
    When the user saves changes to a record on my main form "PERMIT TRACKING", they are prompted with "Do you want to save?" They then have the option to click "yes" or "no". Clicking yes seems to be working on my form and tab control pages.
    However, clicking "no" yields the following results:

    Main Form "PERMIT TRACKING": Error message: "The DoMenuItem action was cancelled"
    Tab Control Pages: Error message: "no current record"

    Any ideas?
    Thanks again in advance.

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

    Re: Tab Control Page: Totals (2002)

    I suppose that you have one or more subforms (it is not clear from your description - a "tab control page" is not a subform, although it may contain a subform). Undoing changes to the main form does not undo changes to the subforms; this can lead to problems. (See ACC: Unable to Undo or Rollback Main Form and Subform Changes). It's hard to give specific advice without seeing the database.

Posting Permissions

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