Need help getting RecordsetClone to work

[smurchie via AccessMonster.com]

I have a form with navigation buttons that I want to enable or disable
depending on what record the user is on. For example, if the user is on the
first record, the Previous button is disabled. I have written the code and
it works on another database I created but when I try to use the same code in
the current database I am working on I get a Run-time error '13' "Type
Mismatch" I have verified I have the same references enabled in the database
it works on and the database that I get the error with.
The line of code with the error is "Set recClone = Me.RecordsetClone()"
Here is the code:

Private Sub Form_Current()
Dim recClone As Recordset

'Make a clone of the recordset underlying the form so
'we can move around that without affecting the form's
'recordset

Set recClone = Me.RecordsetClone() This is the line highlighted when I debug.


'If we are in a new record, disable the <Next> button
'and enable the rest of the buttons
If Me.NewRecord Then
cmdFirst.Enabled = True
cmdPrevious.Enabled = True
cmdNext.Enabled = False
cmdLast.Enabled = True
cmdNew.Enabled = True
Exit Sub
End If

'If we reach here, we know we are not in a new record
'so we can enable the <New> button if the form allows
'new records to be added
cmdNew.Enabled = Me.AllowAdditions

'But we need to check if there are no records. If so,
'we disable all buttons except for the <New> button
If recClone.RecordCount = 0 Then
cmdFirst = False
cmdNext = False
cmdPrevious = False
cmdLast = False
Else

'If there are records, we know that the <First> and <Last> buttons will
always be enabled, irrespective
'of where we are in the recordset
cmdFirst.Enabled = True
cmdLast.Enabled = True

'Synchronise the current pointer in the two recordsets
recClone.Bookmark = Me.Bookmark

'Next, we must see if we are on the first record
'If so, we should disable the <Previous> button
recClone.MovePrevious
cmdPrevious.Enabled = Not (recClone.BOF)
recClone.MoveNext

'And then we should check whether we are on the last record
'If so, we should disable the <Next> button
recClone.MoveNext
cmdNext.Enabled = Not (recClone.EOF)
recClone.MovePrevious
End If

'And finally close the cloned recordset
recClone.Close


End Sub

Thanks,

 

[Scott Murchie via AccessMonster.com]

PS, I use both Access 2000 and 2002 versions and have the problem in both.

 

[John Nurick]

At a guess,
Dim recClone as Recordset
is defaulting to a DAO (or maybe ADO) recordset, while Me.Recordsetclone
is returning an ADO (or maby DAO) one.

I usually avoid the issue (in this particular situation) by declaring
Dim recClone As Object
; if you want early binding try an unambiguous declaration (e.g. As
DAO.Recordset).

 

[Scott Murchie via AccessMonster.com]

Much appreciated John. I just added DAO.RecordSet and it worked perfectly.
Thanks for your help.

 

[Rick Brandt]

I have a form with navigation buttons that I want to enable or disable
depending on what record the user is on. For example, if the user is on the
first record, the Previous button is disabled. I have written the code and
it works on another database I created but when I try to use the same code in
the current database I am working on I get a Run-time error '13' "Type
Mismatch" I have verified I have the same references enabled in the database
it works on and the database that I get the error with.
The line of code with the error is "Set recClone = Me.RecordsetClone()"
Here is the code:

Private Sub Form_Current()
Dim recClone As Recordset

'Make a clone of the recordset underlying the form so
'we can move around that without affecting the form's
'recordset

Set recClone = Me.RecordsetClone() This is the line highlighted when I debug.

Actually no need to even create a variable here. Just use...

With Me.RecordsetClone
If .RecordCount = 0 Then
cmdFirst = False
cmdNext = False
cmdPrevious = False
cmdLast = False
Else...
(more code)
End With

 

[David W. Fenton]

'And finally close the cloned recordset
recClone.Close

You can't close it, as you didn't open it.

You do need to set the variable to Nothing, though.

That said, I never use a variable for working with the
RecordsetClone -- all I ever do is:

With Me.RecordsetClone

End With

That doesn't require any initialization or cleanup.

 

[David W. Fenton]

Actually no need to even create a variable here. Just use...

With Me.RecordsetClone
If .RecordCount = 0 Then
cmdFirst = False
cmdNext = False
cmdPrevious = False
cmdLast = False
Else...
(more code)
End With

I'd probably do something like this:

Dim bolNotEmpty As Boolean
Dim bolNewRecord As Boolean

bolNotEmpty = (Me.RecordsetClone.RecordCount > 0)
bolNewRecord = Me.NewRecord

Me!cmdFirst.Enabled = bolNotEmpty
Me!cmdLast.Enabled = bolNotEmpty
Me!cmdNext.Enabled = bolNotEmpty And (Not bolNewRecord)
Me!cmbPrevious.Enable = bolNotEmpty

I'm not sure I agree that this is the best way to set the navigation
buttons, but it's the way the code was set up.