How to use a "For Each...Next Statement using an array in a report

[John S via AccessMonster.com]

HI,
My name is John.
I am trying do use the For Each Element In Group... Next Statement in Access
'97. I need to use it in a report and I need to use an array. I want to refer
to a text box on my report that contains my array. My code looks like this:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

Dim TestArray(15) As Integer, Itm As Variant
TestArray(15) = Me.Text41

For Each Itm In TestArray
'Image1.Visible = CheckBox1 = True
'1.1. PROJECT DOCUMENTATION AND REVIEW
If Project_Delivery_1_1_1 = False Then
lbl_1_1.Visible = Project_Delivery_1_1_1 = False
Lbl_Incompleted_1_1.Visible = Project_Delivery_1_1_1 = False
End If

If Quality_Peer_Review_1_1_2 = False Then
lbl_1_1.Visible = Quality_Peer_Review_1_1_2 = False
Lbl_Incompleted_1_1.Visible = Quality_Peer_Review_1_1_2 = False
End If

Next Itm

Can anyone help me with making this code work. Thanks so much if you can help.
I have tried everything I can think of but nothing works!!. HELP!!! PLEASE

 

[John Nurick]

Hi John,

I don't really understand what you're trying to do. I've put some
comments in line that may help.

HI,
My name is John.
I am trying do use the For Each Element In Group... Next Statement in Access
'97. I need to use it in a report and I need to use an array. I want to refer
to a text box on my report that contains my array.

A textbox can't contain an array.

My code looks like this:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

Dim TestArray(15) As Integer, Itm As Variant

The previous line declared an array of 15 integers, which these days is
normally means having elements TestArray(0) to TestArray(14), so this

TestArray(15) = Me.Text41

will give an out-of-range error. (Unless you have declared Option Base
1, which is deprecated.)

TestArray(14) = Me.Text41

attempts to assign the 15th element of the array the value of the
textbox Text41. This value is basically a text value (though it could be
Null), but an integer is a number. VBA does its best to convert between
variable types, but will fail if the textbox is empty or if it doesn't
contain something recognisable as a whole number between about -32,000
and +32,000.

For Each Itm In TestArray
'Image1.Visible = CheckBox1 = True
'1.1. PROJECT DOCUMENTATION AND REVIEW
If Project_Delivery_1_1_1 = False Then

This If-condition means that the next two lines will
only ever be executed if Project_Delivery_1_1_1 is False,
so they can be simplified to

lbl_1_1.Visible = True
Lbl_Incompleted_1_1.Visible = True

lbl_1_1.Visible = Project_Delivery_1_1_1 = False
Lbl_Incompleted_1_1.Visible = Project_Delivery_1_1_1 = False
End If

If Quality_Peer_Review_1_1_2 = False Then

and the same here

lbl_1_1.Visible = Quality_Peer_Review_1_1_2 = False
Lbl_Incompleted_1_1.Visible = Quality_Peer_Review_1_1_2 = False
End If

But what I don't understand is that nowhere in this For ... Next loop
are you referring to the loop variable Itm. What are you trying to do?

 

[John S via AccessMonster.com]

Hi John,

I don't really understand what you're trying to do. I've put some
comments in line that may help.


A textbox can't contain an array.


The previous line declared an array of 15 integers, which these days is
normally means having elements TestArray(0) to TestArray(14), so this


will give an out-of-range error. (Unless you have declared Option Base
1, which is deprecated.)


attempts to assign the 15th element of the array the value of the
textbox Text41. This value is basically a text value (though it could be
Null), but an integer is a number. VBA does its best to convert between
variable types, but will fail if the textbox is empty or if it doesn't
contain something recognisable as a whole number between about -32,000
and +32,000.


This If-condition means that the next two lines will
only ever be executed if Project_Delivery_1_1_1 is False,
so they can be simplified to

lbl_1_1.Visible = True
Lbl_Incompleted_1_1.Visible = True


and the same here


But what I don't understand is that nowhere in this For ... Next loop
are you referring to the loop variable Itm. What are you trying to do?

I am Sorry,
I figured my information might be a little confusing. I will try and
explain what I am trying to do in the best way I know how.

Before I begin, I would like to tell all you guys who help us others out here
in Access just how remarkable you all are. You all are so great in helping
out others who are having problems with Access. I have gained so much
knowledge about Access just from reading from other threads. Thanks so much
for all your help.

Anyway,
I have a report that is grouped by a text box that contains a
string value. The information for this text box comes from a query that lists
15 project names. Each project name in the query has about 75 yes/no check
boxes associated with each project name.

What My report DOES is: list only project names and their associated labels
whose associated check boxes = to “False”.

- In the Page header section of my report is my Textbox Project_Name

- In the Project_Name_Header section are my labels and checkboxes that are
associated with each Project_Name.

- In the Project_Name group Header section of my report on the "On Format"
event I have an event procedure that says:

If (Quality_Peer_Review_1_1_2 = False) Then
Me.lbl_1_1.Visible = True
Me.Lbl_Incompleted_1_1.Visible = True
End If

If (EndUsers_1_3 = False) Then
Me.Lbl_1_3.Visible = True
Me.Lbl_Incompleted_1_3.Visible = True
End If

Etc…

- The above code is repeated for all my 75 checkboxes.

- ALL Labels on the report are set to "visible = False"

- Quality_Peer_Review_1_1_2 and EndUsers_1_3 are examples of my checkbox
names

- Me.lbl_1_1 is the first label associated with the checkbox

- Me.Lbl_Incompleted_1_1 is the second label associated with the same
checkbox.

