Pulling Data from Access to Word/Excel

[AfCo]

I'm trying to pull in data from Access into multiple Word & Excel templates.
I want this information to auto populate corresponding fields in the
templates by inputing one primary key data. Help!

Example:

When Project # 12345 is entered, it will then auto populate several fields
with all the data regarding that project #. The data is stored in MS Access
2003.
Thanks!

 

[Doug Robbins - Word MVP]

I would list all of the projects in a multicolumn list box on a userform so
that the user could select the project number and then by clicking on a
command button, have the data transferred into the fields in the template.

Here is a routine that will populate such a listbox from a table in an
Access data base

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
ListBox1.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)
' Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

Here is a bit of code similar to that you will need for the command button
click event. It will need modifying for your particular case.

Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm2.Hide
End Sub

And, if you need to learn how to create a userform, see the article "How to
create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm


--
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

 

[BK13]

Doug,

I am so new to this VB stuff- this is what I need to do, however I am
getting an error on this:
Private Sub UserForm_Initialize()

Dim db As DAO.Database

with Compile error, user-defined type not defined

Can you give me some direction on what I am missing please?

Many thanks
Bryan

 

[Doug Robbins - Word MVP]

You have not set a reference to the Microsoft DAO 3.6 Object Library as is
required to declare such an object.

In the Visual Basic Editor, go to the Tools menu and select References and
scroll down to the above item and check the box against it.

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.

 

[BK13]

Thanks Doug! I got that piece working. now I got to figure out how to make
my list box pull its data from an access DB table.

 

[Doug Robbins - Word MVP]

The code that I gave you does exactly that.

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.