Word drop down list populated from acess table (1 Viewer)

stu_c

Registered User.
Local time
Today, 06:28
Joined
Sep 20, 2007
Messages
366
Hello all
I have got a word document within that I have a drop down list and if possible I would like to be able to populate the information from a acess table is that possible? If so can someone give me some guidance on how to do it, many thanks.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:28
Joined
May 7, 2009
Messages
16,881
you can but you need to use Legacy Controls (ActiveX).
click Design Mode in Developer Tab.
click on Legacy Tools on the Developer Tab and insert the combobox.

put the combobox anywhere the document.

on VBE, add reference to:

"Microsoft ActiveX Data Objects X.XX Library"

you will be needing a ADODB.Recordset and ADODB.Connection.
while on the VBE, add code to Docuement Open Event:
Code:
'* arnelgp
'* In VBE, add reference to
'* Microsoft ActiveX Data Objects X.XX Library
'*
'* you must also save the document as macro-enabled (.docm)
'*
Private Sub Document_Open()
    Dim DATA_PATH As String
    Dim adoRS As ADODB.Recordset
    Dim cnn As ADODB.Connection
    
    DATA_PATH = "the path to your MS Access database, eg. "D:\MyFiles\SourceDatabase.accdb"
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0; " & _
                            "Data Source = " & DATA_PATH & ";" & _
                           "Persist Security Info = False;"
    cnn.Open
    
    Set adoRS = New ADODB.Recordset
    With adoRS
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        '.CursorType=adOpenStatic
        .LockType = adLockOptimistic
        .CursorType = adOpenStatic
        
        '**
	'** NOTE, i am using Table1 from the database and retriving the names from Names field
	'**    
        .Open "SELECT [Names] From Table1;", cnn
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            Me.ComboBox1.AddItem !Names
            .MoveNext
        Wend
    End With
    Set adrs = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub
 

stu_c

Registered User.
Local time
Today, 06:28
Joined
Sep 20, 2007
Messages
366
this seems rather complicated :\
 

Trevor G

Registered User.
Local time
Today, 06:28
Joined
Oct 1, 2009
Messages
2,341
Have you tried to use Mail Merge to populate your document?
 

Users who are viewing this thread

Top Bottom