Solved

recordset count not the correct value

Posted on 2011-03-10
13
317 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Add records to a form to a table 11 41
Update a text value in another table 10 42
IIF help, YN field 7 22
MS Access from Delphi 31 33
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
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…

832 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