Re: Total in footer with VBA

From: Duane Hookom (duanehookom_at_NO_SPAMhotmail.com)
Date: 03/09/04 

Date: Tue, 9 Mar 2004 12:27:46 -0600

You could try duplicate your controls and hiding one set. Then set the
Running Sum on the hidden control. If the name of the hidden control is
"txtRunSum" then you can add a text box in the footer with a control source
of:
=txtRunSum 

-- 
Duane Hookom
MS Access MVP
"Kevin Bruce" <kevinb@telus.net> wrote in message
news:MEl3c.136100$A12.91492@edtnps84...
> I tried everything to get the totals into an SQL statement or query and it
> appears impossible. Here's why:
>
> We invoice two types of clients, call them A and B.
>
> A is billed $30 for the first item and $15 per item thereafter up to a max
> of $125.
>
> B is billed $30 for the first item of a specific type, then $10 for each
> further item of that same type thereafter up to a max of $125.
>
> If either A or B orders more items, they are invoiced only $15 or $10
> respectively until the maximums are reached, within the current year.
>
> It is not practical to simply assign these dollar values to a field in
each
> record since refunding any once item is not a simply a case of giving back
> the amount assigned to it. Therefore, the invoice amounts can only be
> calculated at run time.
>
> I have the invoice working and all my calculations are calculating just
> fine. All that I want is for what I see on the screen in Preview to print
> off the printer without somehow doubling the total.
>
> Are you sure there isn't a simple solution?
>
> _Kevin
>
>
>
>
> "Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
> news:%23T$CndYBEHA.3256@TK2MSFTNGP09.phx.gbl...
> > I would do everything I could to avoid totalling in code. There should
be
> a
> > method of calculating in user-defined functions so you could set control
> > sources to:
> > =MyUDF([fielda], [fieldb])
> > and
> > =Sum(MyUDF([fielda], [fieldb]))
> >
> > -- 
> > Duane Hookom
> > MS Access MVP
> >
> >
> > "Kevin Bruce" <kevinb@telus.net> wrote in message
> > news:LMa3c.169896$Hy3.166666@edtnps89...
> > > This should be simple, I hope.
> > >
> > > I have a report with a text box in the footer of an invoice (report)
> that
> > > holds the total for all the items in the detail section. Because the
> > > individual items and the total are calculated differently depending on
> > > certain conditions, all amounts are calculated using VBA.
> > >
> > > When the invoiced is previewed on screen, the total appears as is
should
> > be.
> > > When it prints, however, it prints exactly double the true total or
> twice
> > > what it appears as while in Preview.
> > >
> > > My code is below. I have added extra notes in CAPS, and have omitted
the
> > > code that produces different amounts and totals under different
> > conditions.
> > >
> > > Thanks in advance for any advice.
> > >
> > > _Kevin
> > >
> > >
> > >
> >
>
'---------------------------------------------------------------------------
> > > --------------------
> > > Option Compare Database
> > > Dim curInvoiceTotal As Currency
> > > Dim curFeeThisArtistOwing As Currency
> > > Dim curFeeThisArtistPaid As Currency
> > >
> > >
> >
>
'---------------------------------------------------------------------------
> > > -----------------------
> > >
> > > Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
> > >
> > > 'calculate District Booking Fees
> > >
> > > 'THIS ITEMIZES EACH ITEM ON THE INVOICE AND CALCULATES A TOTAL FOR
EACH
> > > 'IT WORKS JUST FINE
> > >
> > > If txtEventsAlreadyInvoiced = 0 Then
> > >         curFeeThisArtistOwing = 30 + (txtNoOfEvents - 1) * 10
> > >             If curFeeThisArtistOwing > 125 Then
> > >                 curFeeThisArtistOwing = 125
> > >             End If
> > > Else
> > >         curFeeThisArtistPaid = 30 + (txtEventsAlreadyInvoiced - 1) *
10
> > >             If curFeeThisArtistPaid > 125 Then
> > >                 curFeeThisArtistPaid = 125
> > >             End If
> > >         curFeeThisArtistOwing = txtNoOfEvents * 10
> > >             If curFeeThisArtistOwing + curFeeThisArtistPaid > 125 Then
> > >                 curFeeThisArtistOwing = 125 - curFeeThisArtistPaid
> > >             End If
> > > End If
> > >
> > > 'THIS ASSIGNS THE VALUE CALCULATED ABOVE TO THE TEXT BOX IN THE DETAIL
> > > 'SECTION ON THE INVOICE. NO PROBLEMS HERE.
> > >
> > >    txtSDBookingFeeThisArtist = curFeeThisArtistOwing
> > >
> >
>
'---------------------------------------------------------------------------
> > > --------------------------
> > > Private Sub Detail_Print(Cancel As Integer, FormatCount As Integer)
> > >
> > > 'add fee owing per artist to the invoice total
> > > 'THIS IS MY ATTEMPT TO SUM THE FEES CALCULATED IN THE DETAIL FORMAT
> > 'SECTION
> > > ABOVE
> > >
> > > curInvoiceTotal = curInvoiceTotal + curFeeThisArtistOwing
> > >
> > > End Sub
> > >
> >
>
'---------------------------------------------------------------------------
> > > --------------------------
> > >
> > > Private Sub GroupFooter0_Print(Cancel As Integer, PrintCount As
Integer)
> > >
> > > 'THIS ASSIGNS THE TOTAL TO THE VALUE OF THE TEXT BOX ON THE INVOICE.
> > > 'IN PRINT PREVIEW, THE TOTAL IS EXACTLY WHAT IT SHOULD BE
> > > 'WHEN IT PRINTS, THE TOTAL IS EXACTLY DOUBLE WHAT IT IS IN PREVIEW
> > >
> > > txtInvoiceTotal = curInvoiceTotal
> > >
> > >
> >
> >
>
>