Make unbound text box equal a variable 1

[y2k1981]

Dear God this is doing my head in !! please, can somebody help me. I have a function contained in a module which will be run on several reports (onPagefooter Format event). However, I can't seem to get access to put the value of a global variable into an unbound text box at the bottom of the report(s). The textbox exists in each report, and I need to tell it to make the textbox (textBox1) display the value of the variable (say var1). I've tried everything I can think of, but nothing seems to work. Any time I try anything with the Me keyword, it says invalid use of Me keyword. I tried saying textBox1 = var1, but of course that just creates another variable called textBox1 and copies the value over from var1. please, can somebody help?

 

[KenReay]

Hi

The function is in a module, not local to the report right?

so ME is only available local to the form or report.

so if you call teh function so:

x = MyFunction(Me)

Public Function MyFunction(rpt as Report) As Boolean
rpt!TheTextBoxGivingYouTheProblem = TheGlobalVariable
MyFunction = True
End Function

But I wonder why you do not make teh function return the value you want to store in the text box and so put code like so in teh onformat event of the report footer

txtBox = MyFunction()

?? maybe I misundertsnd your question

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[y2k1981]

Thanks for your response Ken,

Yes, you're correct, the function is in a module and is not local to the report. However, after that I'm a little confused!! You have two MyFunction() functions, or are they the same function? I don't quiet understand that!! I have already specified that the function be called in the Report Footer Format event

>> But I wonder why you do not make teh function return the value you want to store in the text box and so put code like so in teh onformat event of the report footer

I'm not 100% sure what you're saying here. Are you saying why don't I just put it in the report module instead of in a seperate module? The reason is because I actually have about 130 reports, so I'd have to do it for each one. I'm trying to write the code just once and then call it for each report, rather than writing it 130 times.

would you mind clarifying your function a little, thanks!!

 

[KenReay]

Hi

Maybe you simplified what you are doing to ask the question, but if all you are trying to do is set

textBox1 = var1

and textBox1 is a control in the rport footer, and var1 is global and you are doing all this in the report footer onformat event I do not see where teh function come in?

textBox1 = var1 should do it

but you said you were using a function, so I assume it is a bit more complex than that, all I am saying is that I explained how to pass the "me" to you function, and how to use it there, but this is only necessary becuase youi seemed to want to refer to textBox1 in the function, and since textBox1 is local to the report, you cannot do that without qualifying it with the report object. But if you have code like:

textBox1 = MyFunction() in the report footer on format event, the why do you need to refer to textBox1 within the function ?

Without seeing teh code in the onformat event and in your function I am guessing at what you are trying to do, hence the general explanation about teh possible ways to do it (and so hence the two versions of teh function, one of which uses textBox1 within the function and one of which returns the value to textBox1).

Has that explained?, I sometimes (always) find it is easier to do it than explain it !!

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[y2k1981]

Firstly, I'm a novice when it comes to VBA, so I hope I'm explaing everything OK. Second, yes, I did simplify the question a little because I didn't want to bore anybody with the detail. So I'll give you the long version, if you don't mind!! I'm trying to put the number of records on each page of a report, on the bottom of each page. So, a report has 130 records, 30 on 4 pages and 10 on the 5th page, I want the page footer for the first 4 to say 30 and the last page to say 10. There's an FAQ that addresses how to do this, but, it involves puttin the code into the report module. However, I have about 130 reports which all require the same thing. So, rather than do that 130 times, I thought it made more sense to write the code just once and then call it for each report. As far as I can remember, it's FAQ703-3069 (but I'm not 100% sure on that).

Anyway, here's what I have:

Function Report_Open(Cancel As Integer)
intPageCount = 0


End Function
--------
Function Detail_Print(Cancel As Integer, PrintCount As Integer)
intPageCount = intPageCount + 1

End Function
--------
Function PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)


txtPageCount = intPageCount
intPageCount = 0
End Function
----------

The report open function is called in the report open event, the pagefootersection is called in the page footer format event and the detail print is called in the detail print event. But I can't get access to recognise txtPageCount as a text box in the current report, it just treates it as a new variable and assigns it the same value as intPageCount

