How to reference a value in a continous text box?

[mongous]

I am trying to perform a calculation in a report (Access 2000), in which I need to reference the value in a text box on the corresponding sub-report. The problem is, in the sub, the box repeats five times.

I mean the sub-report in design view shows only one text box, but it is continous, so it shows five line items, with different values in each. I need a way to uniquely name the values.

 

[dhookom]

You should have a unique value in the subreport detail which can uniquely identify a row. Use this to place the value in the subreport's report footer where it can be "grabbed" by the main report.

If this doesn't help, provide some sample output so we can provide more direct assistance.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mongous]

Huh?? Ok, you lost me. Here is the output generated by the sub:
SiteID

BHM 43
CWA 52
MyGHC 30
SEA 51
SPK 45

but the report, has of course, 1 txtBox (txtDCA) and one label (lblSiteID). Does this help?

 

[dhookom]

Which value from the subreport do you want to use to perform the calculation on your main report? You have stated you want to use one but haven't provided any details on which one to use.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mongous]

Oh, sorry. I want to use the second column of data (titled DCA), in a field below in the main report.

Make sense?

So it will be something like this:
= someField/fieldDCA (on sub) in this case:

= AgentDCA/SiteDCA (x/43) to produce a %

This is probably sounding confusing....

 

[mongous]

Let me try again. The control source for my txtPct text box will read something like this:

= txtDCA/txtSiteDCA


but txtSiteDCA contains five different values - one on each row (five different sites)

 

[dhookom]

Mongous,
All I was asking for was a statement like:
"I want to pull the txtPct value from the subform where the txtSiteDCA value is 'X'".
Does this describe your need? If so, could you re-state your request with the proper values?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mongous]

OK,that's pretty much it. I want to reference the value in txtSiteDCA (on subform), to use in a calculation in the main form txtPct to come up with a final value.

I have to do this row independant. So, for one site I need to use the txtPct value in the first row, for another I need it in the second, and so on...

 

[dhookom]

If you were to look at the records, how would you be able to tell which record in the subreport you would choose. You have said first, second,... but this has no meaning and shows no specification. I was thinking that it would involve finding the value BHM but now I am totally in the dark as to which record in the subreport.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mongous]

I would involve finding values for the different sites. For instance: BHM.DCA, SEA.DCA, SPK.DCA etc...

 

[dhookom]

Apparently there is no way short of running the report, using a calculater, and manually writing the value onto the printed page. If the record to use can't be determined from the data, there is no way to reference it.

What you are asking is for someone to grab a marble out of a bag. When we grab a marble, you suggest it isn't the proper marble. When we ask, how do we know which marble to grab, you suggest it could be any one of the marbles.

Again, if you ran the report and looked at it on a printed piece of paper, how would you know which record/value from the subreport to use?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mongous]

That is exactly what I am asking. Normally I would say: txtDCA.text/txtSiteDCA.text to get my derived value. This would, however, only work for the first entry - in this case SEA (Seattle). I need to also be able to (elsewhere on the main report) use that formula but instead referencing the other sites: BHM, SPK, CWA, etc...

Are you understanding what I am asking?

 

[dhookom]

I don't understand unless you have some way to identify which value. If you want to find the value for BHM then add a text box in the subreport footer:
Name: txtThisOne
Control Source: =Sum(Abs([FieldToFind]="BHM&quot * [FieldWithNumber])
Then in the main report, you can reference this value with:
=srptYourSub.Report.txtThisOne

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mongous]

This I understand. My question is: Do I have to physically put a seperate text box for EACH returned record on the subReport?

 

[dhookom]

I would think that if you use this method, you would need to add a separate text box in the REPORT FOOTER of the subreport for each value you want to grab from the main report.

There are probably other methods that involve adding the information to the record source of the main report but I don't have enough information to provide a specific example.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mongous]

Ok, great. At least I have a path to follow...

Thanks alot!