Need help inserting data from Access table into Word document 1

[Jtorres13]

Hello. I have an Access database with one table containing user data; one record per user. One of the table fields is FullName.

In Word, I have a template called Filler.dot, where I store several macros. This template opens automatically when Word opens, so the macros are always available.

In this Filler.dot, I created a form which gives me three options, letter, fax, and memo. I open the form by running the LMF macro, which reads:

Sub LMF()
    
    frmTemplateFiller.Show

End Sub

The form has a listbox listing all the records from the Access database, FullName field. The listbox is pulling all records as desired.

The code I wrote to open the form and connect to the database is:

Public Sub UserForm_Activate()
    Dim strSQL As String
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Me\My Documents\Users\TemplateFiller.accdb;Persist Security Info=False"
    strSQL = "SELECT * FROM tblUsers"

    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly

'    populate the list box
    Do While Not rs.EOF
            lstUsers.AddItem rs!FullName
            rs.MoveNext
    Loop
End Sub

My objective is to select a type of correspondence (letter, memo, or fax), click a name in the listbox, then and click a cmdGenerate button, which will run some (currently non-existent) code to open the right type of document and insert fields from the record I selected in the listbox.

Where I'm stuck is:

1. Narrowing down the table to the record selected in the listbox.

2. Inserting the data from the table fields into the document I just opened (letter, memo or fax) which has pre-entered text and it's just waiting for me to fill out the rest of the information with data from the database table, selected record.

I think I have to create bookmarks in the correspondence document as placeholders to where I want the fields from the database to be entered.

I looked in the FAQs and searched, but couldn't find any snipets that would give me a clue I can follow.


I hope I have explained sufficiently, but please ask away and I'll try to clarify wherever I went coo coo.

 

[Andrzejek]

In tblUsers I would assume FullName is unique?

To get the rest of the info from your table (actually from your recordset) per selected name you can try:

rs.Open 
rs.Filter = " FullName = '" & Replace(lstUsers.Text, "'", "''") & "'"[green]
'    populate all fields for that FullName[/green]
rs.Close

End Sub

Have fun.

---- Andy

 

[Jtorres13]

Yay Andrzejek! Thank you so much for the filtering syntax!

I put your code in the Generate button and was able to filter the listbox and find the record. With a little digging here and there, I figured out how to insert the value of fields (LastName) in the document.

Public Sub cmdGenerate_click()
   
   Dim doc As Word.Document
   ' Open the letter template
   Set doc = Word.Application.Documents.Open("C:\Users\Me\My Documents\Users\Letter.dotm")
   rs.Open
   rs.Filter = "FullName = '" & Replace(lstUsers.Text, " '", "' '") & "'"
   Selection.TypeText rs!LastName
   rs.Close

End Sub

I found info on how to use Bookmarks so I can jump here and there in the document and fill it out with data from the Access table. Love it!

 

[Andrzejek]

Good for you!

Just a note about Replace - it is in case you have names line O'Brian (with single quote in the name), so you need to replace it with 2 single quotes. You did not copy my code correctly, there should be no spaces in

Replace(lstUsers.Text, "[highlight] [/highlight]'", "'[highlight] [/highlight]'")

Have fun.

---- Andy