Highlight a row in a subreport based on matching value in main report

[Andrew]

I have a report which details a list of property sales, each sale row also
has a subreport which shows if there are also any previous or any later
sales.

Within the subreport I just want to highlight (change fore or backcolor) the
Sale Date and Price matching the current sale in the main report. This will
provide a quick reference that the sale the person is looking has an earlier
and a later sale.

ie Main Report

Detail Section

Sale 1 House, Sold 1/9/2003 $400,000, Colonial Style etc........
Subreport
9/1/2004 $500,000
**1/9/2003 $400,000**
5/9/2000 $200,000

Sale 2 Unit, Sold 12/8/2002 $150,000, 2BR etc ......
Subreport
18/3/2004 $250,000
11/8/2003 $200,000
**12/8/2002 $200,000**

Using the "OnFormat" event of "Detail" Section of the "Subreport" I am
trying to see if the sales dates match and if so to change the backcolour of
the Detail section. However I can't seem to get the syntax correct and the
report keeps falling over.

If Me.LastSaleDate = Reports!MainReport!LastSaleDate Then
Me.Detail.BackColor = 12514556
Else
Me.Detail.BackColor = 16777215
End If

I have tried many formats, but don't seem to be able to get this to work.

Any help would be appreciated
Andrew

 

[Marshall Barton]

I have a report which details a list of property sales, each sale row also
has a subreport which shows if there are also any previous or any later
sales.

Within the subreport I just want to highlight (change fore or backcolor) the
Sale Date and Price matching the current sale in the main report. This will
provide a quick reference that the sale the person is looking has an earlier
and a later sale.

ie Main Report

Detail Section

Sale 1 House, Sold 1/9/2003 $400,000, Colonial Style etc........
Subreport
9/1/2004 $500,000
**1/9/2003 $400,000**
5/9/2000 $200,000

Sale 2 Unit, Sold 12/8/2002 $150,000, 2BR etc ......
Subreport
18/3/2004 $250,000
11/8/2003 $200,000
**12/8/2002 $200,000**

Using the "OnFormat" event of "Detail" Section of the "Subreport" I am
trying to see if the sales dates match and if so to change the backcolour of
the Detail section. However I can't seem to get the syntax correct and the
report keeps falling over.

If Me.LastSaleDate = Reports!MainReport!LastSaleDate Then
Me.Detail.BackColor = 12514556
Else
Me.Detail.BackColor = 16777215
End If

What you've posted here looks alright to me. What about it
isn't working?

You didn't foget to set all the labels/text boxes BackStyle
to Tranparent, did you?

 

[Andrew]

Marsh, thanks for your reply. Yes I had set the backstyle to transparent.
As you indicated that I had everything ok, I went back and tried deleting
the textbox "LastSaleDate" and then created a new text box and named it
LastSaleDate2. After updating the code everything now seems to work fine.
?????.

When referencing a field in a report is it necessary to name the field
something different to its control source?

Sorry to waste your time and thank you for the reply.
Andrew

 

[Marshall Barton]

Marsh, thanks for your reply. Yes I had set the backstyle to transparent.
As you indicated that I had everything ok, I went back and tried deleting
the textbox "LastSaleDate" and then created a new text box and named it
LastSaleDate2. After updating the code everything now seems to work fine.
?????.

Double ?????
That is wierd. Who knows, maybe(?) you had an invisible
character in something and changing things around got rid of
it????

When referencing a field in a report is it necessary to name the field
something different to its control source?

It not necessary when you refer to it in code, which only
uses the field when it can't find a variable or control with
the name.

However, since a control source expression also looks to
resolve a name first as a control name, you can not refer to
a field that has the same name as a control.

My bottom line, while it is only necessary some of the time,
eliminate the confusion and always use a different name for
all controls that are referenced anywhere else. There are a
bunch of naming conventions out there, but I like to keep it
simple and use a three letter prefix on control names. E.g.
a text box bound to the field Amount would be named
txtAmount. OTOH, I don't care what name a text box's
attached label has, unless I use code to modify one of its
properties.

Sorry to waste your time and thank you for the reply.

No problem here. Besides, with such wierd happenings, a
little help can go a long ways.