VBA Code in a Report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 09 Mar 2005 16:50 GMT

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

Reply to this Message

Ken Snell [MVP] - 09 Mar 2005 17:33 GMT

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.

Signature

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
[quoted text clipped - 21 lines]
>
> End Sub

Reply to this Message

Chris - 09 Mar 2005 17:39 GMT

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
[quoted text clipped - 30 lines]
>
>.

Reply to this Message

Ken Snell [MVP] - 09 Mar 2005 18:48 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

> Changed RecordCount to IntRecordCount and I am still
> getting 0 as the result.
[quoted text clipped - 38 lines]
>>
>>.

Reply to this Message

Chris - 09 Mar 2005 19:19 GMT

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
[quoted text clipped - 55 lines]
>
>.

Reply to this Message

Ken Snell [MVP] - 09 Mar 2005 19:44 GMT

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.

Signature

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
[quoted text clipped - 72 lines]
>>
>>.

Reply to this Message

Chris - 09 Mar 2005 19:57 GMT

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.
[quoted text clipped - 81 lines]
>
>.

Reply to this Message

Ken Snell [MVP] - 09 Mar 2005 20:17 GMT

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.

Signature

Ken Snell
<MS ACCESS MVP>

> Still no go. I am not sure what else would work. You have
> really good ideas but nothing seems to cycle through each
[quoted text clipped - 98 lines]
>>
>>.

Reply to this Message

Marshall Barton - 09 Mar 2005 19:48 GMT

>I have the following code in a report that it trying to
>count the when a text field says "Yes" instead of "No" I
[quoted text clipped - 20 lines]
> Me.Text25 = RecordCount
>End Sub

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

Signature

Marsh
MVP [MS Access]

Reply to this Message

anonymous@discussions.microsoft.com - 09 Mar 2005 20:15 GMT

Thank you, that worked just fine

>-----Original Message-----
>
[quoted text clipped - 36 lines]
>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).

Reply to this Message