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
