Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / January 2006

Tip: Looking for answers? Try searching our database.

Update fields of mail merge based on lookup to database

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
mjbostwick@yahoo.com - 16 Jan 2006 14:25 GMT
I am trying to use the mail merge features of Word to create a template
or form that will create one document based on a lookup to an external
database. I would ask the user to fill in the name of the server they
are interested in and populate the rest of the mail merge fields with
the pertinate information associated with that server name. I have been
able to set up my document to get the necessary data but cannot figure
out how to create a fillin field that will actually go and look the
data up.

Can any one point me in the right direction?
Doug Robbins - Word MVP - 16 Jan 2006 15:33 GMT
You use the terms database and server as if they are perhaps the same thing,
or do you really mean that you want to create a single document that
contains the data from a single record in one table in a database.

If that's really what you are doing, then I would use a userform containing
a listbox that was populated with the data from the database and then the
user could select an item from the listbox and when they then clicked on a
button on the userform, the data from the fields in that record would be
inserted into the document.

See the article "How to create a Userform" at:

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

The following is the code that you would use to populate a listbox with data
from a table in an access database:

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

Signature

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

>I am trying to use the mail merge features of Word to create a template
> or form that will create one document based on a lookup to an external
[quoted text clipped - 6 lines]
>
> Can any one point me in the right direction?
mjbostwick@yahoo.com - 16 Jan 2006 18:01 GMT
When I mentioned database and server, it was a database containing
information about servers we maintain. The database is a mySql database
with multiple tables and my query contains the joins to pull the info
into the form. I guess a list box will work. I will just have to modify
your VB code to use a ODBC connection.

Thanks
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.