Refering to a Reports underlying data using VBA in Detail OnFormat event

[RDub]

I have a complex report that needs to print a series of strings based on
dozens of Boolean Columns in the table the report is based on (Yea I know
all about data normalization, and I know this is wrong, but this is the data
the customer supplies).

The plan was to use some code in the Detail OnFormat event to determine
which strings need to be printed. However I am unable to refer to any of
the columns in the reports underlying recordset. I get a Error 2465
"ProgramName can't find the field 'afieldname' referred to in your
expression" when I try stuff like: Me!ColumnName or Me("ColumnName"). In
fact I can not even refer to the reports recordset object at all. Something
like Me.Recordset.ColumName will not even compile.

So what's up? It looks like a Reports "Me" reference can only "see"
objects that have been actually created on the report. I would hate to have
to put 4 dozen invisible check boxes on the report just to make this happen.
Aside from fixing the bad table design, does anyone have another workaround.

Ron W

 

[Allen Browne]

The problem you describe is quite, common, Ron. It seems to result from the
way Access optimizes reports. If it can't actually see a field somewhere
(directly in the Control Source of a control, or in the Sortin'n'Grouping),
it doesn't bother fetching the field.

Rather than add 40 check boxes, you might consider putting the expressions
into a query, and making the query the Record Source for a report. You can
then put text boxes on your report, and bind them directly to the query
fields.

Presumably the expression would be something like:
IIf([HasMalaria], "Malaria", Null)

 

[RDub]

Thanks Allen

I just wanted to be sure I was not missing something stupid.

Actually what I should probably do is to create a MASSIVE union query (would
have 46 UNIONs) with just the Boolean fields and bind that bad boy to a
Subreport. But frankly I am in a foul mood, and this darn thing has only
got to work for one lousy week late this month. After that it will be
scrapped.

In this case I don't think I can use your suggestion, as it would leave
holes (empty text boxes) in the report. Oh did I mention that this report
has got to fit on a piece of 4" wide by 3" high card stock.

I think I will add a bunch of check boxes, and just proceed as I had
originally planned, Iterating each field and adding the appropriate string
to a Label control (actually 2 label controls as potentially there could be
2 columns of stuff) . An Yes my world does truly suck!

Ron W

The problem you describe is quite, common, Ron. It seems to result from
the way Access optimizes reports. If it can't actually see a field
somewhere (directly in the Control Source of a control, or in the
Sortin'n'Grouping), it doesn't bother fetching the field.

Rather than add 40 check boxes, you might consider putting the expressions
into a query, and making the query the Record Source for a report. You can
then put text boxes on your report, and bind them directly to the query
fields.

Presumably the expression would be something like:
IIf([HasMalaria], "Malaria", Null)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a complex report that needs to print a series of strings based on
dozens of Boolean Columns in the table the report is based on (Yea I know
all about data normalization, and I know this is wrong, but this is the
data the customer supplies).

The plan was to use some code in the Detail OnFormat event to determine
which strings need to be printed. However I am unable to refer to any of
the columns in the reports underlying recordset. I get a Error 2465
"ProgramName can't find the field 'afieldname' referred to in your
expression" when I try stuff like: Me!ColumnName or Me("ColumnName"). In
fact I can not even refer to the reports recordset object at all.
Something like Me.Recordset.ColumName will not even compile.

So what's up? It looks like a Reports "Me" reference can only "see"
objects that have been actually created on the report. I would hate to
have to put 4 dozen invisible check boxes on the report just to make this
happen. Aside from fixing the bad table design, does anyone have another
workaround.

Ron W

 

[John Spencer]

If the report does not use a field (bound to a control, used in sorting, etc.)
then when Access builds the query to be used by the report it drops the field
from the source (behind the scenes).

So, you will need to include all those boolean fields on the report or ...

If the record has a primary key you could use a recordset to retreive the
record and step throught the record and build the strings to be printed.

Are you stuffing all the strings into one control?

Dim Dbany as DAO.Database
Dim rst as DAO.Recordset
Dim strSQL as String

strSQL = "SELECT Boolean1, Boolean2, Boolean3 FROM SomeTable Where PK = " & Me.Pk

Set DbAny = CurrentDb()
Set rst = DbAny.OpenRecordset (strSQL)

'Now process the fields and build the string

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[RDub]

John

Thanks for the suggestion, I decided to go with the 47 invisible check
boxes. It's a nasty hack, but it's all working. I am stuffing strings into
two controls actually. Have just enough room on the report to get 30
strings in two columns. Waiting on the customer to see if 30 is enough.
Might have to go with 3 columns (Label controls). Oye Veh!

Ron W