Solved

Access DB recordcount

Posted on 2000-05-15
5
163 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA filters 2 72
Help me. 3 71
Excel VBA - Run Time error '1004' Application-defined or object-defined error 4 371
Dinamic report to Crosstab query 9 53
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

730 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