Referencing an "off-page" field in a record

Hello!

I have a working report that I am enhancing to highlight
any changes in the data, when compared to a previously
captured "snapshot" of the data. I already have the code
in place (elsewhere in the application) to create
the "snapshot" in a separate table, and I have created a
query that combines the current data, along with
the "snapshot" data, into a single record source for the
report.

So, each resulting record from the query would contain the
following fields:

EmployeeNum
LastName
SNAP_LastName
FirstName
SNAP_FirstName
HomePhone
SNAP_HomePhone
etc.

The Detail section of the report aready contains text box
controls which are bound to the "non-SNAP" fields.

I want to use the Detail's OnFormat event to run through a
series of comparisons between the "non-SNAP" fields and
the corresponding "SNAP" fields, and wherever the pairings
are different, I will change the BackColor property of the
text box to vbYellow (otherwise, I'll set it to the
normall vbWhite).

I have already proven that the concept will work, by
placing another text box (Visible=False) on the report for
the SNAP_LastName field. But, what I'm trying to avoid,
is having to clutter up the Detail section of the report
with a whole series of duplicated non-visible text boxes,
just to be able to gain access to the data in all of
the "SNAP" fields of the record.

Is there a way to directly reference fields in the current
record, when they do not have any associated control
defined on the report? I fooled around a bit with "Me"
and "CurrentRecord" in the VBA code ... but with no luck.
I'm hoping that I just need to tweak the syntax a little
bit to have success.

Any help/ideas will be greatly appreciated.

Brant

It is often convenient to have invisible controls on forms and reports. I
size them down to a fraction of their original size, set the Visible
property to No, and set the background color to something that will clearly
show up in design view, like magenta or dark yellow. The color is a "flag"
for me when looking at design view that the controls are not visible, but
they can still be accessed from code.

Larry Linson
Microsoft Access MVP


"Brant Burrow" wrote in message
...
Hello!

I have a working report that I am enhancing to highlight
any changes in the data, when compared to a previously
captured "snapshot" of the data. I already have the code
in place (elsewhere in the application) to create
the "snapshot" in a separate table, and I have created a
query that combines the current data, along with
the "snapshot" data, into a single record source for the
report.

So, each resulting record from the query would contain the
following fields:

EmployeeNum
LastName
SNAP_LastName
FirstName
SNAP_FirstName
HomePhone
SNAP_HomePhone
etc.

The Detail section of the report aready contains text box
controls which are bound to the "non-SNAP" fields.

I want to use the Detail's OnFormat event to run through a
series of comparisons between the "non-SNAP" fields and
the corresponding "SNAP" fields, and wherever the pairings
are different, I will change the BackColor property of the
text box to vbYellow (otherwise, I'll set it to the
normall vbWhite).

I have already proven that the concept will work, by
placing another text box (Visible=False) on the report for
the SNAP_LastName field. But, what I'm trying to avoid,
is having to clutter up the Detail section of the report
with a whole series of duplicated non-visible text boxes,
just to be able to gain access to the data in all of
the "SNAP" fields of the record.

Is there a way to directly reference fields in the current
record, when they do not have any associated control
defined on the report? I fooled around a bit with "Me"
and "CurrentRecord" in the VBA code ... but with no luck.
I'm hoping that I just need to tweak the syntax a little
bit to have success.

Any help/ideas will be greatly appreciated.

Brant

Hi Larry,

Thanks for your comments. I've often used invisible
controls on reports (for behind-the-scenes calculations,
etc.), and you're quite right, they are definitely useful.
But I've only ever used them in small quantity.

In this case, all of the *visible* controls in the Details
section are butted up against each other (with borders -
to create the same type of effect as turning on gridlines
in a spreadsheet). That is what the previous designer had
set-up, and also, what the end-user wants. And, the
height of the section is set to the same as the height of
the text boxes (for the same reason). So, placing a
duplicate set of invisible controls in the section is
really going to clutter things up, and make future
maintenance a bit of a hassle (i.e. one set of controls is
always going to be "under" the other set).

I was hoping that there was a more elegant way of
accomplishing the task (i.e. just being able to reference
fields from the current record, in VBA, without having to
use invisible controls as intermediaries). But, alas, if
that's the way it must be done, then that's what I'll do
shrug/sigh/grin.

Thanks again for your input.

Brant

PS - I'll monitor this thread for a little while, just in
case someone comes up with an alternate "trick".


-----Original Message-----
It is often convenient to have invisible controls on
forms and reports. I
size them down to a fraction of their original size, set
the Visible
property to No, and set the background color to something
that will clearly
show up in design view, like magenta or dark yellow. The
color is a "flag"
for me when looking at design view that the controls are
not visible, but
they can still be accessed from code.

Larry Linson
Microsoft Access MVP

You can include a bit of design-time code in the Report's module to move the
"invisible" controls all to the front so they show up in design view and to
the back if it troubles you to have them "on top" in design view but
invisible when the report is run.

I just sit them on top of the visible controls ("bring to front"), can see
them in design view, and they don't show up in report view. And, for
testing, if I need to, I can make them visible and nudge them to where I can
see both the ordinarily invisible and ordinarily visible values at the same
time.

