debug error on ".MoveFirst" statement

[CityGuy]

Hi,

I am a recent student of VBA in Access. Look to this web site alot
for help in many areas. I need help with a stange problem.

In the 'Tools | Reference ' settings I have selected the DAO 3.6 Library

In my code I use the following definition statements:
Dim db as DAO.Database
Dim CurrYr as DAO.Recordset

Later on in my code I do the following:
Set db = CurrentDb()
Set CurrYr = db.OpenRecordset("tblCurrentYear", dbOpenDynaset)
'default locking - pessimistic
'
With CurrYr
.MoveFirst <--- debug stops here, shows error message of:
'Object doesn't support this property of
method'

I know from the help menu that .MoveFirst is a DAO method.

- I have tried de-select the DAO 3.6 reference, then reconnecting it.
- I have tried de-selecting the DA0 3.6 referece, saving & compacting .mdb
file
then reconnecting DAO 3.6 reference, saving & compacting the .mdb file

I still get the same error. Help!!!

 

[Douglas J. Steele]

Is it possible that the recordset is empty? Check whether both .BOF and .EOF
are true.

Of course, there's really no reason for the MoveFirst. When you open a
recordset, it always opens at the first record.

 

[CityGuy]

The database in question has only 1 record in it at present.

 

[Dirk Goldgar]

Hi,

I am a recent student of VBA in Access. Look to this web site alot
for help in many areas. I need help with a stange problem.

In the 'Tools | Reference ' settings I have selected the DAO 3.6
Library

In my code I use the following definition statements:
Dim db as DAO.Database
Dim CurrYr as DAO.Recordset

Later on in my code I do the following:
Set db = CurrentDb()
Set CurrYr = db.OpenRecordset("tblCurrentYear", dbOpenDynaset)
'default locking - pessimistic
'
With CurrYr
.MoveFirst <--- debug stops here, shows error message of:
'Object doesn't support this property
of method'

I know from the help menu that .MoveFirst is a DAO method.

- I have tried de-select the DAO 3.6 reference, then reconnecting it.
- I have tried de-selecting the DA0 3.6 referece, saving & compacting
.mdb file
then reconnecting DAO 3.6 reference, saving & compacting the .mdb
file

I still get the same error. Help!!!

Is there something else named "CurrYr" in your database?

 

[Ken Higgins]

I wrote a little program to try to simulate your problem and came to the
conclusion that it is probably in the options, or lockedits portion of your
code that is causing
the problem. I cant believe its the DAO version you are using.

The help files are not very easy to understand with all of the "settings"
that you can optionally use. Try doing something very simple with the
openrecordset statement and get it to work first. Then you can better try the
more advanced setting combinations (if you really need them) --- less is
often better than more!

Here is a simple program to try (Oh by the way - if you try this program you
will also discover the frailty of the RECORDCOUNT property no matter how many
records you add IT works after the record is added - but not before
(because the record is added to the end of the recordset) But that was not
the problem you mentioned.

Public Sub test()

Dim db As DAO.Database
Dim currYr As DAO.Recordset
Dim vReturn As Variant
Dim lngCounter As Long

Set db = CurrentDb()
Set currYr = db.OpenRecordset("tblCurrentYear", dbOpenDynaset)
currYr.Close
With currYr

Debug.Print "BEFORE ADD: Recordset has: " & CStr(.RecordCount) & "
Records"
If .EOF Or .BOF Then
vReturn = MsgBox("Cant Move to an empty recordset" & _
vbCrLf & "Hit OK to Continue or CANCEL to Quit",
vbExclamation + vbOKCancel, _
"Ask User Something")
If vReturn = vbNo Then GoTo DoSomethingElse
End If
' .MoveFirst 'i commented this line out
.AddNew
!txtTestfld1 = "Record " & CStr(.RecordCount + 1)
.Update
Debug.Print "AFTER ADD: Recordset has " & CStr(.RecordCount) & "
Records"

.MoveFirst
End With

DoSomethingElse:
currYr.Close
Set currYr = Nothing
db.Close
Set db = Nothing
End Sub

 

[CityGuy]

No

Is there something else named "CurrYr" in your database?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[CityGuy]

I copied your subroutine into my code, but had to comment out
my original .MoveFirst statement to get the code to compile.

I noticed that if I tried to enter an new .MoveFirst command in my
original code, none of the .Move commands are available.

Your code though works FINE. (all .move commands are available)

What is going on?

 

[Dirk Goldgar]

No

Hmm. Are you executing this code in an MDB file or in an ADP?

If you'd like to send me a cut-down copy of your database, containing
only the elements necessary to demonstrate the problem, compacted and
then zipped to less than 1MB in size (preferably much smaller) -- I'll
have a look at it, time permitting. You can send it to the address
derived by removing NO SPAM from the reply address of this message. If
that address isn't visible to you, you can get it from my web site,
which is listed in my sig. Do *not* post my real address in the
newsgroup -- I don't want to be buried in spam and viruses.

 

[Ken Higgins]

Sorry I have been unavailable since your last post.
I took my original code off of the web site and it required some
substantial editing to make it work like i posted it.

Seems like the site chopped off some of the
line continuations. The movefirst is probably unnecessary since,
as Doug Steele says, it opens to the first record. He's right. So
perhaps the movefirst is not necessary.

With respect to your observation as to why the .movefirst
command seems to render "none of the
..Move commands are available" - I dont understand what is going on
there. But if the code works "fine" as you say -- then use that to get started
and be creative from there.

Hopefully by now, you have been able to work this through.
Come back if not.