Object Variable Error

[alex]

Object Variable Error

Hello,
Using Access ’03…

On my Form, I have a label and command buttons that function like the
typical navigation buttons at the bottom of a form (mine are at the
top and a little more user-friendly).

There’s an issue with the code, however; which usually works rather
well.

When the form experiences a run-time error of any kind, I get a run-
time error (91) where I set Records.Bookmark = Me.Bookmark.

I’m guessing that once an error occurs, my recordset is being
dropped. All I have to do is open the form and everything works just
fine. Do I need to set a reference to a DAO database?

Here’s some of the code:

Option Compare Database
Option Explicit
Dim Records As DAO.Recordset
Dim TotalRecords

Private Sub Form_Load() 'used to count records
On Error Resume Next 'records.movelast will throw error if no records
exist
Set Records = Me.RecordsetClone
Records.MoveLast
TotalRecords = Records.RecordCount
End Sub

Private Sub Form_Current()
'code navigation buttons
If Not Me.NewRecord Then 'is not a new record
Records.Bookmark = Me.Bookmark ‘error occurs here after any run-
time error
Me![RecNum].Caption = "Record " & Records.AbsolutePosition + 1 & "
of " & TotalRecords
Me.cmdNextRecord.Enabled = True 'enable next record arrow
'Debug.Print Records.AbsolutePosition
If Records.AbsolutePosition = 0 Then 'first record
Me.cmdPreviousRecord.Enabled = False 'disable previous record
arrow
Else
Me.cmdPreviousRecord.Enabled = True 'enable previous record
arrow
End If
Else
Me![RecNum].Caption = "New Record"
Me.cmdNextRecord.Enabled = False 'disable next record arrow
Me.cmdPreviousRecord.Enabled = True 'enable previous record arrow
End If
End Sub

Thanks for the help,
alex

 

[Dorian]

I think your problem is the 'on error resume next'. This is almost never a
good idea.
You need an error handling procedure to deal with errors.
If the error is expected, you can test for it and take action (and maybe
resume). Only unexpected errors should terminate your application and you
should then display where the error ocurred and what the error was (number
and description).
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[alex]

I think your problem is the 'on error resume next'. This is almost never a
good idea.
You need an error handling procedure to deal with errors.
If the error is expected, you can test for it and take action (and maybe
resume). Only unexpected errors should terminate your application and you
should then display where the error ocurred and what the error was (number
and description).
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

Object Variable Error

Hello,
Using Access ’03…

On my Form, I have a label and command buttons that function like the
typical navigation buttons at the bottom of a form (mine are at the
top and a little more user-friendly).

There’s an issue with the code, however; which usually works rather
well.

When the form experiences a run-time error of any kind, I get a run-
time error (91) where I set Records.Bookmark = Me.Bookmark.

I’m guessing that once an error occurs, my recordset is being
dropped.  All I have to do is open the form and everything works just
fine.  Do I need to set a reference to a DAO database?

Here’s some of the code:

Option Compare Database
Option Explicit
Dim Records As DAO.Recordset
Dim TotalRecords

Private Sub Form_Load() 'used to count records
On Error Resume Next 'records.movelast will throw error if no records
exist
Set Records = Me.RecordsetClone
Records.MoveLast
TotalRecords = Records.RecordCount
End Sub

Private Sub Form_Current()
'code navigation buttons
If Not Me.NewRecord Then 'is not a new record
    Records.Bookmark = Me.Bookmark ‘error occurs here after anyrun-
time error
    Me![RecNum].Caption = "Record " & Records.AbsolutePosition + 1 & "
of " & TotalRecords
    Me.cmdNextRecord.Enabled = True 'enable next record arrow
    'Debug.Print Records.AbsolutePosition
    If Records.AbsolutePosition = 0 Then 'first record
        Me.cmdPreviousRecord.Enabled = False 'disable previous record
arrow
    Else
        Me.cmdPreviousRecord.Enabled = True 'enable previous record
arrow
    End If
Else
    Me![RecNum].Caption = "New Record"
    Me.cmdNextRecord.Enabled = False 'disable next record arrow
    Me.cmdPreviousRecord.Enabled = True 'enable previous record arrow
End If
End Sub

Thanks for the help,
alex
.- Hide quoted text -

- Show quoted text -

Dorian,
Thanks for the response...
I agree with your assessment of error handling (lack of) but that
doesn't seem to be the problem. I still think my recordset is being
dropped.
alex

 

[Dirk Goldgar]

If you have an unhandled error, your VB project gets reset, and that
includes resetting any variables such as your module-level recordset. I
don't think you need that variable, though. In Access 2003, a new clone is
not created each time you refer to .RecordsetClone, so you ought to be able
to make out fine with code like this:

