Solved

recordset count not the correct value

Posted on 2011-03-10
13
321 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:chtullu135
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35096681
You miss a

rst.FilterOn = True

/gustav
0
 

Author Comment

by:chtullu135
ID: 35096757
Hello Cactus Data
I am using Access 97 and rst.filterOn is not defined
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 425 total points
ID: 35096822
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 75 total points
ID: 35096825
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35097056
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
 
LVL 75
ID: 35097084
You still have to test for zero, then MoveLast to get the correct Count.

mx
0
 
LVL 75
ID: 35097090
Oh ... but yes ... that should work also ...

mx
0
 

Author Closing Comment

by:chtullu135
ID: 35097225
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
 
LVL 75
ID: 35097672
"forms" ... oops ... that's right ... you have to do the dual recordset thing.

mx
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35106495
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
 
LVL 75
ID: 35109792
Or stFiltered.RecordCount = 0

mx
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35110467
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
 
LVL 75
ID: 35113127
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

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

630 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