Using UserForm to get Database DATA into Word Form Letter

[maxillo]

Hello All,

I am trying to automate the writing of a form letter. I have a UserForm, where a name is selected from a combobox that is linked to a .mdb database with addresses. I can populate the combobox with the name of the addressee, but how do I get the rest of the address and relevant info into the word document?

Below is the code that I have for the UserForm

Private Sub UserForm_Initialize()

'declare database object variables
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset 'object
Dim ConnString As String

'initialise connection
Set con = New ADODB.Connection

'open the connection
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=S:\DOCS\REFERALBASE\ReferalsAddresses.mdb;" & _
"Persist Security Info=False"
con.Open ConnString

'initialise recordset
Set rs = New ADODB.Recordset

'open recordset (using the connection)
rs.Open "SELECT * FROM REF_ADDRESS ORDER BY [_NICK];", con, adOpenStatic

'use recordset
rs.MoveFirst
With Me.cboReferral
Do
.AddItem rs![_TITLE]
rs.MoveNext
Loop Until rs.EOF
End With

'exit userform
UserForm_Initialize_Exit:
On Error Resume Next
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
Exit Sub

'userform error
UserForm_Initialize_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume UserForm_Initialize_Exit


End Sub

​

I want the user to select a record in the combobox, then have the columns of that record (_NICK, _TITLE, _ADDRESS) stored in an array so that they can be inserted at various points in the document to complete the form letter. It should function like a mail merge, but only for one record.

Any advise would be much appreciated.

Cheers


Mike