How do I 'Highlight' a record in a report

[Ross]

I have a database that catalogues our products and from that we produce a
Customer Catalogue.
We have now decided to "highlight" the New products in the report to make it
easier for our customer to see what is New, for example:

Code Desc Price
12345 Widget 1 2.50
42548 Widget99 6.50 (ticked in database as New Product needs to be
highlighted)
85421 Widget21 6.40

I have tried different code, on Format or On Print change Backcolour to
Grey, but it does not work.

Any help would be appreciated.

Ross

 

[Al Campagna]

Ross,
Use the OnFormat event of the report to check for NewProduct = True, and
highlight it.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If NewProduct = True Then
SomeField.BackColor = vbYellow
Else
SomeField.BackColor = vbWhite
End If
End Sub
--
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."

 

[Allen Browne]

Another approach might be to use conditional formatting.

1. With the report open in design view, click the text box that should
change color.

2. Choose Conditional Formatting on the Format menu.

3. In the dialog, set up Condition 1 to:
Expression ([NewProduct])
and choose Yellow in the bucket.

I generally find this executes more quickly than code does.

 

[Ross]

Worked like a charm, thankyou.

Also thanks to Allen for the alternative method.

Ross

 

[John]

Al,

Good Day. I tried using your example, but could not find the OnFormat event
on my report. I clicked on the properties form which has all the events
listed for the rerpot and fields on the report. Does this apply to Office
2007? If so, where do I go?

 

[Allen Browne]

1. Open the report in design view.

2. Right-click the Detail section (the grey bar reading, Detail), and choose
Properties.

3. It's on the Event tab of the Properties sheet.

 

[John]

Al,

Thanks! I tried the code and I did not get a result. I created a "Yes/No"
column in my table called Changepri, and I added this column to my query and
report.

I entered the following code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If ChangePri = True Then
partno.BackColor = vbYellow
Else
partno.BackColor = vbWhite
End If
End Sub

When I ran the report nothing happpend. I did not get an error and the back
color did not chagne. Any suggestions?

Thanks again,

John

 

[John]

Al,

Also, I went into my table and change the properties for the "yes/no" to
"True/False" to see if this would change the report, but no change.

Thanks,

John

 

[Allen Browne]

Since this is Access 2007, there are a couple of new views for a report. The
code will not execute in Report View, nor in Layout View. It should work in
Print Preview.

You may need to add your database folder as a trusted location before any
code will work:
Office Button | Access Options | Trust Center | Trust Center Settings

 

[John]

Al,

Once I selected my trust center options it worked. Thanks again. To make
the text bold would I add the following statment:


If ChangePri = True Then
partno.BackColor = vbYellow
partno.FontWeight = vbextrabold

Thanks,

John