I copied Duane's code and modified it but neglected to make one change.
Change the code to this:
Dim Db As DAO.Database
Dim Rst As DAO.Recordset
Dim intS as Integer
Dim strS as String
Set DB = CurrentDB
Set Rst = DB.OpenRecordset("QryLandSection")
intS = 0
Do Until Rst.EOF
intS = intS + 1
If Rst!Status = "In-Hand" And Rst!PCT > 0.75 Then
Me("L" & intS).BackColor = 16744576
ElseIf Rst!Status = "Leased to other" And Me.PCT > 0.75
Me("L" & intS).BackColor = 255
Else
Me("L" & intS).BackColor = 16777215
End If
Rst.Next
Loop
Rst.Close
Set Rst = Nothing
Set DB = Nothing
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]
Steve
It ran, but all the labels came out blue.
:
Let's try this ---
Make a copy of your report and let's make some changes. First, make
the
recordsource property blank. Remove all textboxes that show data. Move
your
map labels into the detail section. Make sure your labels are named L1
to
L36 and the captions are 1 to 36. Now I am going to assume you have a
table
named TblLandSection that records the data for the 36 sections and the
fields are Section, Status and Percent. I am assuming all sections are
numbered from 1 to 36. Create a query based on TblLand Section named
QryLandSection and sort ascending on Section.
Put the following code in the report's Open event:
Dim Db As DAO.Database
Dim Rst As DAO.Recordset
Dim intS as Integer
Dim strS as String
Set DB = CurrentDB
Set Rst = DB.OpenRecordset("QryLandSection")
For intS = 1 to 36
If Rst!Status = "In-Hand" And Rst!PCT > 0.75 Then
Me("L" & intS).BackColor = 16744576
ElseIf Rst!Status = "Leased to other" And Me.PCT > 0.75
Me("L" & intS).BackColor = 255
Else
Me("L" & intS).BackColor = 16777215
End If
Next
Rst.Close
Set Rst = Nothing
Set DB = Nothing
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]
Steve
Yeah, Duane was right, the code should have been for L2 in the
second
loop
(then L3 in the third, etc.) Each Label corresponds to a Section. I
have
them
laid out in a grid (it all has to do with land work.)
I suspect Duane is right, that because my data are in the detail
section
and
the "map" (labels) are in the group footer, it's not processing
correctly.
I
tried to do a print screen of my report, but can't paste it in here.
I'll try to clarify. There are 36 sections. I need each section
represented
on a map, or grid and am using color to indicate the status of that
particular section according to the criteria specified in the code.
So the map looks something like
6 5 4 3 2 1
7 8 9 10 11 12
18 17 16 15 14 13
etc
Each number would be in a label and I'm trying to color these.
Hope that helps and I really appreciate your efforts!
:
Here is the second If loop in your original post --
If Me.
= "02" And Me.Status = "In-Hand" And Me.PCT > 0.75 Then
L1.BackColor = 16744576
ElseIf Me. = "02" And Me.Status = "Leased to other" And
Me.PCT
0.75
Then
L1.BackColor = 255
Else: L2.BackColor = 16777215
End If
Note in the If and ElseIf statements you try to change the
BackColor
of
L1
but in the Else statement you try and change the BackColor of L2. I
asked
you in my post about this. Duane's code differs from your code in
that
his
code looks at the properties of Section 02 and for ALL conditions
tries
to
change the BackColor of L2.
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]
I spoke too soon. The code runs, but only the last label is
colored
in.
For
instance, in one group, there are three sections that meet the
criteria,
but
only the last is colored in. Are all labels reformatted according
to
the
last
"for" in the loop?
:
As Steve suggested, you might be able to change all your code to
something
like:
Dim intS as Integer
Dim strS as String
For intS = 1 to 36
strS = Format(intS,"00")
If Me. = strS And Me.Status = "In-Hand" And Me.PCT > 0.75
Then
Me("L" & intS).BackColor = 16744576
ElseIf Me. = strS And Me.Status = "Leased to other" And
Me.PCT
0.75
Me("L" & intS).BackColor = 255
Else
Me("L" & intS).BackColor = 16777215
End If
Next
It isn't clear what your records look like. We understand
something
doesn't
work but we don't know much more.
--
Duane Hookom
Microsoft Access MVP
:
In your second If loop, shouldn't L1 be L2?
This could all be set up in code in a Do loop rather than a
series
of
36 If
loops.
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And
Word
Applications
[email protected]
message
I have a report I would like to convert into a kind of map.
I'm
trying
to
do
this by creating labels that correspond to particular
sections.
If
acreage
in
a section meets certain criteria, the label would be colored
accordingly.
The
labels are arranged contiguously into a map.
However, it's not working. Following is a sample of the code
I'm
using for
an event procedure under the format event for the group
footer
for
each
STR
(made up of 36 sections.) But all my "maps" look the same
and
do
not
reflect
any of the data.
Here's the code:
If Me. = "01" And Me.Status = "In-Hand" And Me.PCT > 0.75
Then
L1.BackColor = 16744576
ElseIf Me. = "01" And Me.Status = "Leased to other"
And
Me.PCT
0.75
Then
L1.BackColor = 255
Else: L1.BackColor = 16777215
End If
If Me. = "02" And Me.Status = "In-Hand" And Me.PCT >
0.75
Then
