RecordsetClone causing Error

[access83]

Hi All,

I'm trying to clone the recordset that is the recordsource of my form so I can determine if I've reached the last record in the recordset as I move through it with a button on the form.

My current code is...

Dim rs As ADODB.Recordset
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
If Not (rs.AbsolutePosition + 1 = rs.RecordCount) Then
   DoCmd.GoToRecord , , acNext
End If

I put this on the onClick property of the 'Next' button that will move to the next record. However, I keep getting the error
"Data Provider could not be initialized" on Set rs = Me.RecordsetClone

Does anyone know why this is happening?

Thanks in advance

 

[vbaInet]

What is the Record Source of your form? I suspect it's a table.

What are you trying to achieve with the Absolute position line of code?

 

[access83]

Hi,

The recordsource of my form is a recordset. This recordset is populated with records returned from a sql server stored procedure, so records from a sql server table.

The AbsolutionPosition returns the numeric position of the current record in the recordset. So what I am doing in the If statement is saying if the next record's position (current record + 1) is not equal to the total number of records in the recordset then move to the next record. What I am trying to do is navigate through the records in the recordset using 'Next' and 'Previous' buttons so once the next record is equal to the total number of records in the recordset it will not move to the next record and not throw an error.

Thanks

 

[MSAccessRookie]

Hi,

The recordsource of my form is a recordset. This recordset is populated with records returned from a sql server stored procedure, so records from a sql server table.

The AbsolutionPosition returns the numeric position of the current record in the recordset. So what I am doing in the If statement is saying if the next record's position (current record + 1) is not equal to the total number of records in the recordset then move to the next record. What I am trying to do is navigate through the records in the recordset using 'Next' and 'Previous' buttons so once the next record is equal to the total number of records in the recordset it will not move to the next record and not throw an error.

Thanks

Can't rs.EOF be used for a similar purpose?

 

[vbaInet]

You're going about it the wrong way like MSAccessRookie has highlighted. Here's a link on how to work with recordsets:

http://allenbrowne.com/ser-29.html

Try a DAO recordset.