If a checkbox is selected (True) the labels and project_name associated with
these checkboxes remains invisible and does not appear on the report

If a checkbox is not selected (False) the labels and project_name associated
with these checkbox become visible and appear on the report.

My PROBLEM is this. My report works PERFECT for all the checkboxes of the
FIRST Project_Name.
BUT
When I move to the second Project Name where labels are supposed to be
invisible because the checkboxes are selected , labels appear on the report.

The checkboxes themselves for ALL my 15 project_name works correctly. I know
this because I placed every checkbox on the report in order to help trouble
shoot any problems. The checkboxes on the report for all 15 project names
correctly indicate that they have either been selected or not. The PROBLEM I
am having is in making my above code work for all the labels of the other 14
project_names.
The code seems to be ignored.

Does this problem make any sense to you? I am sorry if my explaintion of my
problem tends be a little long . Anyway you can help. What other information
might you need from me? Thanks so much in advance for any help you cna
provide!!

 

[John S via AccessMonster.com]

Hi John,

[quoted text clipped - 68 lines]

Please respond in the newgroup and not by email.

I am Sorry,
I figured my information might be a little confusing. I will try and
explain what I am trying to do in the best way I know how.

Before I begin, I would like to tell all you guys who help us others out here
in Access just how remarkable you all are. You all are so great in helping
out others who are having problems with Access. I have gained so much
knowledge about Access just from reading from other threads. Thanks so much
for all your help.

Anyway,
I have a report that is grouped by a text box that contains a
string value. The information for this text box comes from a query that lists
15 project names. Each project name in the query has about 75 yes/no check
boxes associated with each project name.

What My report DOES is: list only project names and their associated labels
whose associated check boxes = to “False”.

- In the Page header section of my report is my Textbox Project_Name

- In the Project_Name_Header section are my labels and checkboxes that are
associated with each Project_Name.

- In the Project_Name group Header section of my report on the "On Format"
event I have an event procedure that says:

If (Quality_Peer_Review_1_1_2 = False) Then
Me.lbl_1_1.Visible = True
Me.Lbl_Incompleted_1_1.Visible = True
End If

If (EndUsers_1_3 = False) Then
Me.Lbl_1_3.Visible = True
Me.Lbl_Incompleted_1_3.Visible = True
End If

Etc…

- The above code is repeated for all my 75 checkboxes.

- ALL Labels on the report are set to "visible = False"

- Quality_Peer_Review_1_1_2 and EndUsers_1_3 are examples of my checkbox
names

- Me.lbl_1_1 is the first label associated with the checkbox

- Me.Lbl_Incompleted_1_1 is the second label associated with the same
checkbox.

If a checkbox is selected (True) the labels and project_name associated with
these checkboxes remains invisible and does not appear on the report

If a checkbox is not selected (False) the labels and project_name associated
with these checkbox become visible and appear on the report.

My PROBLEM is this. My report works PERFECT for all the checkboxes of the
FIRST Project_Name.
BUT
When I move to the second Project Name where labels are supposed to be
invisible because the checkboxes are selected , labels appear on the report.

The checkboxes themselves for ALL my 15 project_name works correctly. I know
this because I placed every checkbox on the report in order to help trouble
shoot any problems. The checkboxes on the report for all 15 project names
correctly indicate that they have either been selected or not. The PROBLEM I
am having is in making my above code work for all the labels of the other 14
project_names.
The code seems to be ignored.

Does this problem make any sense to you? I am sorry if my explaintion of my
problem tends be a little long . Anyway you can help. What other information
might you need from me? Thanks so much in advance for any help you cna
provide!!
I forgot to mention that this is why I originally tried to write code that would Specifically act on each project_name, hence the reason for using the "For Each... Next Statement" using an array rather than a collection. Obviously my code did not work.

 

[John Nurick]

Before I begin, I would like to tell all you guys who help us others out here
in Access just how remarkable you all are. You all are so great in helping
out others who are having problems with Access. I have gained so much
knowledge about Access just from reading from other threads. Thanks so much
for all your help.

Thank you for the kind words.

Anyway,
I have a report that is grouped by a text box that contains a
string value. The information for this text box comes from a query that lists
15 project names. Each project name in the query has about 75 yes/no check
boxes associated with each project name.

What My report DOES is: list only project names and their associated labels
whose associated check boxes = to “False”.

- In the Page header section of my report is my Textbox Project_Name

- In the Project_Name_Header section are my labels and checkboxes that are
associated with each Project_Name.

- In the Project_Name group Header section of my report on the "On Format"
event I have an event procedure that says:

If (Quality_Peer_Review_1_1_2 = False) Then
Me.lbl_1_1.Visible = True
Me.Lbl_Incompleted_1_1.Visible = True
End If

If (EndUsers_1_3 = False) Then
Me.Lbl_1_3.Visible = True
Me.Lbl_Incompleted_1_3.Visible = True
End If

The one thing that springs to mind is that this code will make the
labels visible if the checkbox.value is false, but won't hide them if
it's true. What happens if you try something like this?

....
If (Quality_Peer_Review_1_1_2 = False) Then
Me.lbl_1_1.Visible = True
Me.Lbl_Incompleted_1_1.Visible = True
Debug.Print Project_Name, "Quality_Peer_Review_1_1_2 is False"
Else
Me.lbl_1_1.Visible = False
Me.Lbl_Incompleted_1_1.Visible = False
Debug.Print Project_Name, "Quality_Peer_Review_1_1_2 is True"
End If
....

The Debug.Print statements will leave a trail in the Immediate pane so
you can be sure the code is executing; comment them out when you've got
it right.