Conditional Formatting Using Macros Instead of VB Scripts

[demzre]

For those who are unfamiliar with VB scripts (like me), you can change the format of an item (fields, objects, etc.) in a report based on a prespecified condition using a macro.

EXAMPLE
Lets say you have a picture in your report called [Logo] and you only want to it to be visible when the field [Employee] is equal to "yes". This is what you do:

1) Create a new macro. I'll name it Macro1 in this example.
2) Make sure Macro1 features the Condition column. If not, click the View menu then Conditions.
3) In the first row, type "[Employee]="yes"" in the Condition field and choose "Set Value" in the Action field.
4) In the Action Argument section below, type [Logo].Visible in the Item field and "True" in the Expression field.
5) OPTIONAL: In the second row, type &quot;[Employee]<>&quot;yes&quot;&quot; in the Condition field and choose &quot;Set Value&quot; in the Action field. In the Action Argument section below, type [Logo].Visible in the Item field and &quot;False&quot; in the Expression field.
6) Save Macro1.
7) Go to related report.
8) In the Properties of the Detail section, choose Macro1 within the Event tab's &quot;On Format&quot; field.
9) Save updated report.

Pretty simple, huh? Obviously, visibility of an item is not the only thing you can change. Play around with this and I'm sure you'll figure out some other cool ways to snaze up your report.

BACKGROUND
I decided to post up this tip after spending 2 days searching this forum on how to create advanced conditional formatting on reports like the example above. All the solutions I found were VB scripts and they were hard decipher and use if you are new to this. I have to admit my Access skills are just average at best. Luckily, I found a tip in an old Access 95 book on how to use reports in macros and it took less than 3 minutes to figure out how to do what I want it to do.

Hope this helps.

 

[dhookom]

Code seems simpler to me especially since intellisense will offer to complete much of your code expressions. You can
1) click the [...] to the right of the On Format event of the section
2) Choose Code Builder
3) add the single red code below:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
[red]Me.Logo.Visible = Me.Employee = True[/red]
End Sub
This assumes Employee is a yes/no field.
Code has much more flexibility, error handling, and I feel easier. However, if macros work for you then more power to you.



Duane
MS Access MVP
Find out how to get great answers faq219-2884.