VBA Code in a Report

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I have the following code in a report that it trying to
count the when a text field says "Yes" instead of "No" I
always end up with 0 as a count when there are 15 to
count. I stepped through the code and it does not look
like the code is look at all of the records. I t appears
to only look at one of the records which has a "No" It may
not be stepping through to the next record on the report,
although it does not infinitely loop.

Any help would be appreciated.

Private Sub Report_Activate()
Dim RecordCount As Integer
Dim R As Report
Dim Rec As Control


For Each Rec In Me.Report
If [Reports]![rpt for qa sup reports all]![Text24]
= "Yes" Then
RecordCount = RecordCount + 1
End If
Next
Me.Text25 = RecordCount

End Sub
 
You may be confusing ACCESS by using RecordCount as a variable. It is a
property of the report's Recordset object.

Change RecordCount to intRecordCount throughout the code, and see if that
works.
 
Changed RecordCount to IntRecordCount and I am still
getting 0 as the result.
-----Original Message-----
You may be confusing ACCESS by using RecordCount as a variable. It is a
property of the report's Recordset object.

Change RecordCount to intRecordCount throughout the code, and see if that
works.

--

Ken Snell
<MS ACCESS MVP>

I have the following code in a report that it trying to
count the when a text field says "Yes" instead of "No" I
always end up with 0 as a count when there are 15 to
count. I stepped through the code and it does not look
like the code is look at all of the records. I t appears
to only look at one of the records which has a "No" It may
not be stepping through to the next record on the report,
although it does not infinitely loop.

Any help would be appreciated.

Private Sub Report_Activate()
Dim RecordCount As Integer
Dim R As Report
Dim Rec As Control


For Each Rec In Me.Report
If [Reports]![rpt for qa sup reports all]![Text24]
= "Yes" Then
RecordCount = RecordCount + 1
End If
Next
Me.Text25 = RecordCount

End Sub
Click to expand...


.
Click to expand...
 
Ahhhh.... you are constantly checking the same textbox over and over and
over.

Change these lines:
If [Reports]![rpt for qa sup reports all]![Text24] = "Yes" Then
intRecordCount = intRecordCount + 1
End If


to these:
If Rec.ControlType = acTextbox Then
If Rec.Value = "Yes" Then _
intRecordCount = intRecordCount + 1
End If

--

Ken Snell
<MS ACCESS MVP>

Chris said:
Changed RecordCount to IntRecordCount and I am still
getting 0 as the result.
-----Original Message-----
You may be confusing ACCESS by using RecordCount as a variable. It is a
property of the report's Recordset object.

Change RecordCount to intRecordCount throughout the code, and see if that
works.

--

Ken Snell
<MS ACCESS MVP>

I have the following code in a report that it trying to
count the when a text field says "Yes" instead of "No" I
always end up with 0 as a count when there are 15 to
count. I stepped through the code and it does not look
like the code is look at all of the records. I t appears
to only look at one of the records which has a "No" It may
not be stepping through to the next record on the report,
although it does not infinitely loop.

Any help would be appreciated.

Private Sub Report_Activate()
Dim RecordCount As Integer
Dim R As Report
Dim Rec As Control


For Each Rec In Me.Report
If [Reports]![rpt for qa sup reports all]![Text24]
= "Yes" Then
RecordCount = RecordCount + 1
End If
Next
Me.Text25 = RecordCount

End Sub
Click to expand...


.
Click to expand...
Click to expand...
 
It is still not counting. I am still getting a 0. I think
now it jumps through each control on the report and checks
to see if it is a text box, if it is, then it jumps down
to see if the value = "Yes" but it still doesn't cycle
through each record on the report to check it's value.

It looks like we a re still check the same value over and
over again just now on each control not each instance of a
record.

-----Original Message-----
Ahhhh.... you are constantly checking the same textbox over and over and
over.

Change these lines:
If [Reports]![rpt for qa sup reports all]![Text24] = "Yes" Then
intRecordCount = intRecordCount + 1
End If


to these:
If Rec.ControlType = acTextbox Then
If Rec.Value = "Yes" Then _
intRecordCount = intRecordCount + 1
End If

--

Ken Snell
<MS ACCESS MVP>

Changed RecordCount to IntRecordCount and I am still
getting 0 as the result.
-----Original Message-----
You may be confusing ACCESS by using RecordCount as a variable. It is a
property of the report's Recordset object.

Change RecordCount to intRecordCount throughout the
Click to expand...
code,
and see if that
works.

--

Ken Snell
<MS ACCESS MVP>

I have the following code in a report that it trying to
count the when a text field says "Yes" instead of "No" I
always end up with 0 as a count when there are 15 to
count. I stepped through the code and it does not look
like the code is look at all of the records. I t appears
to only look at one of the records which has a "No" It may
not be stepping through to the next record on the report,
although it does not infinitely loop.

