Show data from an unbound form textbox in a report page footer 1

[Epsilon101]

Hi,

I have a form used to create a filter for a report, what i want to try and do is to display the information from one of the unbound textboxes, in my report.

Below is an example of my thoughts on doing this.
IsLoaded is a function in one of my modules that checks if txtbox is empty, null and lots more.

What i have found is that .controlsource isnt a selectable option in vba but its there when i look at the text boxes properties in the report.

TxtIntroducers is the txt box in the report
TxtIntroID is the text box on the form

I use the report normally and with a filter, so if its filtered i need it to show which have been filtered, if not then just say ALL.

Private Sub Report_Open(Cancel As Integer)
If IsLoaded("FrmKPIFilter") Then
    Me.TxtIntroducers.controlsource = Form!FrmReportFilter!TxtIntroCriteria
Else
Me.TxtIntroducers = "All Introducers"

End Sub

Any help would be great


---------------------------------------

Neil

 

[KenReay]

If IsLoaded("FrmKPIFilter") Then
Me.TxtIntroducers.controlsource = Forms!FrmReportFilter!TxtIntroCriteria
Else
Me.TxtIntroducers = "All Introducers"


Regards

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

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

UK

 

[KenReay]

sorry too quick on the send button

If IsLoaded("FrmKPIFilter") Then
Me.TxtIntroducers.controlsource = Form!FrmKPIFilter!TxtIntroCriteria
Else
Me.TxtIntroducers = "All Introducers"
End If

this assuming the control is on the form FrmKPIFilter


Regards

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

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

UK

 

[KenReay]

sorry too quick on the send button, third time lucky!!

If IsLoaded("FrmKPIFilter") Then
Me.TxtIntroducers.controlsource = Forms!FrmKPIFilter!TxtIntroCriteria
Else
Me.TxtIntroducers = "All Introducers"
End If

this assuming the control is on the form FrmKPIFil

Regards

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

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

UK

 

[Epsilon101]

Lol thanks Ken,

Ill get back to you in about an hour or so on it, let you know how it goes.

---------------------------------------

Neil

 

[Epsilon101]

Hi Ken,

It is giving an error of, cant assign a value to this object

Me.TxtIntroducers = "All Introducers"



---------------------------------------

Neil

 

[KenReay]

Hi

OK, I did not notice that you had it in the on open event of the report

move the code to the onformat event of the section in which the control Me.TxtIntroducers is, Page Heading section onform event at a guess

Regards

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

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

UK

 

[Epsilon101]

Ok this is what ive got now, it works when i just open the report as it doesnt try to change the control source, just tried it with the form open, using this code and it gives error, "You can't set the Control Source property in print preview or after printing has started".

This report is to open in print preview as i have made a custom shortcut menu bar for them to use to only be able to print it, so im guessing ive gone and put it in the wrong section of code again.

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)

If IsLoaded("FrmKPIFilter") Then
    Me.TxtIntroducers.ControlSource = Forms!FrmKPIFilter!TxtIntroCriteria
Else
Me.TxtIntroducers = "All Introducers"
End If

If IsLoaded("FrmKPIFilter") Then
    Me.TxtFromDateRep.ControlSource = Forms!FrmKPIFilter!TxtFromDate
Else
Me.TxtFromDateRep = ""
End If

If IsLoaded("FrmKPIFilter") Then
    Me.TxtToDateRep.ControlSource = Forms!FrmKPIFilter!TxtToDate
Else
Me.TxtFromDateRep = ""
End If

End Sub

---------------------------------------

Neil

 

[Epsilon101]

Ah got rid of the .controlsource bit and it works.

Silly thing really

---------------------------------------

Neil

 

[Epsilon101]

Thanks for your help, i will give you a star once the site is running properly again, since it isnt letting me do it at the moment

---------------------------------------

Neil

 

[KenReay]

Hi

Yes, .controlsource was no where in the code posted

.control source is used to bind a control to an underlying query of table column (of the report, or form)

Regards

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

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

UK

 

[Epsilon101]

Sorry im lost now,

You sent this one

If IsLoaded("FrmKPIFilter") Then
Me.TxtIntroducers.controlsource = Forms!FrmKPIFilter!TxtIntroCriteria
Else
Me.TxtIntroducers = "All Introducers"
End If

So it binds to the query or table column, other than that you cant add it via control source when adding it to an unbound text box found in the report??

Is that close to what you mean?? Just need a little help with the explanation.


---------------------------------------

Neil

 

[KenReay]

Hi

Yes, you are quite right, I copied your code and amended it, did not notice the .controlsource, I was answering your post while listening (half) to a (boring) user who I ahve explained the same thing to about fifty times, sorry....

Regards

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

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

UK

 

[Epsilon101]

Just wanted to make sure i wasnt getting the wrong end of the stick, main reason for my confusion was that vba's help system that gives a drop down selection when you hit the . is very helpful and displays what you can use, it didnt have .controlsource in there, but the boxes property had it, you have explained why it doesnt have it alrdy, just wanted to make it clear to myself that if vba help doesnt display it as an option you cant use it.

Hope that makes some sense, man im crap at explaining things.

Thanks

---------------------------------------

Neil

 

[KenReay]

Hi

I think we have got too hung up on this. It was my failure to pay full attention which caused me to copy the code containing the .controlsource property. It was simply not needed.

From Access Help:

For reports, the ControlSource property applies only to report group levels

ControlSource Property Example

The following example sets the ControlSource property for a text box named AddressPart to a field named City:

Forms!Customers!AddressPart.ControlSource = "City"
The next example sets the ControlSource property for a text box named Expected to the expression =Date() + 7.

Me!Expected.ControlSource = "=Date() + 7"


Thanks for the start by the way

Regards

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

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

UK