A little "design-time only" code that I run from the Immediate Window has
proven handy in a good many instances over the years.

Larry Linson
Microsoft Access MVP

"Brant Burrow" wrote in message
...
Hi Larry,

Thanks for your comments. I've often used invisible
controls on reports (for behind-the-scenes calculations,
etc.), and you're quite right, they are definitely useful.
But I've only ever used them in small quantity.

In this case, all of the *visible* controls in the Details
section are butted up against each other (with borders -
to create the same type of effect as turning on gridlines
in a spreadsheet). That is what the previous designer had
set-up, and also, what the end-user wants. And, the
height of the section is set to the same as the height of
the text boxes (for the same reason). So, placing a
duplicate set of invisible controls in the section is
really going to clutter things up, and make future
maintenance a bit of a hassle (i.e. one set of controls is
always going to be "under" the other set).

I was hoping that there was a more elegant way of
accomplishing the task (i.e. just being able to reference
fields from the current record, in VBA, without having to
use invisible controls as intermediaries). But, alas, if
that's the way it must be done, then that's what I'll do
shrug/sigh/grin.

Thanks again for your input.

Brant

PS - I'll monitor this thread for a little while, just in
case someone comes up with an alternate "trick".


-----Original Message-----
It is often convenient to have invisible controls on
forms and reports. I
size them down to a fraction of their original size, set
the Visible
property to No, and set the background color to something
that will clearly
show up in design view, like magenta or dark yellow. The
color is a "flag"
for me when looking at design view that the controls are
not visible, but
they can still be accessed from code.

Larry Linson
Microsoft Access MVP

It is simple to refer to a field in the RecordSource of a Form in its
Current Property. I don't know the "combination" to refer to a Field in the
RecordSource of a Report in the Format or Print events of the Detail
Section -- the same code that works in the Form does not work in a Report,
and neither did any other that I tried.

Maybe someone will explain and I'll learn something new.

Larry Linson
Microsoft Access MVP

"Brant Burrow" wrote in message
...
Hi Larry,

Thanks for your comments. I've often used invisible
controls on reports (for behind-the-scenes calculations,
etc.), and you're quite right, they are definitely useful.
But I've only ever used them in small quantity.

In this case, all of the *visible* controls in the Details
section are butted up against each other (with borders -
to create the same type of effect as turning on gridlines
in a spreadsheet). That is what the previous designer had
set-up, and also, what the end-user wants. And, the
height of the section is set to the same as the height of
the text boxes (for the same reason). So, placing a
duplicate set of invisible controls in the section is
really going to clutter things up, and make future
maintenance a bit of a hassle (i.e. one set of controls is
always going to be "under" the other set).

I was hoping that there was a more elegant way of
accomplishing the task (i.e. just being able to reference
fields from the current record, in VBA, without having to
use invisible controls as intermediaries). But, alas, if
that's the way it must be done, then that's what I'll do
shrug/sigh/grin.

Thanks again for your input.

Brant

PS - I'll monitor this thread for a little while, just in
case someone comes up with an alternate "trick".


-----Original Message-----
It is often convenient to have invisible controls on
forms and reports. I
size them down to a fraction of their original size, set
the Visible
property to No, and set the background color to something
that will clearly
show up in design view, like magenta or dark yellow. The
color is a "flag"
for me when looking at design view that the controls are
not visible, but
they can still be accessed from code.

Larry Linson
Microsoft Access MVP

My experience is that you must bind a control to the field in order for code
to "see" the value of the field. You might want to try create a lowest level
of sorting and grouping on th primary key field of your records and create a
group header on this field. Add your invisible controls to this section and
then set the entire section to invisible. I'm not sure if this will work but
it might...

--
Duane Hookom
MS Access MVP


"Brant Burrow" wrote in message
...
Hi Larry,

Thanks for your comments. I've often used invisible
controls on reports (for behind-the-scenes calculations,
etc.), and you're quite right, they are definitely useful.
But I've only ever used them in small quantity.

In this case, all of the *visible* controls in the Details
section are butted up against each other (with borders -
to create the same type of effect as turning on gridlines
in a spreadsheet). That is what the previous designer had
set-up, and also, what the end-user wants. And, the
height of the section is set to the same as the height of
the text boxes (for the same reason). So, placing a
duplicate set of invisible controls in the section is
really going to clutter things up, and make future
maintenance a bit of a hassle (i.e. one set of controls is
always going to be "under" the other set).

I was hoping that there was a more elegant way of
accomplishing the task (i.e. just being able to reference
fields from the current record, in VBA, without having to
use invisible controls as intermediaries). But, alas, if
that's the way it must be done, then that's what I'll do
shrug/sigh/grin.

Thanks again for your input.

Brant

PS - I'll monitor this thread for a little while, just in
case someone comes up with an alternate "trick".


-----Original Message-----
It is often convenient to have invisible controls on
forms and reports. I
size them down to a fraction of their original size, set
the Visible
property to No, and set the background color to something
that will clearly
show up in design view, like magenta or dark yellow. The
color is a "flag"
for me when looking at design view that the controls are
not visible, but
they can still be accessed from code.

Larry Linson
Microsoft Access MVP