Hope I've explained this ok? And thanks for your time and help

 

[y2k1981]

sorry, should have mentioned that the variable is also declared outside of the functions

 

[KenReay]

Hi

OK I have Read the FAQ, but I am afarid I do not follow your explanation of what you have actually done, you seem to have made functions which mimic the event names (Subs) within the report, but where do you call these functions?

In the Function "Function PageFooterSection_Format" you have done exactly what I said, ie you have refered to txtpageCount outside of the rport and txtPageCount is local to the report.

Do you understand the idea of 'scope' of variables ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[y2k1981]

Hi Ken,

Sorry, the functions are called in the same places as if they were in the report module, so the Report_Detail function is called in the onPrint event of the detail section. The pageFooterSectionFormat function is called in the onFormat section of the PageFooter and the Report_Open function is called when the report is opened. Basicly, I guess what I need to do is combine my PageFooterSectionFormat function with your function above which uses the Me keyword?

To answer your question, no, I can't say I understand the scope of variables but I'm guessing it has something to do with where you can use a variable or something like that?

Thanks again for your help

 

[y2k1981]

Me again !!

Well, after re-reading your response over and over, as well as doing some searching on google, I think I understand what you're saying. So, I've simplified my procedures a little as follows:

function Report_Open()
intPageCont = 0
End function
-----------
The above function is called when the report is opened
-----------
function Detail_Print() As Integer [COLOR=#009900]' I'm learning !![/color]
intPageCount = intPageCount +1

End function
-----------
The above function is called in the Detail Print event
-----------
In my report, I've set the field's control source to ="Number of records on this page: " & Detail_Print()

And finally, I have something in the box (yippee !!), excpet that it always says 0. I can't understand why. I thought first perhaps because the intPageCount was set to zero in one funciton and was being incremented in another that it was being treated as two seperate variables, but I've confirmed that that's not the case by using as MsgBox. So then I thought maybe it's being written to the text box too early, so I set the Report_Open function to set the value to 15 instead of 0, but it still showing 0 on the report. Why is that? Can you help me out one last time?

 

[y2k1981]

sorry, of course I just realized that when I was using the MsgBox, I was showing the intPageCount variable, when actually the "variable" that's showing in the page footer is Detail_Print(). So, my problem is that Detail_Print is returning zero, is that correct?

 

[KenReay]

Hi

As you show it:

Function Detail_Print(Cancel As Integer, PrintCount As Integer)
intPageCount = intPageCount + 1

End Function
does not return a value, you would need (say):

Function Detail_Print(Cancel As Integer, PrintCount As Integer)
intPageCount = intPageCount + 1
Detail_Print = intPageCount
End Function


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[y2k1981]

Thanks Ken, you've definitely earned that star !! However, I still have a problem. Detail_Print is returning one more a value one higher than what it should be. it's returning 32 on a page with 31 records and 11 on a page with 10 records. I can't understand why.

Also, What's the signifigance of the Cancel As Integer and PrintCount As Integer (where does PrintCount come from?)

My function was Detail_Print() As Integer and yours was Detail_Print(Cancel As Integer, PrintCount As Integer) - they're both returning the exact same results.

Thanks again for your help.

 

[KenReay]

Hi

It is not 'my' function I jsut copied and pasted it from one of yor earlier posts!

I am guessing a little here, but..

Cancel As Integer I would suspect in teh original sub would allow you to cancel printing of a detail line - for example if you tested in code some condition and decided that meant you wanted to suppress printing of that row, Cancel = True and it is gone

PrintCount As Integer - When producing reports, Access may make several 'passes' - if you look at some of the things you can do like for example have the totals before the detail, you can see that it would need to do this. So you will find in several events (like OnFormat) tehre are varaibles to tell you how many times routine has been executed. This often causes problems if you try to accumulated counters etc in such events.

I do not have an explanation as to why your value is one out, except perhaps it is to do with the timing of when it is updated?



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK