Word UserForm and Access

[Kevin R]

I have a UserForm with a couple of combo boxes and text boxes. I been able
to populate the combo boxes with data from an Access data base but now I need
to update a text field on the userform based on the selection from a combo
box. Example - in the database table I store the employee's first name, last
name, and title. When the user selects the employee's name from the combo
box, I want that employee's title to automatically populate into the text
field.

Here's the 1st part of my code so far, but how do I update the txtTitle
field on the userform after the user has selected the employee's name from
the combo box?

Private Sub UserForm_Initialize()
'Build drop down selection

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim aResults()

'Activate the Database connection.
Set db = OpenDatabase("C:\RO_DB.mdb")

'Open the tblRO_Mgmt table.
Set rs = db.OpenRecordset("SELECT [RO_FName], [RO_LName] FROM tblRO_Mgmt
ORDER BY [RO_LNAME]", dbOpenSnapshot)

i = 0

With rs
'Populate the cboROName drop down from the database.
Do Until .EOF
cboROName.AddItem (i)
cboROName.Column(0, i) = .Fields("RO_FName") + " " +
..Fields("RO_LName")
.MoveNext
i = i + 1
Loop
End With

'Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub

 

[Doug Robbins - Word MVP]

Populate the combobox with all of the data that is required, or all of the
data whether it is required or not, and then use the .BoundColumn property
of the combobox to get the title for the selected employee:

Private Sub UserForm_Initialize()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
' Open the database
Set db = OpenDatabase("D:\Access\ResidencesXP.mdb")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM Owners")
' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
ComboBox1.ColumnCount = rs.Fields.Count
' Load the ComboBox with the retrieved records
ComboBox1.Column = rs.GetRows(NoOfRecords)
' Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP