Solved

RecordsetClone recordcount erroneous in Access 97 application

Posted on 2006-07-21
3
449 Views
Last Modified: 2008-02-01
We have an Access 97 application running on Windows 95 that is hanging.  It has both the front end application and a rear-end database.

To diagnose what was going on, I used an access database that I had written and used before to set the startup settings to default, so that, when opened, the database window opens without any screens.  Running the application under Access 97/Win 95 in debug mode, the program erred when attempting to obtain a recordcount from the recordsetclone of the screen being used.  Although the recordsource table only contained 3361 records, values for recordsetclone.recordcount were in the millions and different each time the value was queried in the debug window (?me.recordsetclone.recordcount).  When this same maneuver was repeated in a copy of the application upgraded and running under Access 2002/Windows XP professional, the same recordcount, when queried in debug, was a repeatable 3361.

This error is rendering this critical application unusable.  Any ideas as to what is happening here?

sjl
0
Comment
Question by:sjlevine34
  • 2
3 Comments
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 250 total points
ID: 17159047
sjlevine,
you did not post your code, but there are some known mistakes made with recordset/clone record counts.  Allen Browne (see link) advises that you need to do a MoveLast before extracting the count, but you shouldn't do a movelast unless you absolutely need the count....otherwise you can do a movefirst after checking for eof/bof.

sample syntax for retrieving the count after a movelast, if the count is required:

Private Sub Form_Load()

    Dim x As Integer, rs As DAO.RecordSet

    Set rs = Me.RecordsetClone
    rs.MoveLast
    x = rs.RecordCount


                           http://www.allenbrowne.com/ser-29.html

another problem I have heard about involves improper counts from recordsets based on linked forms/subforms.  Here is one solution that was offered by Trevor Best, MvP:

In the main form:
Private Sub Form_Current()
Dim rs As Recordset
Set rs = MySubForm.Form.RecordsetClone
If rs.RecordCount Then
rs.MoveLast
MySubForm.Form.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing
End Sub


Hope this helps.
0
 
LVL 1

Author Comment

by:sjlevine34
ID: 17161114
In this case, repairing the backend database seems to have fixed the problem.  I could not do it until now because it was a colleague's database (he had been on vacation when the problem occurred) and I did not know the database password.  Once we got in touch, he gave me the password and I was able to effect repairs.

sjl
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17161131
Glad you got your problem resolved.  Thanks for points and grade.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

808 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