Navigating using Prev and Next

[dimitri]

Hello,

On Form Load I get the data using the recordset

Private Sub Form_Load()

Dim rsErrors As DAO.Recordset
Set rsErrors = CurrentDb.OpenRecordset(" SELECT * FROM EMPLOYEES ")
--- code here to load the first record in the text box --

End Sub

Now to navigate the records, there is a Prev and Next button

This is the code for Next Click

Private Sub cmdNext_Click()
On Error GoTo Err_cmdNext_Click
Dim mvrs As Variant
Set mvrs = Me.subIndex.Form.RecordsetClone
mvrs.Bookmark = Me.subIndex.Form.Bookmark
mvrs.MoveNext
Me.subIndex.Form.Bookmark = mvrs.Bookmark
mvrs.Close
Set mvrs = Nothing

Exit_cmdNext_Click:
Exit Sub
Err_cmdNext_Click:
If Err.Number <> 3021 Then
MsgBox Err.Description
Resume Exit_cmdNext_Click
End If
End Sub

Getting an Invalid reference to the RecordsetClone property.

Could you please help me in Navigating Prev and Next records. If there is a better way please do let me know. Thanks

 

[dimitri]

Sorry correction on the Next button Click, subINdex is removed.

Dim mvrs As Variant
Set mvrs = Me.Form.RecordsetClone
mvrs.Bookmark = Me.Form.Bookmark
mvrs.MoveNext
Me.Form.Bookmark = mvrs.Bookmark
mvrs.Close
Set mvrs = Nothing

 

[boblarson]

Why are you using the recordset for this? There is no reason to.

If the button is on the form you are moving to the next record with the code it is just

DoCmd.RunCommand acCmdRecordsGoToNext

And if you have the button on the parent form and want to control the subform you can just use

Me.SubformControlNameHere.Form.Recordset.MoveNext

Where SubformControlNameHere is the name of the control on the parent form that HOUSES/DISPLAYS the subform and not the subform name itself unless the subform and the subform control are named exactly the same as each other.

 

[dimitri]

Bob,

Thanks for the reply,

The scenario is when the form loads, I run a query to get the list of records and display the First records value. I use the recordset for this operation.

After that to traverse the records there are Next and Previous buttons,

I used the above command "DoCmd.RunCommand acCmdRecordsGoToNext" in the click event of Next and getting the error "The command or action 'RecordsGoToNext' isn't available now.

 

[dimitri]

The button is on the same form

 

[boblarson]

Bob,

Thanks for the reply,

The scenario is when the form loads, I run a query to get the list of records and display the First records value. I use the recordset for this operation.

After that to traverse the records there are Next and Previous buttons,

I used the above command "DoCmd.RunCommand acCmdRecordsGoToNext" in the click event of Next and getting the error "The command or action 'RecordsGoToNext' isn't available now.

Are you binding the form to the query?

 

[dimitri]

No, the query is constructed in the Form_Load by using a time period obtained from Previous form..

 

[boblarson]

So please pardon me for asking, because I like to find out why someone does what they do especially when it is outside the norm, why are you doing it this way instead of binding the form to the query? What is the purpose of taking what Access does for you and going down the road of having to write code to do what Access will already do for you?

 

[boblarson]

But outside of my questioning why you are going down this path, the problem is that you have the recordset declaration inside the On Load event. You need it either at form level or at global level (in a standard module).

So move this:

Dim rsErrors As DAO.Recordset

to the General Declarations section of the form.

Then don't use mvrs because if you aren't binding the form to the query then there is NO RECORDSET so there is NO RECORDSET CLONE to use.

 

[dimitri]

Bob, binding the form to the query worked.. Well I am new to Access programming and did not know this could be achieved in this manner. Hence in my first post requested if there was a better approach.

One quick clarification, there is a combobox in the form where the item is being added programatically. That is working fine, but when I select the item in the combo box and click on next record, the same item in the combo box is displayed. I have used this to remove the items

Do While Me.ddlItems.ListCount > 0
Me.ddlItems.RemoveItem (0)
Loop

The item gets removed but when the next button is clicked the same item is still displayed.

Your help is greatly appreciated.

Thanks

 

[boblarson]

So you have a combo box with a row source type as VALUE LIST? And you are populating it how? Are you populating it with code? Or have you entered the values in to the Row Source property when in design view?

 

[dimitri]

Clarifying, when the next button is clicked, the next items ( item b ) text is displayed in the combo box but the previous items ( item a ) text is shown selected and when I select the ( item b ) data, item a is not in the combo box.

I hope this explanation makes sense.

 

[dimitri]

Row Source type is set as Value List and the items are added in the code behind

 

[boblarson]

Try requerying the combo after the delete:

Do While Me.ddlItems.ListCount > 0
Me.ddlItems.RemoveItem (0)
Loop
Me.ddlItems.Requery

 

[dimitri]

Bob,

I had tried ReQuery and it did not work bu I tried SetFocus and it worked. When Next button is clicked, the New item is getting displayed correctly.

Thanks a lot, the issues have been resolved