UserForm question

[Matt]

I need to get some values from an Access database and place them into
a worksheet. I am thinking the best way to do this would be to create
a user form with a few controls (combo boxes) that would allow them to
pick the different items they need from the Access database (a job
number, and a status option). They would then click OK on the form
and the appropriate fields would be populated on the worksheet. Does
this sound like the best method to accomplish this? I have started
creating a userform with one combo box to lookup the job number but it
is not listing any data.... the code I am using is below: THanks!

Private Sub ComboBox1_Change()
On Error GoTo UserForm_Initialize_Err
Dim i As Integer
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=U:\Intranet\pmdata.mdb"
rst.Open "SELECT [JobNumber], [JobName] FROM mstJobs WHERE
[CONTRACTSTATUS] = TRUE ORDER BY [JobNumber];", _
cnn, adOpenStatic
rst.MoveFirst

With Me.ComboBox1
.Clear
Do
.AddItem rst![JobNumber]
rst.MoveNext
Loop Until rst.EOF
End With

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

 

[David Sisson]

I generally put my code in the Private Sub UserForm_Initialize(). But
if your requirements for the combobox change as you progress through
the user form, then put the code in the ComboBox1_Enter, or Click, or
DblClick event.

 

[Matt]

I generally put my code in the Private Sub UserForm_Initialize(). But
if your requirements for the combobox change as you progress through
the user form, then put the code in the ComboBox1_Enter, or Click, or
DblClick event.

Thanks for the reply. I will place the code in the
UserForm_Initialize() and see what happens. I guess I don't
understand how that code would be specific to my combo box?? I am new
to Excel VBA, obviously, so any tips or help is appreciated.

Thanks,Matt