?
Solved

Access DB recordcount

Posted on 2000-05-15
5
Medium Priority
?
173 Views
Last Modified: 2010-05-02
Hi,

I have a table in an Access DB which has several records with the same value in a number field (called DIN Number). I have a query to get all the records with a specified DIN number. In Access I open the query type a number (eg 5) and it returns all the records with DIN number = 5. In this case (6 records).

When I open the query from my VB 5.0 program the max number of records it will get is 2. So I pass the parameter input, DIN number = 5, knowing there are 6 records and the record.count property = 2. the data for the first two records in consistent with the first two records of the six opened in access.

This happens for all DIN numbers, if there is more than 2 records it will only count two.

Why is this and how do I fix it?

VB code follows:


On Error Resume Next
rstDINsDetails.MoveNext
rstDINsDetails.MoveFirst
For i = 1 To rstDINsDetails.RecordCount

   String1 = rstDINsDetails.Fields("Ref Number")
    string 2= format(rstDINsDetails.RecordCount)
' not exact code in loop, actual code is too large

rstDINsDetails.MoveNext
Next i

Thanks in advance,

Andy
0
Comment
Question by:andyknott
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 2810258
replace .movenext
by .movelast
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2810264
in fact, your .movenext will read record 2, whereas .movelast will read all the records.

Other option may be to have you recordset on client side (which will read all the records by default)

Hope this helps
0
 
LVL 1

Expert Comment

by:jgravelle
ID: 2810549
In order to get an accurate record count you need to move to the last record first then do your recordcount. Otherwise you could put a counter field in your query so it will return aa count as the query runs. The reason you may want to do this is because using the .MoveLast can cause significant performance loss in your app. Imagine if your table contained 50,000 records you would have to move to the last everytime you wanted a recordcount.
0
 
LVL 12

Expert Comment

by:mark2150
ID: 2810766
Simpler and easier is to not use record count at all. Change your loop to:

Do while not RS.EOF
  ...
  RS.MoveNext
Loop

Logic is clearer than FOR loop and you use fewer vars.

M
0
 

Author Comment

by:andyknott
ID: 2811036
Hi everyone,

Doh! I meant to put movelast not next! how did I miss that? Well spotted and thanks for the tips and other methods.

I excepted angelIII's comment as an answer a while ago, I don't know why it didn't work, http error? Sorry to waste your time and thanks anyway.

Andy

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

829 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