Total in footer with VBA

[Kevin Bruce]

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

 

[Duane Hookom]

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

 

[Kevin Bruce]

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

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

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

 

[Duane Hookom]

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

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

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

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

'---------------------------------------------------------------------------'---------------------------------------------------------------------------'---------------------------------------------------------------------------'---------------------------------------------------------------------------

 

[Kevin Bruce]

Yeah, I've already got LOTS of hidden controls as it is. The trouble is that
the itemized amounts are calculated in code and therefore the sum function
in the footer doesn't work in such a case.

I did manage to get the previously invoiced items into the query for the
invoice by creating another query that holds all the data from previous
invoices. This means I can now calculate fees as UDF's. The problem I'm
experiencing now is that if it's a new invoice, there are no records in this
second query and it returns a null recordset. I'll trying using an Iif
statement to see if I can set the value to zero for those instances.

Will let you know how I make out.

Cheers.

_Kevin

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

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

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


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

'---------------------------------------------------------------------------'---------------------------------------------------------------------------'---------------------------------------------------------------------------'---------------------------------------------------------------------------

 

[Kevin Bruce]

Yeah, that did the trick. I created a second query that is referred to in
the SQL statement of the report and had to write an astonishing array of
nested Iif statements to cover all the possibilities including the null
recordset problem. This combined with controls that are made visible or
invisible depending on certain conditions it all works just fine. I'd hate
to be the programmer who someday may need to decipher what I've done, but
for now it I'll take it.

Thanks for your help, Duane.

_Kevin

Yeah, I've already got LOTS of hidden controls as it is. The trouble is that
the itemized amounts are calculated in code and therefore the sum function
in the footer doesn't work in such a case.

I did manage to get the previously invoiced items into the query for the
invoice by creating another query that holds all the data from previous
invoices. This means I can now calculate fees as UDF's. The problem I'm
experiencing now is that if it's a new invoice, there are no records in this
second query and it returns a null recordset. I'll trying using an Iif
statement to see if I can set the value to zero for those instances.

Will let you know how I make out.

Cheers.

_Kevin

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

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




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


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

'---------------------------------------------------------------------------'---------------------------------------------------------------------------

*
10

'---------------------------------------------------------------------------'---------------------------------------------------------------------------