Solved

RecordsetClone recordcount erroneous in Access 97 application

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

770 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