'----- start of revised code ------
Option Compare Database
Option Explicit

Private Sub Form_Load() 'used to count records

Me.RecordsetClone.MoveLast

End Sub

Private Sub Form_Current()

'code navigation buttons
If Not Me.NewRecord Then 'is not a new record
Me![Recnum].Caption = _
"Record " & Me.CurrentRecord & " of _
" & Me.RecordsetClone.RecordCount
Me.cmdNextRecord.Enabled = True
If Me.CurrentRecord = 1 Then 'first record
Me.cmdPreviousRecord.Enabled = False
Else
Me.cmdPreviousRecord.Enabled = True End If
Else
Me![Recnum].Caption = "New Record"
Me.cmdNextRecord.Enabled = False
Me.cmdPreviousRecord.Enabled = True
End If

End Sub
'----- end of revised code ------

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)


Object Variable Error

Hello,
Using Access ’03…

On my Form, I have a label and command buttons that function like the
typical navigation buttons at the bottom of a form (mine are at the
top and a little more user-friendly).

There’s an issue with the code, however; which usually works rather
well.

When the form experiences a run-time error of any kind, I get a run-
time error (91) where I set Records.Bookmark = Me.Bookmark.

I’m guessing that once an error occurs, my recordset is being
dropped. All I have to do is open the form and everything works just
fine. Do I need to set a reference to a DAO database?

Here’s some of the code:

Option Compare Database
Option Explicit
Dim Records As DAO.Recordset
Dim TotalRecords

Private Sub Form_Load() 'used to count records
On Error Resume Next 'records.movelast will throw error if no records
exist
Set Records = Me.RecordsetClone
Records.MoveLast
TotalRecords = Records.RecordCount
End Sub

Private Sub Form_Current()
'code navigation buttons
If Not Me.NewRecord Then 'is not a new record
Records.Bookmark = Me.Bookmark ‘error occurs here after any run-
time error
Me![RecNum].Caption = "Record " & Records.AbsolutePosition + 1 & "
of " & TotalRecords
Me.cmdNextRecord.Enabled = True 'enable next record arrow
'Debug.Print Records.AbsolutePosition
If Records.AbsolutePosition = 0 Then 'first record
Me.cmdPreviousRecord.Enabled = False 'disable previous record
arrow
Else
Me.cmdPreviousRecord.Enabled = True 'enable previous record
arrow
End If
Else
Me![RecNum].Caption = "New Record"
Me.cmdNextRecord.Enabled = False 'disable next record arrow
Me.cmdPreviousRecord.Enabled = True 'enable previous record arrow
End If
End Sub

Thanks for the help,
alex

 

[alex]

If you have an unhandled error, your VB project gets reset, and that
includes resetting any variables such as your module-level recordset.  I
don't think you need that variable, though.  In Access 2003, a new clone is
not created each time you refer to .RecordsetClone, so you ought to be able
to make out fine with code like this:

'----- start of revised code ------
Option Compare Database
Option Explicit

Private Sub Form_Load() 'used to count records

    Me.RecordsetClone.MoveLast

End Sub

Private Sub Form_Current()

    'code navigation buttons
    If Not Me.NewRecord Then 'is not a new record
        Me![Recnum].Caption = _
            "Record " & Me.CurrentRecord & " of _
            " & Me.RecordsetClone.RecordCount
        Me.cmdNextRecord.Enabled = True
        If Me.CurrentRecord = 1 Then 'first record
            Me.cmdPreviousRecord.Enabled = False
        Else
            Me.cmdPreviousRecord.Enabled = True       End If
    Else
        Me![Recnum].Caption = "New Record"
        Me.cmdNextRecord.Enabled = False
        Me.cmdPreviousRecord.Enabled = True
    End If

End Sub
'----- end of revised code ------

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)


Object Variable Error

Hello,
Using Access ’03…

On my Form, I have a label and command buttons that function like the
typical navigation buttons at the bottom of a form (mine are at the
top and a little more user-friendly).

There’s an issue with the code, however; which usually works rather
well.

When the form experiences a run-time error of any kind, I get a run-
time error (91) where I set Records.Bookmark = Me.Bookmark.

I’m guessing that once an error occurs, my recordset is being
dropped.  All I have to do is open the form and everything works just
fine.  Do I need to set a reference to a DAO database?

Here’s some of the code:

Option Compare Database
Option Explicit
Dim Records As DAO.Recordset
Dim TotalRecords

Private Sub Form_Load() 'used to count records
On Error Resume Next 'records.movelast will throw error if no records
exist
Set Records = Me.RecordsetClone
Records.MoveLast
TotalRecords = Records.RecordCount
End Sub

