Solved

Access DB recordcount

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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

772 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