dynamic Record Source property text box

  • Thread starter Thread starter Steveo
  • Start date Start date
S

Steveo

Server = Win2000 SP4 + SQL Server 2000 SP3
Client = Win2000 SP4 + Access 2000 SP3

I have a main report with 5 columns of numbers, these have
various groupings/levels.

I would like to be able reference the last line of totals
on the main report in a sub report.

The sub report has these columns taken from a table called
tbl_Ratios:

Type: Opperator: Amount:
Population / 100000

In the sub report I would like to have 5 extra empty text
box's which are in line with the totals from the main
report.

I then tried to use the ON FORMAT event of the sub report
to dynamically set the Control Source of each of the empty
box's as/when the report is viewed.

eg.

me.txtBox1.properties("Record Source")= "=(" &
report_rpt_Main_Total01 & me.opperator & me.Amount & ")"

In English
me.txtBox1.Record Source = "=(200,000/100000)"

But I can't seem to set the record source of the text
box's.

The reason I don't want to hard code the calculations, is
that the Amount and Opperation may change and for end
users changing data in table is easier than messing with
the design of a report.
Also using the 'CAN SHRINK/GROW' properties of the sub
report, means if people add different ratios then the sub
report will just grow as the number of lines
increases/decreases.

Apologies for the waffle

Can I change the record source of the text box's at
runtime?

Or am I going about this all wrong?

Any suggestions would be fantastic as I've hit a wall and
can't think round this.....
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're going about it all wrong. Control's use ControlSource not
RecordSource.

Subreport data is filled before the main report data, so referencing
the main report controls from the subreport will probably fail. You
should refer to controls on the subreport as you do on a subform:

ControlSource in control on main report:

=Me!subreportControlName.Report!ControlName

To get the result of a calculation in which you don't know the
operator you can use the Eval() function. E.g.:

ControlSource: =Eval(Number_1 & Operator & Number_2)

So if you had 3 controls on the main report named [Number_1],
[Operator] & [Number_2] and the Value of each was:

Number_1 = 200000
Operator = "/"
Number_2 = 100000

The result of the Eval() function would be:

2 = Eval(Number_1 & Operator & Number_2)

If some of the referenced controls were on the subreport the
ControlSources of the main report's controls may look like this:

Operator ControlSource: =Me!subreportControlName.Report!Operator
Number_2 ControlSource: =Me!subreportControlName.Report!Amount

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDO8AYechKqOuFEgEQLe8wCgxUhTj8pWQpW4YUCFIZLA2hIceXUAoLWo
4mnT0Hv0TvfPmBP3T3ZmadD1
=SDL2
-----END PGP SIGNATURE-----
 
You're going about it all wrong. Control's use
ControlSource not
RecordSource

Apologies, I was referencing the control source when I was
trying to get it sorted...But it was late yesterday when I
posted, and I typed the wrong thing into my explanation.
Really, but that seems very strange. A sub report is data
based on the result of the main report, so how can it
populate before the main report as it wouldn't know what
data to fetch?
Also, I was using the On Format event not the on Open, so
the data should already be in the record set.

From what you've mentioned, is it right to say that the
ControlSource of a text box cannot be set using something
like an on open event of the report?

Also to reference forms and reports you can also use
form_FormName.ControlName OR report_ReportName.ControlName
etc..

In this example, the report totals are created in the main
report and the sub report is where the ratios are
calculated.
This is because there may be many ratios depending on the
ratio table.
So I do need to either send the result from the main
report to the sub, or pull the data from the main into the
sub.

Many thanks for the eval() suggestion, worked perfectly on
an example.


-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're going about it all wrong. Control's use ControlSource not
RecordSource.

Subreport data is filled before the main report data, so referencing
the main report controls from the subreport will probably fail. You
should refer to controls on the subreport as you do on a subform:

ControlSource in control on main report:

=Me!subreportControlName.Report!ControlName

To get the result of a calculation in which you don't know the
operator you can use the Eval() function. E.g.:

ControlSource: =Eval(Number_1 & Operator & Number_2)

So if you had 3 controls on the main report named [Number_1],
[Operator] & [Number_2] and the Value of each was:

Number_1 = 200000
Operator = "/"
Number_2 = 100000

The result of the Eval() function would be:

2 = Eval(Number_1 & Operator & Number_2)

If some of the referenced controls were on the subreport the
ControlSources of the main report's controls may look like this:

Operator ControlSource: =Me!subreportControlName.Report! Operator
Number_2 ControlSource: =Me!subreportControlName.Report! Amount

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDO8AYechKqOuFEgEQLe8wCgxUhTj8pWQpW4YUCFIZLA2hIceX UAoLWo
4mnT0Hv0TvfPmBP3T3ZmadD1
=SDL2
-----END PGP SIGNATURE-----

Server = Win2000 SP4 + SQL Server 2000 SP3
Client = Win2000 SP4 + Access 2000 SP3

I have a main report with 5 columns of numbers, these have
various groupings/levels.

I would like to be able reference the last line of totals
on the main report in a sub report.

The sub report has these columns taken from a table called
tbl_Ratios:

Type: Opperator: Amount:
Population / 100000

In the sub report I would like to have 5 extra empty text
box's which are in line with the totals from the main
report.

I then tried to use the ON FORMAT event of the sub report
to dynamically set the Control Source of each of the empty
box's as/when the report is viewed.

eg.

me.txtBox1.properties("Record Source")= "=(" &
report_rpt_Main_Total01 & me.opperator & me.Amount & ")"

In English
me.txtBox1.Record Source = "=(200,000/100000)"

But I can't seem to set the record source of the text
box's.

The reason I don't want to hard code the calculations, is
that the Amount and Opperation may change and for end
users changing data in table is easier than messing with
the design of a report.
Also using the 'CAN SHRINK/GROW' properties of the sub
report, means if people add different ratios then the sub
report will just grow as the number of lines
increases/decreases.
Click to expand...
Can I change the record source of the text box's at
runtime?

Or am I going about this all wrong?
Click to expand...

.
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can NOT fill control values in the report's OnOpen event because
the report hasn't retrieved any data yet. The exception is the
report's subreport MAY have retrieved it's data that the main report
can read. Not sure about that, you'll have to experiment.

To refer to main report controls from the subreport you can use:

ControlSource: =Parent.MainReportControlName

If the subreports are datasheet reports you can have a control in the
Report Footer that is an expression (calculated value) that can be
referenced by the main report. E.g.:

Subreport Footer control:

ControlSource: =Sum(Amount) / Count(Amount) * 25

Main report control:

ControlSource: =SubreportControlName.Report!ControlName

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDUJ4oechKqOuFEgEQIg6gCcD0RnJNXRDY067DDUN0Elh5PjKP8AoNyI
OHmr7v+BPVgEY0u6Qp+v9MUc
=lzOB
-----END PGP SIGNATURE-----
 
Back
Top