Solved

current Recordset

Posted on 1999-01-28
20
512 Views
Last Modified: 2011-10-03
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
Comment
Question by:kaznicki
  • 6
  • 5
  • 2
  • +4
20 Comments
 
LVL 8

Expert Comment

by:Helicopter
ID: 1977968
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
 
LVL 8

Expert Comment

by:Helicopter
ID: 1977969
Sorry, just re-read the bit about parameter based queries. This complicates it a little. You will probably have to reject my answer
0
 
LVL 10

Expert Comment

by:brewdog
ID: 1977970
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
 
LVL 5

Expert Comment

by:dtomyn
ID: 1977971
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
 
LVL 5

Expert Comment

by:dtomyn
ID: 1977972
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
 
LVL 3

Expert Comment

by:Stefaan
ID: 1977973
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
 
LVL 5

Expert Comment

by:dtomyn
ID: 1977974
Hmmm, the answer looks EXACTLY like my comment yet it was a day later... hmmm....
0
 

Author Comment

by:kaznicki
ID: 1977975
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
 
LVL 5

Expert Comment

by:dtomyn
ID: 1977976
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
 

Author Comment

by:kaznicki
ID: 1977977
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Expert Comment

by:dtomyn
ID: 1977978
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
 
LVL 1

Expert Comment

by:alcapps
ID: 1977979
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
 
LVL 3

Expert Comment

by:Stefaan
ID: 1977980
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
 

Author Comment

by:kaznicki
ID: 1977981
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
 

Author Comment

by:kaznicki
ID: 1977982
Adjusted points to 200
0
 

Author Comment

by:kaznicki
ID: 1977983
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
 
LVL 7

Accepted Solution

by:
Dedushka earned 200 total points
ID: 1977984
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
 
LVL 7

Expert Comment

by:Dedushka
ID: 1977985
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
 

Author Comment

by:kaznicki
ID: 1977986
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
 
LVL 1

Expert Comment

by:alcapps
ID: 1977987
Good Answer thats what I proposed but without an IIF statement..
Have a Nice day
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now