• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 592
  • Last Modified:

current Recordset

I've looked all throughout the documentation, but I can't find an answer to this seemingly simple question.

I want to write a function much like the DCount domain aggregate function that will allow me to count the number of records in a query that meet a specific criteria.  I want to use this function in several different reports that are tied to parameter-based queries.  My questions is:

Is there some statement that allows me to access the Recordset that the underlying query produces that is used in the report?  I'm hoping that there is some simple way to do something like this:

DIM thisDB As Database
Dim thisRS As Recordset

Set thisDB = currentDB
Set thisRS = thisDB.currentRS

I'm fairly new to this so I'm hoping that this isn't a stupid question.  Any help would be appreciated
0
kaznicki
Asked:
kaznicki
  • 6
  • 5
  • 2
  • +4
1 Solution
 
HelicopterCommented:
Assume you have a textbox on your report that will display the number of records.

Set the controlsource to :

=mycount([Reports].[YourReportName].[RecordSource])

Then have a function

Public Function mycount(str As String) As Long

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset(str, dbOpenSnapshot)
rst.MoveLast
rst.MoveFirst

mycount = rst.RecordCount

End Function


This will fill the number of records returned into the textbox
0
 
HelicopterCommented:
Sorry, just re-read the bit about parameter based queries. This complicates it a little. You will probably have to reject my answer
0
 
brewdogCommented:
You seem to have a couple options. If you're talking about queries, you could do this:

dim rs as recordset
dim qdf as querydef
dim strSQL as string

set qdf = currentdb().querydefs("NameOfYourQueryDef")
strSQL = qdf.SQL
'at this point you'd want to set your parameter values, then redefine strSQL to include the values
set rs = currentdb().openrecordset(strSQL)
rs.movelast            'this will give error 3021 if there aren't any
msgbox rs.recordcount

I just ran this (on a non-parameter query) and it worked fine. I can work on getting the parameter part, too, if this sounds like a promising approach to you. Let me know -- it might take a few minutes.

brewdog
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dtomynCommented:
I may not understand your question completely, so will summarize what I think you are trying to do.  A report is generated ok, but you want a text box (or something) to display the number of records it generated?  If this is somewhat close, you might want to try something like:
Me![subfrmSearchResults].Form.RecordsetClone.RecordCount
The above of course is determining the number of records that are in a subform on a main form, but I am sure you could apply it to a report by changing ".Form" to ".Report" ...let me know if this is close....

0
 
dtomynCommented:
Oh just to extend on the above, if you wanted to use a recordset, set the recordset to the recordsetclone like the following:
Set rs = Me.Addresses.Form.RecordsetClone
Then...
With rs
   If Not .eof then
        .MoveLast
        MsgBox .RecordCount
   End If
   .close
End Eith

0
 
StefaanCommented:
There is a reference to the currentrecordset in Access : RecordsetClone.  When you are on a form (or report) the following code will give you the current recordset :

Dim DB as DataBase
Dim rsCurrent as Recordset

Set DB = CurrentDB()
Set Rs = Me.RecordsetClone


This should do the trick (at least in Access95 and Access97)
0
 
dtomynCommented:
Hmmm, the answer looks EXACTLY like my comment yet it was a day later... hmmm....
0
 
kaznickiAuthor Commented:
I keep getting a compile error message that states that its an impropper use of the 'Me' keyword.  I think this is getting closer though
0
 
dtomynCommented:
What does your code look like right now, and how exactly is it being used... i.e. is it a main report with a subreport and you want to count the subreport from the main report.  Please give details.
0
 
kaznickiAuthor Commented:
I'm still perplexed about this, though I do appreciate everyone's help.  Perhaps I should be more specific in what I'm trying to do.  

I have a report that is tied to a parameter based query.  I run the report, enter the parameter values, and the report is generated.  In the report there is some header sections, a detail section, and a summary section.  The detail section contains the individual records that match the parameters, and the summary section contains statistics such as Avg, Min, and Max (using the built in Access functions) for those records.  What I'm hoping to do is to write other functions for the summary section that can count the number of detail records that meet some criteria, such as all of those records that have a value in field x that are > 7.  In short, I am hoping to get direct access to the RecordSet produced by the report/query and test it against some criteria, without having to rerun the query within the function.  The standard functions (Avg, Min, and Max) are almost exactly what I'm trying to replicate, though with a slight twist.  Does anyone know how they work (what the code looks like)?

Here's the code I've done so far (forgive if not propper syntax) based on the RecordsetClone idea that produces a compile error:

Public Function myCount(str As String) As Long

Dim db As Database
Dim rst As Recordset
Dim counter As Long

counter = 0

Set db = CurrentDb()
Set rst = Me.RecordsetClone

rst.MoveFirst

Do
If rst!str > 7 Then
    counter = counter + 1
    rst.MoveNext
End If
Loop While Not rst.EOF

myCount = counter

End Function

Any help is greatly appreciated
0
 
dtomynCommented:
The reason the me does not work is that it is inappropriate to use it within an actual function. i.e. if you were to put the same code on the OnFormat method of the report section where you want to generate this you might have better success.  Otherwise, you might want to consider sending the function another paramater -- the report's name (strReportName) and then use something like:
Reports(strReportName).RecordSetClone
I am not sure of the exact syntax (if this question is still open in a few hours a will try out a sample database), but I think this should be close.  About the only thing I can think of off hand that might be different is the above may want:
Reports(strReportName).Report.RecordSetClone

Keep posting your results...
0
 
alcappsCommented:
if you are attempting to count the number of records that meet a specific set.. then go to the detail on print and use this.. code

if whatever field > 7 then
   field_on_report_counter =  field_on_report_counter + 1
end if
add field_on_report_counter to the Footer and you got your count.

hope this helps
0
 
StefaanCommented:
Why don't you just pass the recordset to the function ?

Public Function MyCount (RS as Recordset) as Long
     rs.MoveFirst

     
     do
       If rs!str > 7 Then
         counter = counter + 1
         rs.MoveNext
       End If
     Loop While Not rs.EOF

     myCount = counter
End Function

This should work.  The only thing you need to do, is pass the Recordset which you want to count to the funtion.  Too bad I couldn't post this as an answer
0
 
kaznickiAuthor Commented:
I still haven't been able to successfully any of these responses.  Does anyone know what the code to the the stock Count() or Avg() function might look like?
0
 
kaznickiAuthor Commented:
Adjusted points to 200
0
 
kaznickiAuthor Commented:
Does anyone know what the code to the stock AVG() and COUNT() functions might look like?  I think I could answer this easily if I could see that code.
0
 
DedushkaCommented:
Hi, kaznicki!

I can propose a way to calculate "the number of detail records that meet some criteria, such as all of those records that have a value in field x that are > 7".

1. Place on detail section invisible textbox "txt1" and set its Control Sourse property to
=IIf([fld_X]>7;1;0)
and set Running Sum property to
Over All

2. Place on Report Footer textbox "txt2" and set its Control Source property to
=[txt1]

That's all.
Cheers,
Dedushka
0
 
DedushkaCommented:
kaznicki,
if on your computer delimiters set to "," then you should use
=IIf([fld_X]>7,1,0)
instead of
=IIf([fld_X]>7;1;0)

0
 
kaznickiAuthor Commented:
Thanks Dedushka!

Not quite what i had in mind, but it does what I was hoping nonetheless.  The points are yours!  I appreciate your help very much.  Thanks to all who  tried as well.


0
 
alcappsCommented:
Good Answer thats what I proposed but without an IIF statement..
Have a Nice day
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 6
  • 5
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now