How can Conditional Formating be set up in Detail_Format?

  • Thread starter Thread starter rmcompute
  • Start date Start date
R

rmcompute

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
 
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
 
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.

Duane Hookom said:
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
--
Duane Hookom
Microsoft Access MVP


rmcompute said:
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
Click to expand...
Click to expand...
 
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
--
Duane Hookom
Microsoft Access MVP


rmcompute said:
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.

Duane Hookom said:
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
--
Duane Hookom
Microsoft Access MVP


rmcompute said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.

Duane Hookom said:
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
--
Duane Hookom
Microsoft Access MVP


rmcompute said:
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.

Duane Hookom said:
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
--
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
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
Click to expand...
Click to expand...
Click to expand...
 
You stated "verified the field was there" how about a text boxes named AMV
and AMVTarget? "Fields" are in tables, queries, and recordsets.

--
Duane Hookom
Microsoft Access MVP


rmcompute said:
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.

Duane Hookom said:
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
--
Duane Hookom
Microsoft Access MVP


rmcompute said:
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.
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
--
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
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
Click to expand...
Click to expand...
Click to expand...
 
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.

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

--
Duane Hookom
Microsoft Access MVP


rmcompute said:
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.

Duane Hookom said:
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
--
Duane Hookom
Microsoft Access MVP


:

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.
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
--
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
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top