ExcelBanter - Display first, next, previous on userform from recordset

Dennis

I've included the entire userform module below. If you want me to send you
the workbook, send me a private email and I will reply with the workbook
attached.

My recordset has five fields and my userform has five textboxes. The
textboxes have tags like Field0, Field1, etc. to tell it which fields go in
which textboxes. I use a separate sub FillTextBoxes to populate them
whenever they need updating. Then I have four command buttons to go First,
Last, Previous and Next. I use the Tag property of these like ButtonFirst,
ButtonNext, etc. The sub DisableButtons is used whenever the record is
changed. I pass the Tags that I want to disable to the sub and it enables
everything but those buttons.

Here's all the code behind the userform:

Option Explicit

Dim mADOCon As ADODB.Connection
Dim mADORs As ADODB.Recordset

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 UserForm_Initialize()

Dim sConn As String
Dim sSQL As String

sConn = "DSN=MS Access Database;"
sConn = sConn & "DBQ=C:\Program Files\Microsoft
Office\Office\Samples\Northwind.mdb;"
sConn = sConn & "DefaultDir=C:\Program Files\Microsoft
Office\Office\Samples;"
sConn = sConn & "DriverId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;"

sSQL = "SELECT Employees.EmployeeID, Employees.LastName, "
sSQL = sSQL & "Employees.FirstName, Employees.BirthDate,
Employees.HireDate "
sSQL = sSQL & "FROM `C:\Program Files\Microsoft Office\"
sSQL = sSQL & "Office\Samples\Northwind`.Employees Employees"

Set mADOCon = New ADODB.Connection
Set mADORs = New ADODB.Recordset
mADORs.CursorLocation = adUseClient

mADOCon.Open sConn
mADORs.Open sSQL, mADOCon, adOpenDynamic
mADORs.MoveFirst

FillTextBoxes
DisableButtons "ButtonFirst", "ButtonPrev"

End Sub

Private Sub FillTextBoxes()

Dim cTxtBx As Control
Dim lFldNo As Long

For Each cTxtBx In Me.Controls
If cTxtBx.Tag Like "Field*" Then
lFldNo = Mid(cTxtBx.Tag, 6)
cTxtBx.Text = mADORs.Fields(lFldNo)
End If
Next cTxtBx

End Sub

Private Sub DisableButtons(ParamArray aBtnTags() As Variant)

Dim i As Long
Dim ctl As Control

For Each ctl In Me.Controls
ctl.Enabled = True
For i = LBound(aBtnTags) To UBound(aBtnTags)
If ctl.Tag = aBtnTags(i) Then
ctl.Enabled = False
Exit For
End If
Next i
Next ctl

End Sub

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Dennis wrote:
I have a recordset variable using ADO and I wondered if
anyone has a code example of setting various controls with
certain field values from that recordset. I wanted to
know a way to incorporate a Next and Previous button to
see other records.

Please let me know, Dennis