Word VBA ComboBox Access lookup 1

[jmpawson]

I have created a userform in Word VBA designed to get users to save files in the correct folder, with a standard naming system. The form has various text box fields and a combobox.

How do I get the ComboBox to lookup a field (ClientName), in an access database table (SMDB.MDB / Clients) stored on a network share, and use the field (CSavePath)?

I've done VBA in Access and Word before, but have never had to get them talking to each other before.

Any comments appreciated. Ta

 

[BillPower]

Try this as a starter/guide:

Private Sub UserForm_Initialize()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmbArray()
Dim lngRows As Long
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\SMDB.MDB ;" & _ 'edit this
"User ID=;" & _
"Password=;"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "SELECT CSavePath, ClientName FROM Clients ORDER BY ClientName;", conn, adOpenStatic, _
adLockBatchOptimistic, adCmdText
cmbArray = rs.GetRows()
rs.MoveFirst
For lngRows = 0 To UBound(cmbArray, 2)
ComboBox1.Column = cmbArray
Next lngRows
conn.Close
Set conn = Nothing
End Sub

To use it you must have the "Microsoft AciveX Data Objects Library" referenced. To test it create a Form - UserForm, create a Combo Box - ComboBox1, ColumnCount = 2, BoundColumn = 1, choose your own Column Widths.

Put this in ComboBox1's click event to test ComboBox1:

MsgBox ComboBox1.Value

Should display CSavePath.

Let me know if you have any problems.

 

[jmpawson]

That is BRILLIANT. Thanks so much, its given me a great start into ADO/OLE etc.