A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

creating "map" based on field values



 
 
Thread Tools Display Modes
  #11  
Old July 18th, 2007, 01:50 AM posted to microsoft.public.access.reports
Steve[_10_]
external usenet poster
 
Posts: 608
Default creating "map" based on field values

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






"okschlaps" wrote in message
...
Steve
It ran, but all the labels came out blue.

"Steve" wrote:

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





"okschlaps" wrote in message
...
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!

"Steve" wrote:

Here is the second If loop in your original post --
If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "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






"okschlaps" wrote in message
...
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?

"Duane Hookom" wrote:

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.[S] = strS And Me.Status = "In-Hand" And Me.PCT 0.75
Then
Me("L" & intS).BackColor = 16744576
ElseIf Me.[S] = 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


"Steve" wrote:

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




"okschlaps" wrote in
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.[S] = "01" And Me.Status = "In-Hand" And Me.PCT 0.75
Then
L1.BackColor = 16744576
ElseIf Me.[S] = "01" And Me.Status = "Leased to other" And
Me.PCT

0.75
Then
L1.BackColor = 255
Else: L1.BackColor = 16777215
End If

If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT
0.75
Then
L1.BackColor = 16744576
ElseIf Me.[S] = "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












  #12  
Old July 18th, 2007, 02:06 PM posted to microsoft.public.access.reports
okschlaps
external usenet poster
 
Posts: 37
Default creating "map" based on field values

I'm getting a "Method or data member not found" error at Rst.Next. I've
copied my code in case I've typed something wrong. Thanks
Private Sub Report_Open(Cancel As Integer)
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 Rst!PCT 0.75 Then
Me("L" & intS).BackColor = 255
Else
Me("L" & intS).BackColor = 16777215
End If
Rst.Next
Loop
Rst.Close
Set Rst = Nothing
Set Db = Nothing

End Sub

"Steve" wrote:

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






"okschlaps" wrote in message
...
Steve
It ran, but all the labels came out blue.

"Steve" wrote:

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





"okschlaps" wrote in message
...
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!

"Steve" wrote:

Here is the second If loop in your original post --
If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "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






"okschlaps" wrote in message
...
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?

"Duane Hookom" wrote:

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.[S] = strS And Me.Status = "In-Hand" And Me.PCT 0.75
Then
Me("L" & intS).BackColor = 16744576
ElseIf Me.[S] = 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


"Steve" wrote:

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




"okschlaps" wrote in
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.[S] = "01" And Me.Status = "In-Hand" And Me.PCT 0.75
Then
L1.BackColor = 16744576
ElseIf Me.[S] = "01" And Me.Status = "Leased to other" And
Me.PCT

0.75
Then
L1.BackColor = 255
Else: L1.BackColor = 16777215
End If

If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT
0.75
Then
L1.BackColor = 16744576
ElseIf Me.[S] = "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













  #13  
Old July 18th, 2007, 04:02 PM posted to microsoft.public.access.reports
Steve[_10_]
external usenet poster
 
Posts: 608
Default creating "map" based on field values

Sorry, my brain was running faster than my typing. Rst.Next should be
Rst.MoveNext.

Steve



"okschlaps" wrote in message
...
I'm getting a "Method or data member not found" error at Rst.Next. I've
copied my code in case I've typed something wrong. Thanks
Private Sub Report_Open(Cancel As Integer)
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 Rst!PCT 0.75 Then
Me("L" & intS).BackColor = 255
Else
Me("L" & intS).BackColor = 16777215
End If
Rst.Next
Loop
Rst.Close
Set Rst = Nothing
Set Db = Nothing

End Sub

"Steve" wrote:

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






"okschlaps" wrote in message
...
Steve
It ran, but all the labels came out blue.

"Steve" wrote:

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





"okschlaps" wrote in message
...
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!

"Steve" wrote:

Here is the second If loop in your original post --
If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "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






"okschlaps" wrote in message
...
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?

"Duane Hookom" wrote:

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.[S] = strS And Me.Status = "In-Hand" And Me.PCT 0.75
Then
Me("L" & intS).BackColor = 16744576
ElseIf Me.[S] = 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


"Steve" wrote:

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




"okschlaps" wrote in
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.[S] = "01" And Me.Status = "In-Hand" And Me.PCT 0.75
Then
L1.BackColor = 16744576
ElseIf Me.[S] = "01" And Me.Status = "Leased to other"
And
Me.PCT

