Changing Text in Page Footer

[Kobe1]

Hi Everyone,

I have designed an invoice in Access that records the amount of hours an employee works * their hourly rate. e.g
Hours Rate Total
Alan Martin 38 £10 £380

The problem I am having is in the page footer. If the invoice only requires one page then I want the text to say 'Total Amount'
However, if the invoice spans multiple pages then I want the text in the page footer to say 'Sub Total' until it gets to the final page where it says 'Total Amount'.

I have tried to use an If Statement based on page number, but with limited success. The code I used was:

IIF([Page]>1,Total Amount,Sub Total).

This has flaws as it only works properly if the invoice is 2 pages - if the invoice is only one page long it prints 'Sub Total'.

Can anyone please help me with this problem?

Thanks in advance

Kobe1

 

[aflat]

Kobe1,

That is what the Report Footer is for. It can total the amounts in any fields in Detail sections or Group Header/Footer sections.

In one of your Group Footers place the "Sub-Total", in the Report Footer place the "Total Amount". The cool part of this is you can have any number of employees with sub-totals and still get an accurate Total Amount!

Lots of examples in Access help files.

aflat

 

[Kobe1]

Hi aflat,

I need the 'sub total' or 'total amount' (depending on the number of pages on the invoice)to print out at the bottom of each page and that is why I have placed my calculated controls and labels there.

If I were to put them in the group or report footer then they would print out on different locations on the report depending on the amount of information in the detail section.

If the invoice spans multiple pages then I need the invoice to show a sub total at the bottom of the page calculating the employees that can fit on that page.

However, if the invoice is only on one page then all I want at the bottom of the page is 'Total Amount'.

Thanks in advance

Kobe1

 

[CosmoKramer]

Kobe1,

Check out the following tip:

thread703-326104

 

[Kobe1]

CosmoKramer

This is not working for me. I have calculated the total for the invoice, but what i need is the actual text beside the calculated field to change.

If the invoice is a page long i want the text beside the total (situauted at the bootom of the page)to say "Total Amount"

If the invoice is two pages i would like the text beside the total on that page that first page to say "Sub Total" and then on the second page the text should say "Total Amount"

Is this possible?

Thanks
Kobe1

 

[CosmoKramer]

Sure, I assume you are using the Page and Pages properties to display the page numbers in your page footer.

In the On Format event of your page footer place code like this to assign the text "Total Amount" to a text box when it is the last page of the report, and "Sub Total" when it is not:

If Me.Page = Me.Pages Then
   Me.txtTotalLabel = "Total amount"
Else
   Me.txtTotalLabel = "Sub Total"
End If

Let me know if this helps or not.....

 

[Kobe1]

I have put the code in but i keep getting a compile error: Method or data member not found!

I have just started VB programming so I am wondering if I am doing everything right.

This is the code I have put into the code builder in the On Format event of my page footer.

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
If Me.Page = Me.Pages Then
Me.txtTotalLabel = "Total Amount"
Else
Me.txtTotalLabel = "Sub Total"
End If
End Sub


When I get the error message the txtTotalLabel is highlighted.

I placed an unbound text box in the page footer section of my invoice and named it 'TotalLabel' but that did not work. I also did the same with a Label.

What am I doing wrong?

Thanks in advance

Kobe1

 

[Kobe1]

CosmoKramer

I have cracked it! Thank you so much for your help. I have been trying to solve that problem for a long time.

The problem I was having was that I was naming the text field 'TotalLabel' instead of 'txtTotalLabel'

Can I bother you with another problem?

I want to export data from an Access Query into an Excel template that I have created. Exporting the data does not put it in the spreadsheet where I want it.

I have done some research on this and found some code (below) in one of the forums but i am unsure of where to place it.

Dim xlobj As Excel.Application
Set xlobj=CreateObject("excel.application&quot
With xlobj

.Workbooks.Open Filename:="C:\My Documents\profile.xls"
.Range("C12".Select
.ActiveCell.FormulaR1C1 = "what you want in this cell"
.Range("C14&quot.Select
.ActiveCell.FormulaR1C1 = "what you want in this cell"
'and so on
.Quit
End With

Thanks again
Kobe1

 

[CosmoKramer]

Kobe1,

I'm glad I could help you with this report problem, but I'm afraid I can't help with this Excel export issue.

To get a better response you should open this as a new post, probably in the "Access Other Topics" forum...