Dynamically changing captions

[ryetee]

Not sure what you're driving at here. If I convert to a text box and use a some function will the same not happen when OutputTo re-opens the report resulting in a loss of changes again?

If the report gets reopened then surely the on open event still happens and I've moved my code to there but it still doesn't work.

 

[vbaInet]

The function gets the value from a variable.

 

[ryetee]

The function gets the value from a variable.

OK think I understand.
I have tried this with 1 label (which is still called label140).
I have made it textbox which in the first instance is unbound.
I have then changed the control source to
=fLoadReportforXLS("Monthly XLS Report","Label140","GB")
The function is
fLoadReportforXLS(RepAny As String, RepTxt As String, fCountry As String) As String

and once the appropriate row from the recordset query has been found i set

fLoadReportforXLS = rs.Fields(9) (this is the field in the record set I want)

and then I exit the function.

This has no effect on the header row in excel other than to add another column in with the (unchanged) text box name of label140.

Right just discovered that the column headings come from the (other) name of the data control and not the report heading. Getting late but something else to play with tomorrow.

 

[vbaInet]

fLoadReportforXLS = rs.Fields(9) (this is the field in the record set I want)

Think out of the box ryetee A function doesn't store values, it needs to get its value from a variable. Pass the value of the recordset field to a variable and read off that variable using the function:

' The global variable assignment
Global_Variable = rs.Fields(x)


' The function
Function fLoadReportforXLS(...)
   fLoadReportforXLS = Global_Variable
End Function

 

[ryetee]

Think out of the box ryetee A function doesn't store values, it needs to get its value from a variable. Pass the value of the recordset field to a variable and read off that variable using the function:

' The global variable assignment
Global_Variable = rs.Fields(x)


' The function
Function fLoadReportforXLS(...)
   fLoadReportforXLS = Global_Variable
End Function

Unless I'm missing the point this has made no difference to what I had in previous post.

I have a report which have labels as the headings. The label caption is the same as the name of the data control. I changed one label to a text box and called the function to return the caption. This doesn't work. All it does is it gives me an extra column in the spreadsheet. The remaining columns take the heading from the data control and NOT the label. The only way around this is, as I see, to change the data control. I wanted one process that fits all and this would go against that but maybe that;s what I have to do.

If I actually produce a report using the same logic/process as for all the other reports it works. But ifI then try and export it to excel using the access ribbon it again picks up headings from the data controls and not the labels.

Obviously need a complete rethink for excel.

 

[vbaInet]

Right, I missed that you're talking about column headings in Excel. You will need to manipulate the Excel file after exporting or alias the field names in the Record Source before exporting. The export to Excel uses the field names as you've recognised.

 

[ryetee]

Right for the 'excel' reports that I have to do it slightly differently
1. open report in design mode
2. call function that updates textboxes with the caption from the pre populated tables (had to change that function to include text boxes)
3. output the report to excel
4.close report (with option to either save or not so that user doesn't get a do you want to save this report message)
Jobs a good 'un!

 

[ryetee]

There's always one more problem!!
SO I have set up 3 tables which are used to change the language headings on forms and within excel spreadsheet. Everything works in English. For my second language, Polish, everything works if I change the text to say PL+rest of text. As soon as I change the text to actual polish headings I get and error whilst trying to outputto the spread sheet.
I get error 2501 the outputto action was cancelled.
I'm guessing one or more of the special characters in polish are causing this to happen.
Any ideas of a way round it?

 

[vbaInet]

Perhaps the problem is the method you're using. Allow me to re-direct you to this post:

http://www.access-programmers.co.uk/forums/showpost.php?p=1428257&postcount=17

Try that.

 

[ryetee]

Perhaps the problem is the method you're using. Allow me to re-direct you to this post:

http://www.access-programmers.co.uk/forums/showpost.php?p=1428257&postcount=17

Try that.

Lol!! I had considered doing that but then docmd.transferspreadsheet uses a query/table rather than a report doesn't. That said my report is based on a query but I would effectively be back at square 1 working out how to change query headings and who's to say it wouldn't fall over if you pass some weird special character over to it!

I may have to find out!

Annoying thing is I know my way works if no special characters are used and surely access should be able to cope!?

 

[vbaInet]

Going back to square one would be your fault. I had already explained what you needed to do to get your exports to Excel as expected. You could also export to Excel using a Recordset using CopyFromRecordset but all the formatting would need to be done in Excel.

OutputTo is mainly for RTF and PDFs.

 

[ryetee]

I think I more or less followed most of your suggestions in some form or another and basically it all works except when I actually want to use Polish words with non standard characters.

The only thing I really ignored was "You will need to manipulate the Excel file after exporting or alias the field names in the Record Source before exporting."

Reason for that is because I don't know how to edit/manipulate (from VBA) an excel file after exporting and neither aliasing field names!

Looks like I'll have to find out.

 

[vbaInet]

That's what we're here for, if there's something you don't know how to do, you ask us.

Here's a link on copying to Excel and formatting:

http://btabdevelopment.com/?s=excel

The only downside with the other method, i.e. aliasing, is the Control Sources will also need to changed to reflect the alias. So perhaps not worth looking into.