vba word-update userform textbox base on selection in combobox

[djames2173]

I am trying to give functionality to a userform [which i have already created] that will do the following. When a user selects an individuals fullname from a combobox dropdown that is populated from an access database, various other text boxes are populated with the details of that persons record.

 

[Remou]

Create the combobox with several columns, using the List property. The additional columns can hold other details from the record, which can be filled into textboxes in a change event for the combo:

Me.txtText=Me.cboCombo.Column(2)

Where column numbering starts from 0.

 

[djames2173]

Remou,
Thank you for your response. I am going to sohw you the code i have to populate the combobox below. I am having difficulty getting two different columns to appear from the database. Any assistance or examples are greatly appreciated.

Private Sub UserForm_Initialize()
'allocate memory for the database object as a whole and for the active record
Dim myDataBase As Database
Dim myActiveRecord As Recordset
'Open a database
Set myDataBase = OpenDatabase("C:\Authors.mdb", True, True)
'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset("Authors", dbOpenForwardOnly)
'Loop through all the records in the table until the end-of-file marker is reached
Do While Not myActiveRecord.EOF
ComboBox1.AddItem myActiveRecord.Fields("LookupName")
'access the next record
myActiveRecord.MoveNext
Loop
'Then close the database
myActiveRecord.Close
myDataBase.Close
End Sub

 

[Andrzejek]

Have you tried:

Private Sub ComboBox1_Click()
Dim myDataBase As Database
Dim myActiveRecord As Recordset
'Open a database
Set myDataBase = OpenDatabase("C:\Authors.mdb", True, True)
'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset([b]"Select * from TableName Where Authors = '" & ComboBox1.Text & "'"[/b], dbOpenForwardOnly)
[b]
If Not myActiveRecord.EOF
    TextBox1.Text = myActiveRecord!FieldName1
    TextBox2.Text = myActiveRecord!FieldName2
    TextBox3.Text = myActiveRecord!FieldName3
End If[/b]
'Then close the database
myActiveRecord.Close
myDataBase.Close
End Sub

HTH

---- Andy

 

[Remou]

I was thinking of something like:

Dim astrArray() As Variant
'Array of 5 fields resized to number of records in 
'the recordset
x = rs.RecordCount - 1
ReDim astrArray(x, 4)
j = 0
Do While Not rs.EOF

  For i = 0 To 4
    astrArray(j, i) = rs.Fields(i)
  Next

rs.MoveNext
j = j + 1
Loop

Me.cboCombo.List = astrArray