Solved

recordset count not the correct value

Posted on 2011-03-10
13
318 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
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 49

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 49

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 49

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 49

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

809 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