Solved

recordset count not the correct value

Posted on 2011-03-10
13
320 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 50

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 50

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 50

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 50

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

734 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