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
(e-mail address removed)
okschlaps said:
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
(e-mail address removed)
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
(e-mail address removed)
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
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
S (section), status and percent are all fields in the detail
section
of
the
report. It didn't matter if I moved the map to the detail
section -
it was
still inaccurate.
Should I simply set up a function in a query to create a new
field
representing the section status? Also, there are 36 sections
and I
would
really like to simplify the code - I assume there's an easy way
with
an
array. Could you point me in the right direction on that, too.
Thanks
