References problem with RecordsetClone

[Dennis Snelgrove]

I created a database in Access97 some time ago, and it's recently been
converted to Access2002(XP?). In it is a snippet of code that refers to the
RecordsetClone of a subform. Essentially what I was trying to do was have
the last 5 entries of a subform appear in the subform, since the users
(sigh...) demanded that the list be sorted in ascending chronological order
rather than in reverse order. It still works after conversion, but the first
time I either alter the form (frmMainSheet) or do a Compact/Repair on the
front end it gives me the following error:

Run-Time Error 2455:
You entered an expression that has an invalid reference to the property
Form/Report.

The Debug highlights this line - "If .Form.RecordsetClone.EOF = False Then".
I've checked the Object browser, and I can't find any properties or methods
for RecordsetClone, but it's never given me problems when it ran for over a
year and half in Access97. Has the RecordsetClone property been changed, or
are my references not translating properly from 97 to XP?

Thanks for any help offered. I'm at a loss at this point as to where to go
with this...



Following are the Library References that are in the front-end, and yes I do
want the Excel reference; I use an Excel function for some string
manipulation.

Visual Basic for Applications
Microsoft Access 10.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft Excel 10.0 Object Library
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft Office XP Web Components



With subfLogEntries
.Requery

If Len(.LinkMasterFields) > 0 Then
If .Form.RecordsetClone.EOF = False Then
.Form.RecordsetClone.MoveLast
.Form.RecordsetClone.MoveFirst
End If
If .Form.RecordsetClone.RecordCount > 5 Then
.Form.SelTop = .Form.RecordsetClone.RecordCount - 4
Else
.Form.SelTop = 1
End If
End If
End With

 

[Dennis Snelgrove]

I tried this, but it's still giving me the same error. My interpretation of
the error is this: it seems as though somehow any of the properties and
methods of the RecordsetClone property aren't able to be seen by the VBA
editor. When I checked the Object browser, I couldn't see any properties or
methods for RecordsetClone. Maybe I'm misunderstanding something, but for
the Class "Form", there's a member called "Recordset". Then, "Recordset" has
a number of members in it as well. Shouldn't the same be true of
RecordsetClone? It was in Access97, or my coding wouldn't have worked back
then. Do you see my confusion?

Thanks for your time and help, btw. I really do appreciate it...

 

[Bruce M. Thompson]

I created a database in Access97 some time ago, and it's recently been
converted to Access2002(XP?). In it is a snippet of code that refers to the
RecordsetClone of a subform. Essentially what I was trying to do was have
the last 5 entries of a subform appear in the subform, since the users
(sigh...) demanded that the list be sorted in ascending chronological order
rather than in reverse order. It still works after conversion, but the first
time I either alter the form (frmMainSheet) or do a Compact/Repair on the
front end it gives me the following error:

.... <snip>

If this is a fairly large application, you may be suffering from some VBA
corruption. You might consider implementing a Decompile/Recompile of your VBA
code, using the following set of steps (including those leading up to the
decompile instructions, for diagnostic purposes):

Compact the file, then open the Debug/Code window (Ctl+G) and goto the menu
"Debug" and, depending on which version of Access you are using, there will be
an item "Compile and Save All" or "Compile (Name Of Your Database)". In the
latter case, save the project after compiling. If you don't encounter any errors
and the problem still exists then check the "References" in that file. For
information on that, see
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html and
http://users.bigpond.net.au/abrowne1/ser-38.html.

If the previous steps have not helped, then you might have some corruption in
the VBA code. There is a "decompile" switch (available in Access 97, 2000 and,
apparently, 2002) that can be used to help correct this situation, but this is
more of a "last resort" in that there is some risk to the file as described in
http://www.trigeminal.com/usenet/usenet004.asp?1033 (so ... work with a copy of
the file). Further information is available at
http://www.databasecreations.com/largedb.htm. The procedure I use when
implementing this is as follows:

1) BACK UP YOUR MDB FILE!
1) BACK UP YOUR MDB FILE! (I meant it the first time <g>)
2) Compact the MDB.
3) Implement the "/decompile" as described in the articles I referenced.
(Access 2000, and later, don't provide the confirmation dialog that
existed in Access 97, but the decompile will still take place.)
4) Open Access normally and compact the MDB again to clean up.
5) Compile and save.
6) Compact again before testing/using.

 

[Tim Ferguson]

The Debug highlights this line - "If .Form.RecordsetClone.EOF = False
Then".

If the great CurrentDB() saga taught us nothing else, it was that functions
that return objects don't always behave like the objects themselves.

I would set a new variable to the RecordsetClone property for the whole
section of code and this would (a) point to which property is failing, (b)
make sure that the same object is being manipulated by all these MoveFirst
and MoveLast things, and (c) speed it up too.

I confess that I don't have Access 10 so I haven't tested this:-

Eg

dim rs as Recordset

....



subfLogEntries.Requery
If Len(subfLogEntries.LinkMasterFields) > 0 Then
'
Set rs = subfLogEntries.Form.RecordsetClone

If rs.EOF = False Then
rs.MoveLast
rs.MoveFirst

End If

subfLogEntries.Form.SelTop = IIf( _
rs.RecordCount > 5, _
rs.RecordCount - 4, _
1 )


HTH


Tim F

 

[Dennis Snelgrove]

This was the problem. It needed to be decompiled and recompiled. You know
that I'll be keeping copies of all this info for any future problems.

Thank you to Bruce, Tim, Van, and Dirk for all your time and help. You guys
are the best!