Fill userform textboxes with data from Access database

[johnsonk]

Hi, I have a userform with a combobox and some texboxes I can get data from access database for the combobox but I also want it to populate the textboxes with a combobox change event how do I do that ?

Private 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.Jet.OLEDB.4.0;" & _
"Data Source=myDatabase.mdb"
rst.Open "SELECT DISTINCT [Product Code] FROM Stores Code ORDER BY [Product Code];", _
cnn, adOpenStatic
rst.MoveFirst
With Me.ComboBox1
.Clear
Do
.AddItem rst![Product Code]
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

Private Sub ComboBox1_Change()

??????

 End Sub

 

[ranman256]

when user changes combobox1 , it fills the form

Private Sub ComboBox1_AfterUpdate()
LoadData2Form
End Sub


Private Sub LoadData2Form()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myDatabase.mdb"
rst.Open "SELECT * from table where [Product Code] ='" & ComboBox1 & "';", cnn, adOpenStatic

With rst
  txtBox1 = .Fields("Name").Value & ""
  txtBox2 = .Fields("Addr").Value & ""
  txtBox3 = .Fields("Phone").Value & ""
Wend
Set rst = Nothing
End Sub

 

[johnsonk]

Hi ranman256, Thanks for the code I am getting syntax error on the code below.

rst.Open "SELECT * from table where [Product Code] ='" & ComboBox1 & "';", cnn, adOpenStatic

Regards