Any help would be appreciated.

Private Sub Report_Activate()
Dim RecordCount As Integer
Dim R As Report
Dim Rec As Control


For Each Rec In Me.Report
If [Reports]![rpt for qa sup reports all]![Text24]
= "Yes" Then
RecordCount = RecordCount + 1
End If
Next
Me.Text25 = RecordCount

End Sub



.
Click to expand...
Click to expand...


.
Click to expand...
 
I see that you're trying to run this code in the report's Activate event.
When that event is running, likely the textboxes don't have any values yet.

Run the code in the Format event of the section of the report that contains
the Text25 control.

--

Ken Snell
<MS ACCESS MVP>


Chris said:
It is still not counting. I am still getting a 0. I think
now it jumps through each control on the report and checks
to see if it is a text box, if it is, then it jumps down
to see if the value = "Yes" but it still doesn't cycle
through each record on the report to check it's value.

It looks like we a re still check the same value over and
over again just now on each control not each instance of a
record.

-----Original Message-----
Ahhhh.... you are constantly checking the same textbox over and over and
over.

Change these lines:
If [Reports]![rpt for qa sup reports all]![Text24] = "Yes" Then
intRecordCount = intRecordCount + 1
End If


to these:
If Rec.ControlType = acTextbox Then
If Rec.Value = "Yes" Then _
intRecordCount = intRecordCount + 1
End If

--

Ken Snell
<MS ACCESS MVP>

Changed RecordCount to IntRecordCount and I am still
getting 0 as the result.
-----Original Message-----
You may be confusing ACCESS by using RecordCount as a
variable. It is a
property of the report's Recordset object.

Change RecordCount to intRecordCount throughout the code,
and see if that
works.

--

Ken Snell
<MS ACCESS MVP>

message
I have the following code in a report that it trying to
count the when a text field says "Yes" instead of "No" I
always end up with 0 as a count when there are 15 to
count. I stepped through the code and it does not look
like the code is look at all of the records. I t appears
to only look at one of the records which has a "No" It
may
not be stepping through to the next record on the
report,
although it does not infinitely loop.

Any help would be appreciated.

Private Sub Report_Activate()
Dim RecordCount As Integer
Dim R As Report
Dim Rec As Control


For Each Rec In Me.Report
If [Reports]![rpt for qa sup reports all]![Text24]
= "Yes" Then
RecordCount = RecordCount + 1
End If
Next
Me.Text25 = RecordCount

End Sub



.
Click to expand...


.
Click to expand...
Click to expand...
 
Chris said:
I have the following code in a report that it trying to
count the when a text field says "Yes" instead of "No" I
always end up with 0 as a count when there are 15 to
count. I stepped through the code and it does not look
like the code is look at all of the records. I t appears
to only look at one of the records which has a "No" It may
not be stepping through to the next record on the report,
although it does not infinitely loop.

Any help would be appreciated.

Private Sub Report_Activate()
Dim RecordCount As Integer
Dim R As Report
Dim Rec As Control


For Each Rec In Me.Report
If [Reports]![rpt for qa sup reports all]![Text24]
= "Yes" Then
RecordCount = RecordCount + 1
End If
Next
Me.Text25 = RecordCount
End Sub
Click to expand...


There is no mechanism for you to use code to loop through
the records in a report. Certainly nothing usful can be
done in the Activate event, which doesn't even fire if the
report is going directly to a printer.

A better approach would be to use an aggregate function in
the Text25 text box in the Report header/footer section:

=Abs(Sum(tablefield = "Yes"))

Note that the above tablefield is NOT the name of a text box
control in the report. It must be a field in the report's
base table/query (the one your Text24 text box is bound to).
 
Still no go. I am not sure what else would work. You have
really good ideas but nothing seems to cycle through each
record.
-----Original Message-----
I see that you're trying to run this code in the report's Activate event.
When that event is running, likely the textboxes don't have any values yet.

Run the code in the Format event of the section of the report that contains
the Text25 control.

--

Ken Snell
<MS ACCESS MVP>


It is still not counting. I am still getting a 0. I think
now it jumps through each control on the report and checks
to see if it is a text box, if it is, then it jumps down
to see if the value = "Yes" but it still doesn't cycle
through each record on the report to check it's value.

It looks like we a re still check the same value over and
over again just now on each control not each instance of a
record.

-----Original Message-----
Ahhhh.... you are constantly checking the same textbox over and over and
over.

Change these lines:
If [Reports]![rpt for qa sup reports all]![Text24] = "Yes" Then
intRecordCount = intRecordCount + 1
End If


to these:
If Rec.ControlType = acTextbox Then
If Rec.Value = "Yes" Then _
intRecordCount = intRecordCount + 1
End If

--

