Navigate a Recordset With a Userform – Part IV

The grand finale! In Part I, we set up the userform. Part II was special purpose procedures to make our coding a little easier down the line. Then in Part III, we connected to the Recordset. Now, we’ll code the four CommandButtons that will allow the user to navigate through the Recordset.

I told you this would be easy, and I wasn’t kidding. There are basically two kinds of buttons from a code perspective; buttons that take you to the extremes of the Recordset, and buttons that move one record at a time. All four buttons do three things; make the correct record the current record, call the FillTextBoxes sub, and call the DisableButtons sub. The “move one record” buttons have one additional step. Namely, to determine which record is current. If it’s the first or last record, we need different buttons disabled.

Private Sub cmdFirst_Click()

    mADORs.MoveFirst
    FillTextBoxes
    
    DisableButtons “ButtonFirst”, “ButtonPrev”
    
End Sub

Private Sub cmdLast_Click()

    mADORs.MoveLast
    FillTextBoxes
    
    DisableButtons “ButtonLast”, “ButtonNext”
    
End Sub

Private Sub cmdNext_Click()

    mADORs.MoveNext
    FillTextBoxes
    
    If mADORs.AbsolutePosition = mADORs.RecordCount Then
        DisableButtons “ButtonLast”, “ButtonNext”
    Else
        DisableButtons
    End If
    
End Sub

Private Sub cmdPrev_Click()

    mADORs.MovePrevious
    FillTextBoxes
    
    If mADORs.AbsolutePosition = 1 Then
        DisableButtons “ButtonFirst”, “ButtonPrev”
    Else
        DisableButtons
    End If
    
End Sub

Private Sub cmdFirst_Click()

mADORs.MoveFirst

FillTextBoxes

DisableButtons “ButtonFirst”, “ButtonPrev”

End Sub

Private Sub cmdLast_Click()

mADORs.MoveLast

FillTextBoxes

DisableButtons “ButtonLast”, “ButtonNext”

End Sub

Private Sub cmdNext_Click()

mADORs.MoveNext

FillTextBoxes

If mADORs.AbsolutePosition = mADORs.RecordCount Then

DisableButtons “ButtonLast”, “ButtonNext”

Else

DisableButtons

End If

End Sub

Private Sub cmdPrev_Click()

mADORs.MovePrevious

FillTextBoxes

If mADORs.AbsolutePosition = 1 Then

DisableButtons “ButtonFirst”, “ButtonPrev”

Else

DisableButtons

End If

End Sub

The methods MoveNext, MoveFirst, MovePrevious, and MoveLast are methods of the Recordset object. They couldn’t have made it much easier for us. The rest is just calling our procedures that do all the work.

You can download the file ADOInUserform.xls to see how it all fits together.

5 thoughts on “Navigate a Recordset With a Userform – Part IV”

Hello, looking for examples I came across this yours, but since I do not speak much English I find it a bit difficult to follow the example, your download link does not work, could you send me the file to my email please? Thank you.

Atte.
Carlos Soto
caso601@yahoo.com

The link is fixed.

Thank you very much, Dick Kusleika… it took me quite a while to find this and I’m sure it will save me a lot of time. I’ve programmed quite a lot in Excel VBA (building actuarial tools) but without much ADODB experience where data has to be written back to the database – usually I’ve just had to fetch data from a database, analyse it, and use the results.
John