Passing a txt box value from subreport on main report (1 Viewer)

Hello1 · Jun 23, 2019

Im getting an error "You entered an expression that has an invalid reference to the property Form/Report"

Main report name: rptMainRep
Sub report name: sbrSubRep

Me.Text111.ControlSource = [Reports]![rptMainRep]![sbrSubRep].[Report]![txtFieldOnSubRep]

The txt box has Sum in it.

theDBguy · Jun 23, 2019

Hi. Where are you putting this code? Which event?

Hello1 · Jun 23, 2019

On Report_Open

Edit:
I tried also to put it directly on report design in the text boxs control source but when I go print preview I get #Error
=[Reports]![rptMainRep]![sbrSubRep].[Report]![txtFieldOnSubRep]

theDBguy · Jun 23, 2019

Hello1 said:
On Report_Open

Edit:
I tried also to put it directly on report design in the text boxs control source but when I go print preview I get #Error
=[Reports]![rptMainRep]![sbrSubRep].[Report]![txtFieldOnSubRep]

As a Control Source, try:

Code:

=[sbrSubRep].[Report]![txtFieldOnSubRep]

Hello1 · Jun 23, 2019

Again error :/
Strange because I have another report on which it works with same query and subforms but only with one record, this one has multiple. Might that be the reason?

Edit: not subforms, subreports**

Hello1 · Jun 23, 2019

I just cant figure it out.
This is my case in details. On a button click I open a report which shows sth like bills, with filters. Parameters are the Id from form to equal the Id from report query and same for date. Now when the report opens, it shows 4 pages, so 4 records, 4 bills. The report has 4 subreports which show the bill details, what was bought or how many workers did do a job n so on. The subreports are linked Link master fields: BillsId Link child fields: BillsId. The subreports show the correct data and that works just fine, but the total of the subreport data just doesnt want to show in the main report, no matter which of the above options used. What am I doing wrong

June7 · Jun 23, 2019

You are opening report in Report View? Won't work at all for PrintPreview or direct to print.

Syntax is wrong. Options:

Me.Text111.ControlSource = "=[Reports]![rptMainRep]![sbrSubRep].[Report]![txtFieldOnSubRep]"

or (assuming text type not number value)

Me.Text111.ControlSource = "='" & [Reports]![rptMainRep]![sbrSubRep].[Report]![txtFieldOnSubRep] & "'"

Should be able to put expression directly in ControlSource property and that is best approach.

txtFieldOnSubRep is actual name of textbox in subreport?

If you want to provide db for analysis, follow instructions at bottom of my post.

Hello1 · Jun 24, 2019

yes txtFieldOnSubRep is name of the textbox in the subreport, "=[Reports]![rptMainRep]![sbrSubRep].[Report]![txtFieldOnSubRep]" this one works in code, however I still get the #Error on print preview.
I went report view and yes it finally does show up there without #Error and the actual number, but only showing my page footer once at the end of the report and not on every page like in print preview.
Anyway why cant I see the number in print preview and I do see it on another report?

June7 · Jun 24, 2019

As I said, VBA cannot work for PrintPreview.

ReportView does not have pages.

I don't know what you have done on other report.

Hello1 · Jun 24, 2019

When I put it in report header it loads in print preview, but in details and page footer it doesnt, huh?

Edit: I dont know what else to do, guess its better I try to make the report without subreports?

June7 · Jun 24, 2019

I tested a textbox in report header. Tried VBA to set ControlSource - PrintPreview fails.

Can only suggest again to provide db for analysis.

Hello1 · Jun 24, 2019

Too much stuff to translate and clean there

What could I use as an alternative? Now knowing that passing a subreport control to the main report will fail to load in print preview, most probably.

Micron · Jun 24, 2019

AFAK, you cannot reference anything on or about a main report or form, from a sub report or sub form open or load event because the sub always opens first. Therefore the sub code runs before the main is even open. Research ms access order of form events or something similar.

Cronk · Jun 24, 2019

Hello1 wrote

The txt box has Sum in it.

Presumably the text box on the sub report.

Try putting the code in the OnFormat event on the section of the parent report containing the text box. This text box to be unbound with the code being

Code:

Me.Text111 = Me.[sbrSubRep].[Report]![txtFieldOnSubRep]

where sbrSubRep is the name of the sub report control on the parent report

June7 · Jun 24, 2019

I am testing this in report. Expression in textbox simply:

=[subreportcontainername]![textboxname]

But if Sum shows in subreport, why do you need to repeat it on main report?

Hello1 · Jun 24, 2019

Because I'm taking the sum of the first subreport + the sum of the second subreport n so on until 4th in that one text box on the main.
Thanks Cronk, I will try it out a little later.

Hello1 · Jun 24, 2019

Cronk said:
Hello1 wrote

Presumably the text box on the sub report.

Try putting the code in the OnFormat event on the section of the parent report containing the text box. This text box to be unbound with the code being

Code:

Me.Text111 = Me.[sbrSubRep].[Report]![txtFieldOnSubRep]

where sbrSubRep is the name of the sub report control on the parent report

It works this way when Im refering to one field in the subreport. Now im trying to do something like this:

Code:

Me.Text111 = Me.[sbrSubRep].[Report]![txtFieldOnSubRep] + Me.[sbrSubRep1].[Report]![txtFieldOnSubRep1]

It gives me an error that one of them has no value, whats true but on the next record it has a value.
I tried this:

Code:

Me.Text111 = IIf(IsNull(Me.[sbrSubRep].[Report]![txtFieldOnSubRep]), 0, Me.[sbrSubRep].[Report]![txtFieldOnSubRep]) + IIf(IsNull(Me.[sbrSubRep1].[Report]![txtFieldOnSubRep1]), 0, Me.[sbrSubRep1].[Report]![txtFieldOnSubRep1])

But I get automation error

Cronk · Jun 24, 2019

Use

Code:

Me.Text111 = nz(Me.[sbrSubRep].[Report]![txtFieldOnSubRep]) + nz(Me.[sbrSubRep1].[Report]![txtFieldOnSubRep1])

Hello1 · Jun 24, 2019

I get the same error

However, I figure it out to pass the value through the recordset i set, but then again, at the end of the report I need a Sum of all the textboxes to which I pass the value from subreports and the problem is that it only will read the text box on the current page because the OnFormat event, so probably when printing they will be empty too, except the page Im on?

June7 · Jun 24, 2019

Did you see post 16?

Passing a txt box value from subreport on main report (1 Viewer)

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

Registered User.

Registered User.

Attachments

AWF VIP

Similar threads

Users who are viewing this thread