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
Solved

Access DB recordcount

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
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…

839 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