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

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
"John Spencer" <spencer@xxxxxxxxx> wrote in message
news:eIZlcY%23KKHA.4376@xxxxxxxxxxxxxxxxxxxxxxx
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 wrote:
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


.

Relevant Pages

  • Re: [Fwd: Re: [PATCH 4/5]PCI: x86 MMCONFIG: introduce pcibios_fix_bus_scan()]
    ... >> Introduces the x86 arch-specific routine that will determine whether ... these strings defined and other strings embedded in the routine body. ... remove the string that advises posting a report. ...
    (Linux-Kernel)
  • Re: Stopping report
    ... I can send a string through OpenArgs ... these two values to do some calculations in the report. ... unbound controls in another unbound control on the forms and pass it to the ... > In its Click event you need to build two strings: ...
    (microsoft.public.access.forms)
  • RE: Generate a field value, then extract from it...
    ... InStr function: ... Then on your report, you can concatenate the strings to print them: ... > There are 10 check boxes to select pizza toppings. ...
    (microsoft.public.access.forms)
  • Re: Passing info to a report from a form
    ... I actually want to pass six different strings to be displayed in my report. ... Do you know if I can pass an array into OpenArgs? ... Dim Var As Variant ...
    (microsoft.public.access.reports)
  • Re: "Find this, insert page break": what approach?
    ... Use InStr to return the position of the text strings, ... >> of each new record in the report and inserts a page break, ... >> The information at each label may or may not change, ... >> remain fixed at their character positions in each line at each report ...
    (microsoft.public.word.vba.general)
Quantcast