Solved

Access DB recordcount

Posted on 2000-05-15
5
159 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 142

Accepted Solution

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

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now