Capture Field Caption from a table

[ScottS]

Hello all,
This question could apply to a form or a report.

I frequently do reports where I populate a table that will be form or report
data source. The fields hold data for time periods - e.g. month, week, day.

I want to dynamically change the field captions to display correctly on a
report. I already know how to update the report table field captions
dynamically. My goal is to (via VBA) set the value of a label or text box in
a form or report using the Caption property of the report table.

Is this possible?

Thanks

Scott

 

[Al Campagna]

Scott,
Give us some sample table/field values, their default label captions,
and the result you want to see on the report. We need to know what logic is
involved in the caption assignments.

You should be able to use the OnFormat event of the Section your data
falls within... to change the label captions.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[ScottS]

Al,

I have not written any code yet, but I think I can describe what I want to
do here.

I have a table with 8 fields. The first two have text descriptions. The
remaining six fields serve as monthly date-data buckets. I've named them
Mn1, Mn2 ...

I use a function to set the Caption value for Mn1, Mn2, to the period names
they represent when I run the report: e.g. Aug-07 is the caption for Mn1,
Sep-07 is the caption for Mn2..

I load the records to this table using a VBA function.

Here is where my request comes into the picture. I want to use this table
as a report source. In my report definition I have text boxes in my page
header for each date bucket. What I was hoping to do was to set the value of
these text boxes to the Capion property in the report source table above via
VBA in the report definition using a statement like

txtDate1 = Currentdb.TableDef("tblRpt").Fields("Mn1").Caption

I know the syntax may not be right but this the idea of what I'm after here
-- the ability to update the text boxes in my header with source table
caption valuest that I set base on user's criteria.

What do you think? Is this even feasible?

Thanks again for your interest and help.