Solved

RecordsetClone recordcount erroneous in Access 97 application

Posted on 2006-07-21
3
446 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

911 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

17 Experts available now in Live!

Get 1:1 Help Now