How can Conditional Formating be set up in Detail

How can Conditional Formating be set up in Detail_Format?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rmcompute - 30 Apr 2008 20:38 GMT

I was trying to use conditional formatting to change the color of a report
field based on a formula. The Conditional formatting was not working
properly so I set up a subrouteen as listed below. How can I change the
color of the AMV field based on the formula. I can't seem to reference the
format property from the subrouteen. I tried Me.AMV. which listed the
different properties, but I could not find format.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If AMV < [AMVTarget] - ([AMVTarget] * 0.3) Then

Else

End If
End Sub

Reply to this Message

Duane Hookom - 30 Apr 2008 20:53 GMT

AMV and AMVTarget must both be controls in your report. There are several
properties that don't show up automatically in Intellisense but are there.
Just try them.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.AMV < Me.[AMVTarget] - (Me.[AMVTarget] * 0.3) Then
Me.AMV.Format = "...."
Else
Me.AMV.Format = "...."
End If
End Sub

Signature

Duane Hookom
Microsoft Access MVP

> I was trying to use conditional formatting to change the color of a report
> field based on a formula. The Conditional formatting was not working
[quoted text clipped - 10 lines]
> End If
> End Sub

Reply to this Message

rmcompute - 30 Apr 2008 23:41 GMT

Duane,

I tried Me.AMV.Format = "0.00" and got the following error when I compiled it:

Compile Error: Method or data member not found. The .Format= was highlighed.

> AMV and AMVTarget must both be controls in your report. There are several
> properties that don't show up automatically in Intellisense but are there.
[quoted text clipped - 22 lines]
> > End If
> > End Sub

Reply to this Message

Duane Hookom - 01 May 2008 02:50 GMT

Are you sure AMV is the name of a text box in your report's detail section? I
just created a simple report based on the Orders table in Northwind with two
text boxes in the detail section [OrderID] and [Freight]. The following code
worked as expected:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.OrderID Mod 2 = 1 Then
Me.Freight.Format = "Currency"
Else
Me.Freight.Format = "0"
End If
End Sub

Signature

Duane Hookom
Microsoft Access MVP

> Duane,
>
[quoted text clipped - 28 lines]
> > > End If
> > > End Sub

Reply to this Message

rmcompute - 02 May 2008 00:03 GMT

I verified the field was there and got the same message. I have Access 2003;
Do you have a later version? I am wondering if the earlier version it works
this way.

> Are you sure AMV is the name of a text box in your report's detail section? I
> just created a simple report based on the Orders table in Northwind with two
[quoted text clipped - 41 lines]
> > > > End If
> > > > End Sub

Reply to this Message

Duane Hookom - 02 May 2008 02:15 GMT

You stated "verified the field was there" how about a text boxes named AMV
and AMVTarget? "Fields" are in tables, queries, and recordsets.

Signature

Duane Hookom
Microsoft Access MVP

> I verified the field was there and got the same message. I have Access 2003;
> Do you have a later version? I am wondering if the earlier version it works
[quoted text clipped - 45 lines]
> > > > > End If
> > > > > End Sub

Reply to this Message

rmcompute - 03 May 2008 14:01 GMT

Yes, that was it! The report I was changing had text boxes which referenced
fields AMV and AMVTarget, but there were no text boxes for just AMV and
AMVTarget. When I added them, I was able to compile and run the report.

Thanks for the help and best regards.

> You stated "verified the field was there" how about a text boxes named AMV
> and AMVTarget? "Fields" are in tables, queries, and recordsets.
[quoted text clipped - 48 lines]
> > > > > > End If
> > > > > > End Sub

Reply to this Message