Using Reports Recordset in a DCount Function

I am using this code in a ControlSource of a text box on a Report. It doesn't work as it doesn't like the syntax which states Me.Recordset.

=DCount("SatisfactionRating", Me.Recordset,"SatisfactionRating = 1")

Please advise if you know what would be correct syntax for the above function. Thanks.
dpokerbearAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  Instead of Me.Recordset, you need to use whatever the report is using.  If it's a table, name the table.  If it's a query, name the query.

 You also could get the Dcount in code.

JimD.
dpokerbearAuthor Commented:
Hi JDettman,

I know that [RecordSource] can be used and that just gets whatever table or query is listed under that property.

However, when I am opening the report using docmd.openreport, i specify a WHERE clause. So i actually need to work with the recordset you see, otherwise it will display all records for that table or query and I don't want that...
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Your not going to be able to get at the reports actual recordsource with anything other then code and a recordset variable.  If you cannot duplicate the where clause used to open the report and add the SatisfactionRating = 1 check as well, then your going to be left doing something like this:


Dim lngSatisfactionCount as long  ' Declared module level

In the OnLoad event:

  Dim rst as DAO.Recordset

  Set rst = Me.Recordset

  lngSatisfactionCount = 0
  Do until rst.EOF
     If rst![SatisfactionCount] = 1 then lngSatisfactionCount = lngSatisfactionCount + 1
     rst.Movenext
  Loop
 
  Set rst = nothing

 and then in the OnFormat of whatever section your printing this in:

  Me.<some control> = lngSatisfactionCount

JimD.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dpokerbearAuthor Commented:
Jim,

I made a query to work around the problem.

Thanks for the effort in assisting me with this problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.