Improve company productivity with a Business Account.Sign Up

x
?
Solved

Count records in a recordset (VBA)

Posted on 2001-09-03
9
Medium Priority
?
707 Views
Last Modified: 2012-09-13
I have a recordset called rsMerge in my code and I want to get the number of records in that recordset so that I can do a test.

If the number of records is 1 the program flow will be different, and if more than 1 then the flow will be different.

I have tried using .MoveLast and .RecordCount but this is extremely slow, and I have 300,000 records to process.

I need something much faster! Please can anyone help?
0
Comment
Question by:naqayya
9 Comments
 
LVL 12

Expert Comment

by:Paurths
ID: 6450942
hi nagayya,

have u tried to only use rsMerge.Movenext ? (should work)


or else u could use the DCount-function:
var = DCount("SomeField","YourTable","SomeField = Criteria")

hope this helps,
Ricky
0
 

Author Comment

by:naqayya
ID: 6450995
thanks Ricky,

sorry, i don't understand how to use rsMerge.Movenext to count the number of records in rsMerge. Could you please explain?

Thanks.
0
 

Author Comment

by:naqayya
ID: 6451053
Ricky,

the DCount function does not work with recordsets, only tables or queries.

Here is the code i am using:

With rsMerge
                .MoveLast
               
                If .RecordCount = 1 Then
                    CurrentDb.Execute "INSERT INTO tblDonationAllocations_New VALUES (" & !DonationID & ",'" & !DonationType & "','" & !DonationDetails & "'," & !Amount & ",'" & !PayMethod & "','" & !AppealCode & "','" & !OnBehalfOf & "');"
                    CurrentDb.Execute "INSERT INTO tblDonationAllocations_Done VALUES (" & !Serial & ");"
                   
                ElseIf .RecordCount > 1 Then
                    .MoveFirst
                    SumAmount = 0
                    Do While Not .EOF
                        CurrentDb.Execute "INSERT INTO tblDonationAllocations_Done VALUES (" & !Serial & ");"
                        SumAmount = SumAmount + !Amount
                        .MoveNext
                    Loop
                    CurrentDb.Execute "INSERT INTO tblDonationAllocations_New VALUES (" & myDonationID & ",'" & myDonationType & "','" & myDonationDetails & "'," & SumAmount & ",'" & rsAllDonAlloc!PayMethod & "','" & rsAllDonAlloc!AppealCode & "','" & myOnBehalfOf & "');"
                End If
                .Close
            End With


Any more suggestions? Thanks.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
LVL 12

Accepted Solution

by:
Paurths earned 400 total points
ID: 6451067
hi ngayya,

yes, the dcount only works with tables and queries.
That is what i meant.

on the other hand, concerning the movenext:
replace :
.MoveLast
by
.MoveNext



With rsMerge
               .MoveNext
               
               If .RecordCount = 1 Then
                   CurrentDb.Execute "INSERT INTO tblDonationAllocations_New VALUES (" & !DonationID
& ",'" & !DonationType & "','" & !DonationDetails & "'," & !Amount & ",'" & !PayMethod & "','" & !AppealCode
& "','" & !OnBehalfOf & "');"
                   CurrentDb.Execute "INSERT INTO tblDonationAllocations_Done VALUES (" & !Serial & 
");"
                   
               ElseIf .RecordCount > 1 Then
                   .MoveFirst
                   SumAmount = 0
                   Do While Not .EOF
                       CurrentDb.Execute "INSERT INTO tblDonationAllocations_Done VALUES (" & !Serial
& ");"
                       SumAmount = SumAmount + !Amount
                       .MoveNext
                   Loop
                   CurrentDb.Execute "INSERT INTO tblDonationAllocations_New VALUES (" & myDonationID
& ",'" & myDonationType & "','" & myDonationDetails & "'," & SumAmount & ",'" & rsAllDonAlloc!PayMethod
& "','" & rsAllDonAlloc!AppealCode & "','" & myOnBehalfOf & "');"
               End If
               .Close
           End With


0
 
LVL 12

Expert Comment

by:Paurths
ID: 6451077
example for the DCount:

assuming the sql-statement for the rsMerge is like this:

set rsMerge= db.openrecordset("Select Field1, Field2 from YourTable where Field3 = 'YourCriteria';")


then u can use the dcount like this:

var = DCount("Field1","YourTable","Field3 = 'YourCriteria'")

var will now hold the value of the amount of records found.
0
 
LVL 9

Expert Comment

by:perove
ID: 6451087
..or you could check if there is on ore more record by
1 Go to first record
2.Go on record forward and siee if EOF=true




Function testing()
Dim rs As Recordset
Dim db As Database

Set db = CurrentDb
Set rs = db.OpenRecordset("table1")

If rs.BOF Then 'check if no records
    MsgBox "no records at all"
    Exit Function
End If

rs.MoveFirst 'go to frist
rs.MoveNext 'go to second
  If rs.EOF Then  'check if one record
     MsgBox "1 records only in this recordset"
     Exit Function

End If
 


End Function
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6451152
Hi all,

Just miss in the Q and comments the "empty" situation or no records at all.
I guess the code for filling the recordset is also like:
set rsMatch = dbcurrent.openrecordset("SELECT ... FROM ... WHERE X=" & intX & ";")

To speed thing up you are better off with defining a query like:
"SELECT ... FROM ... WHERE X= [parameter];"
This query can be "compiled" by access on forehand and will thus execute faster. You will need to specify the parameter in the code..

perove's .movenext is also faster as a movelast, but when the number can be one, then the total won't be that enormous too, that's why the slow response looks odd.
I guess the .movelast won't differ much as the prime selection will be the most time consuming. Jusrt use the single step mode to execute your code and you get a first impression where the time is consumed....

And check for EOF & BOF to make sure records are there.

Nic;o)
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6451174
yep,

movenext is faster than movelast,
as i commented in the first post

:-)
Ricky
0
 

Author Comment

by:naqayya
ID: 6451340
Solved the problem using .MoveNext and (mainly) setting indexes on the main table.

Thanks.
0

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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.

Join & Write a Comment

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

607 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