VBA Code in a Report

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
 
K

Ken Snell [MVP]

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.
 
C

Chris

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...
 
K

Ken Snell [MVP]

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...
 
C

Chris

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...
 
K

Ken Snell [MVP]

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...
 
M

Marshall Barton

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).
 
C

Chris

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...
 
G

Guest

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...
 
K

Ken Snell [MVP]

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding Blank Lines to a Report 0
getting current record count in a table 5
Record Count 12
Print to PDF in VBA Code 11
Removing OrderBy from Tables and Queries in VBA 2
Code Not Working For Deleting All Linked Tables 3
Random to appear on a report 4
Change report printer destination using code 5

Top