How can Conditional Formating be set up in Detail_Format?

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
 
D

Duane Hookom

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
 
R

rmcompute

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

Duane Hookom

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

rmcompute

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

Duane Hookom

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

rmcompute

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Detail_Format works in Print Preview but Not When Printing 0
Conditional Formating a Textbox in report... 2
Word macro that can call cells out of Excel for a find and replace 0
Field Conditional Formating 4
Conditional format -- other fields including border 4
Report Formating Code Question 3
Problem with SET & REF function 0
Conditional Formatting with VBA 4

Top