Private Sub Form_Current()
'code navigation buttons
If Not Me.NewRecord Then 'is not a new record
    Records.Bookmark = Me.Bookmark ‘error occurs here after any run-
time error
    Me![RecNum].Caption = "Record " & Records.AbsolutePosition + 1 & "
of " & TotalRecords
    Me.cmdNextRecord.Enabled = True 'enable next record arrow
    'Debug.Print Records.AbsolutePosition
    If Records.AbsolutePosition = 0 Then 'first record
        Me.cmdPreviousRecord.Enabled = False 'disable previous record
arrow
    Else
        Me.cmdPreviousRecord.Enabled = True 'enable previous record
arrow
    End If
Else
    Me![RecNum].Caption = "New Record"
    Me.cmdNextRecord.Enabled = False 'disable next record arrow
    Me.cmdPreviousRecord.Enabled = True 'enable previous record arrow
End If
End Sub

Thanks for the help,
alex

Dirk,
You were right (not surprisingly).
I appreciate the help. As a note, I'm assuming that there's a
difference between a DAO recordset and Me.Recordset...
A different reference I presume?
alex

 

[Dirk Goldgar]

As a note, I'm assuming that there's a difference between a DAO recordset
and Me.Recordset... A different reference I presume?

A DAO recordset is a type of object. Me.Recordset is a reference to the
form's own recordset, which -- unless you're working in an Access Data
Project (ADP) -- is by default a DAO recordset. In an ADP, the form's
recordset is by default an ADODB recordset, which is a slightly different
type of recordset object.

Since (at least) Access 95, the only way programmers could work with the
same set of data the form was using was by way of the form's RecordsetClone
property, which would (if needed) make a clone of the form's recordset and
pass a reference to that clone back to the caller. The clone is essentially
an in-memory copy of the form's recordset, such that bookmarks are
compatible between the RecordsetClone, the form's original recordset, and
the form's Bookmark property. However, operations such as record navigation
that are carried out on the clone aren't refelected on the form. Data
changes, of course, are, because it's the same data.

Since Access 2000, programmers have had also been able to work with the
form's recordset directly by way of the form's .Recordset property. When
you operate on the form's recordset directly, not on a clone, then all
operations on the recordset are immediately reflected on the form itself,
because this is the way forms are wired up. That means that some of the
code that you used to have to use, involving navigating in a form's
..RecordsetClone and then synchronizing bookmarks between the recordsetclone
and the form, can now be simplified to navigating the the form's .Recordset
itself -- the form is automatically sunchronized with the recordset.

On the other hand, the RecordsetClone is still very useful, *because* you
can navigate in it without affecting the form. For example, in the code I
posted earlier, I used the statement "Me.RecordsetClone.MoveLast" to force
the recordset to count records without positioning the form to the last
record.

 

[De Jager]

Object Variable Error

Hello,
Using Access ’03…

On my Form, I have a label and command buttons that function like the
typical navigation buttons at the bottom of a form (mine are at the
top and a little more user-friendly).

There’s an issue with the code, however; which usually works rather
well.

When the form experiences a run-time error of any kind, I get a run-
time error (91) where I set Records.Bookmark = Me.Bookmark.

I’m guessing that once an error occurs, my recordset is being
dropped. All I have to do is open the form and everything works just
fine. Do I need to set a reference to a DAO database?

Here’s some of the code:

Option Compare Database
Option Explicit
Dim Records As DAO.Recordset
Dim TotalRecords

Private Sub Form_Load() 'used to count records
On Error Resume Next 'records.movelast will throw error if no records
exist
Set Records = Me.RecordsetClone
Records.MoveLast
TotalRecords = Records.RecordCount
End Sub

Private Sub Form_Current()
'code navigation buttons
If Not Me.NewRecord Then 'is not a new record
Records.Bookmark = Me.Bookmark ‘error occurs here after any run-
time error
Me![RecNum].Caption = "Record " & Records.AbsolutePosition + 1 & "
of " & TotalRecords
Me.cmdNextRecord.Enabled = True 'enable next record arrow
'Debug.Print Records.AbsolutePosition
If Records.AbsolutePosition = 0 Then 'first record
Me.cmdPreviousRecord.Enabled = False 'disable previous record
arrow
Else
Me.cmdPreviousRecord.Enabled = True 'enable previous record
arrow
End If
Else
Me![RecNum].Caption = "New Record"
Me.cmdNextRecord.Enabled = False 'disable next record arrow
Me.cmdPreviousRecord.Enabled = True 'enable previous record arrow
End If
End Sub

Thanks for the help,
alex

 

[joelgeraldine]

ll:!!!!!