recordset count not the correct value

Hello,

I wrote a simple function that returns a record count.  However, the recordset set count I've been returning is always the maximum number of records in the underlying recordset which contains 787 records.  Even if the task order is not equal to mTaskOrder (a public variable set in the main form current event), I still get a record count of 787.  It's almost as if the filter is not working or rather is returning everything.
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("qryShareAllocation", dbOpenDynaset)
rst.Filter = "[TaskOrder] = '" & mTaskOrder & "'"
rst.MoveLast
If rst.BOF Or rst.EOF Then
    GetRecordSetCount = 0
 Else
    GetRecordSetCount = rst.RecordCount
End If

Open in new window

Juan VelasquezAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
You are right. That's for forms.
You have to do it in another way:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstFiltered As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("qryShareAllocation", dbOpenDynaset)
rst.Filter = "[TaskOrder] = '" & mTaskOrder & "'"
set rstFiltered = rst.OpenRecordset
rstFiltered .MoveLast
If rstFiltered .BOF Or rstFiltered .EOF Then
    GetRecordSetCount = 0
 Else
    GetRecordSetCount = rstFiltered.RecordCount
End If 

Open in new window

/gustav
0
 
Gustav BrockCIOCommented:
You miss a

rst.FilterOn = True

/gustav
0
 
Juan VelasquezAuthor Commented:
Hello Cactus Data
I am using Access 97 and rst.filterOn is not defined
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Also ... you need to check for the existence of any records before doing a Move, otherwise an error will occur if there a zero records.  And you code can be simplified to this:

With CurrentDb.OpenRecordset("qryShareAllocation", dbOpenDynaset)
     .Filter = "[TaskOrder] = '" & mTaskOrder & "'"    
     .FilterOn = True                   ' per Gustav
     If .RecordCount = 0 then   ' test to see if you have records before executing a Move ...
        ' No Records
          GetRecordSetCount =0
        ' Exit or what ever
     Else
          .MoveLast
           GetRecordSetCount = .RecordCount
    End If
End With

mx
0
 
Jeffrey CoachmanMIS LiasonCommented:
Why not include the filter in the recordset?
Something roughly like this...

Set rst = db.OpenRecordset("SELECT * FROM YourTable WHERE [TaskOrder] = mTaskOrder", dbOpenDynaset)

Then the count should be correct, no?
Other expert comments...?


0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You still have to test for zero, then MoveLast to get the correct Count.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Oh ... but yes ... that should work also ...

mx
0
 
Juan VelasquezAuthor Commented:
Thanks everyone,
Below is the final solution,  I've incorporated the suggestion made by DatabaseMX to test for zero records into the solution proffered by cactus Data.  It's working fine now.  Thanks again.

Private Function GetRecordSetCount() As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstFiltered As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("qryShareAllocation", dbOpenDynaset)

rst.Filter = "[TaskOrder] = '" & mTaskOrder & "'"

Set rstFiltered = rst.OpenRecordset

If rstFiltered.RecordCount = 0 Or rstFiltered.BOF Or rstFiltered.EOF Then
    GetRecordSetCount = 0
   
Else
    rstFiltered.MoveLast
    GetRecordSetCount = rstFiltered.RecordCount
End If

End Function
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"forms" ... oops ... that's right ... you have to do the dual recordset thing.

mx
0
 
Gustav BrockCIOCommented:
I think you can reduce this:

If rstFiltered.RecordCount = 0 Or rstFiltered.BOF Or rstFiltered.EOF Then

to just:

If rstFiltered.EOF Then

/gustav
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Or stFiltered.RecordCount = 0

mx
0
 
Gustav BrockCIOCommented:
I guess you are right. If any records exists, RecordCount will return 1 or more.
If so:

If rstFiltered.RecordCount > 0 Then
    rstFiltered.MoveLast
End If
GetRecordSetCount = rstFiltered.RecordCount

/gustav
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well, according to Microsoft (some previous Help file), testing for zero (not even >0) is the most reliable way ... and I've never had an issue doing so.

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

All Courses

From novice to tech pro — start learning today.