Ken Snell
<MS ACCESS MVP>

Changed RecordCount to IntRecordCount and I am still
getting 0 as the result.
-----Original Message-----
You may be confusing ACCESS by using RecordCount as a
variable. It is a
property of the report's Recordset object.

Change RecordCount to intRecordCount throughout the code,
and see if that
works.

--

Ken Snell
<MS ACCESS MVP>

message
I have the following code in a report that it trying to
count the when a text field says "Yes" instead of "No" I
always end up with 0 as a count when there are 15 to
count. I stepped through the code and it does not look
like the code is look at all of the records. I t appears
to only look at one of the records which has a "No" It
may
not be stepping through to the next record on the
report,
although it does not infinitely loop.

Any help would be appreciated.

Private Sub Report_Activate()
Dim RecordCount As Integer
Dim R As Report
Dim Rec As Control


For Each Rec In Me.Report
If [Reports]![rpt for qa sup reports all]! [Text24]
= "Yes" Then
RecordCount = RecordCount + 1
End If
Next
Me.Text25 = RecordCount

End Sub



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Thank you, that worked just fine
-----Original Message-----
Chris said:
I have the following code in a report that it trying to
count the when a text field says "Yes" instead of "No" I
always end up with 0 as a count when there are 15 to
count. I stepped through the code and it does not look
like the code is look at all of the records. I t appears
to only look at one of the records which has a "No" It may
not be stepping through to the next record on the report,
although it does not infinitely loop.

Any help would be appreciated.

Private Sub Report_Activate()
Dim RecordCount As Integer
Dim R As Report
Dim Rec As Control


For Each Rec In Me.Report
If [Reports]![rpt for qa sup reports all]![Text24]
= "Yes" Then
RecordCount = RecordCount + 1
End If
Next
Me.Text25 = RecordCount
End Sub
Click to expand...


There is no mechanism for you to use code to loop through
the records in a report. Certainly nothing usful can be
done in the Activate event, which doesn't even fire if the
report is going directly to a printer.

A better approach would be to use an aggregate function in
the Text25 text box in the Report header/footer section:

=Abs(Sum(tablefield = "Yes"))

Note that the above tablefield is NOT the name of a text box
control in the report. It must be a field in the report's
base table/query (the one your Text24 text box is bound to).
Click to expand...
 
Marsh makes a good point in his reply. Your code is cycling through the
textboxes on the report, but for just a single record. It won't cycle
through the records in the report's recordsource.

If Marsh's suggestion isn't quite what you're seeking, then tell us *in
words* about the data that the report contains, what you want to do, what
the report's Recordsource query is, etc.

--

Ken Snell
<MS ACCESS MVP>


Chris said:
Still no go. I am not sure what else would work. You have
really good ideas but nothing seems to cycle through each
record.
-----Original Message-----
I see that you're trying to run this code in the report's Activate event.
When that event is running, likely the textboxes don't have any values yet.

Run the code in the Format event of the section of the report that contains
the Text25 control.

--

Ken Snell
<MS ACCESS MVP>


It is still not counting. I am still getting a 0. I think
now it jumps through each control on the report and checks
to see if it is a text box, if it is, then it jumps down
to see if the value = "Yes" but it still doesn't cycle
through each record on the report to check it's value.

It looks like we a re still check the same value over and
over again just now on each control not each instance of a
record.


-----Original Message-----
Ahhhh.... you are constantly checking the same textbox
over and over and
over.

Change these lines:
If [Reports]![rpt for qa sup reports all]![Text24]
= "Yes" Then
intRecordCount = intRecordCount + 1
End If


to these:
If Rec.ControlType = acTextbox Then
If Rec.Value = "Yes" Then _
intRecordCount = intRecordCount + 1
End If

--

Ken Snell
<MS ACCESS MVP>

message
Changed RecordCount to IntRecordCount and I am still
getting 0 as the result.
-----Original Message-----
You may be confusing ACCESS by using RecordCount as a
variable. It is a
property of the report's Recordset object.

Change RecordCount to intRecordCount throughout the
code,
and see if that
works.

--

Ken Snell
<MS ACCESS MVP>

message
I have the following code in a report that it trying to
count the when a text field says "Yes" instead
of "No" I
always end up with 0 as a count when there are 15 to
count. I stepped through the code and it does not look
like the code is look at all of the records. I t
appears
to only look at one of the records which has a "No" It
may
not be stepping through to the next record on the
report,
although it does not infinitely loop.

Any help would be appreciated.

Private Sub Report_Activate()
Dim RecordCount As Integer
Dim R As Report
Dim Rec As Control


For Each Rec In Me.Report
If [Reports]![rpt for qa sup reports all]! [Text24]
= "Yes" Then
RecordCount = RecordCount + 1
End If
Next
Me.Text25 = RecordCount

End Sub



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top