[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

RecordsetClone recordcount erroneous in Access 97 application

Posted on 2006-07-21
3
Medium Priority
?
454 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 1000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses
Course of the Month18 days, 10 hours left to enroll

826 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