Option Explicit
Public Sub UserForm_Initialize()
On Error GoTo UserForm_Initialize_Err
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\ProjectStatusDetails.accdb"
rst.Open "SELECT ProjectName, PMName, CoachName FROM ProjectStatusDetail;", _
cnn, adOpenStatic
rst.MoveFirst
With Me.ComboBox1
.Clear
Do
.AddItem rst![ProjectName]
.List(.ListCount - 1, 1) = rst!PMName
.List(.ListCount - 1, 2) = rst!CoachName
rst.MoveNext
Loop Until rst.EOF
End With
ComboBox1.ListIndex = 0
UserForm_Initialize_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
UserForm_Initialize_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume UserForm_Initialize_Exit
End Sub
Sub ComboBox1_change()
With Me.ComboBox1
Me.TextBox2.Text = .List(.ListIndex, 1)
Me.TextBox3.Text = .List(.ListIndex, 2)
End With
End Sub
b