Re: Total in footer with VBA

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Kevin Bruce (kevinb_at_telus.net)
Date: 03/09/04 

Date: Tue, 09 Mar 2004 15:45:48 GMT

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
> >
> >
>
>

Relevant Pages

  • Re: Total in footer with VBA
    ... method of calculating in user-defined functions so you could set control ... > code that produces different amounts and totals under different ... > Private Sub Detail_Format ... > 'THIS ITEMIZES EACH ITEM ON THE INVOICE AND CALCULATES A TOTAL FOR EACH ...
    (microsoft.public.access.reports)
  • Re: Newbie, Help needed with rounding errors
    ... This is why tax should only be computed on the sub-grouped total values, ... The Revenue's method of subtracting the computed price net of tax from the ... price per item on the invoice, so I round to get 21.57 each. ... incorrect totals." ...
    (microsoft.public.access.gettingstarted)
  • Re: Total in footer with VBA
    ... Running Sum on the hidden control. ... > I tried everything to get the totals into an SQL statement or query and it ... the invoice amounts can only be ... >> method of calculating in user-defined functions so you could set control ...
    (microsoft.public.access.reports)
  • Re: Formula selection: a real tough one
    ... I misread the OP saying he wanted the Invoice TOTAL, where Item SYSTM was included. ... The totals will be just for that Item. ... Drag Item No to Page area ...
    (microsoft.public.excel)
  • RE: SubTotals on Forms
    ... No. SubForm 3 draws on the invoice query and only has two items on the ... I want to have two totals at the bottom of the ...
    (microsoft.public.access.forms)