0.75
Then
L1.BackColor = 255
Else: L1.BackColor = 16777215
End If

If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT
0.75
Then
L1.BackColor = 16744576
ElseIf Me.[S] = "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















  #14  
Old July 18th, 2007, 04:12 PM posted to microsoft.public.access.reports
Keith Wilby
external usenet poster
 
Posts: 812
Default creating "map" based on field values

"okschlaps" wrote in message
...
I'm getting a "Method or data member not found" error at Rst.Next.


Try MoveNext.

Keith.
www.keithwilby.com

  #15  
Old July 25th, 2007, 10:26 PM posted to microsoft.public.access.reports
okschlaps
external usenet poster
 
Posts: 37
Default creating "map" based on field values

Hello - I'm back. Pulled away on another project.
I'm getting an error at the "Else" line - "Can't find the field 'L37'" I
tried changing the Do until S = 35 or Do While S 36 but that didn't work
either.
Also, should I have an added criteria to relate the Label to the Section? Or
am I not understanding recordsets? Does it automatically look at one line at
a time?
Thanks again!

"Steve" wrote:

Sorry, my brain was running faster than my typing. Rst.Next should be
Rst.MoveNext.

Steve



"okschlaps" wrote in message
...
I'm getting a "Method or data member not found" error at Rst.Next. I've
copied my code in case I've typed something wrong. Thanks
Private Sub Report_Open(Cancel As Integer)
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 Rst!PCT 0.75 Then
Me("L" & intS).BackColor = 255
Else
Me("L" & intS).BackColor = 16777215
End If
Rst.Next
Loop
Rst.Close
Set Rst = Nothing
Set Db = Nothing

End Sub

"Steve" wrote:

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






"okschlaps" wrote in message
...
Steve
It ran, but all the labels came out blue.

"Steve" wrote:

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





"okschlaps" wrote in message
...
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!

"Steve" wrote:

Here is the second If loop in your original post --
If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "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






"okschlaps" wrote in message
...
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?

"Duane Hookom" wrote:

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.[S] = strS And Me.Status = "In-Hand" And Me.PCT 0.75
Then
Me("L" & intS).BackColor = 16744576
ElseIf Me.[S] = 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


"Steve" wrote:

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




"okschlaps" wrote in
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.[S] = "01" And Me.Status = "In-Hand" And Me.PCT 0.75
Then
L1.BackColor = 16744576
ElseIf Me.[S] = "01" And Me.Status = "Leased to other"
And
Me.PCT

0.75
Then
L1.BackColor = 255
Else: L1.BackColor = 16777215
End If

If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT
0.75
Then

  #16  
Old July 26th, 2007, 03:46 PM posted to microsoft.public.access.reports
okschlaps
external usenet poster
 
Posts: 37
Default creating "map" based on field values

Disregard my last post - the problem was my query. I works and it's
beautiful! Thanks for hanging with me, Steve!

"Steve" wrote:

Sorry, my brain was running faster than my typing. Rst.Next should be
Rst.MoveNext.

Steve



"okschlaps" wrote in message
...
I'm getting a "Method or data member not found" error at Rst.Next. I've
copied my code in case I've typed something wrong. Thanks
Private Sub Report_Open(Cancel As Integer)
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 Rst!PCT 0.75 Then
Me("L" & intS).BackColor = 255
Else
Me("L" & intS).BackColor = 16777215
End If
Rst.Next
Loop
Rst.Close
Set Rst = Nothing
Set Db = Nothing

End Sub

"Steve" wrote:

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






"okschlaps" wrote in message
...
Steve
It ran, but all the labels came out blue.

"Steve" wrote:

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





"okschlaps" wrote in message
...
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!

"Steve" wrote:

Here is the second If loop in your original post --
If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "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






"okschlaps" wrote in message
...
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?

"Duane Hookom" wrote:

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.[S] = strS And Me.Status = "In-Hand" And Me.PCT 0.75
Then
Me("L" & intS).BackColor = 16744576
ElseIf Me.[S] = 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


"Steve" wrote:

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




"okschlaps" wrote in
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.[S] = "01" And Me.Status = "In-Hand" And Me.PCT 0.75
Then
L1.BackColor = 16744576
ElseIf Me.[S] = "01" And Me.Status = "Leased to other"
And
Me.PCT

0.75
Then
L1.BackColor = 255
Else: L1.BackColor = 16777215
End If

If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT
0.75
Then

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.