Access subform into numbered Word list

I've created a procedure to insert text boxes and other controls from the form into a Word document using bookmarks.  So far so good but the form also allows the user to select from a list. These are added into another table.

In the Word document, it's displayed as

I never know how many items are going to be selected so I can't use bookmarks. Any suggestions?

Also, does anyone know of any good links/books on how to interact Access and Word?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can place a unique string (e.g. $$list$$) behind the "1." numeration and use the next function to insert the listitems:

Function fncReplace(strFrom As String, strTo As String)

Dim doc As Word.Document
Dim rng As Range

Set rng = ActiveDocument.Content
rng.Find.Text = strFrom
rng.Find.Replacement.Text = strTo
rng.Find.Execute Replace:=wdReplaceAll

End Function

Just make sure the listitems are concatenated with a vbCRLF in between and Word will do the numbering for you.

Martine_Author Commented:
Um, I think I know what you're saying and it looks good. Maybe we could back up a little. Before I put in this code how do I get every item that they selected into the word document? I have a query created from the table which contains items that they've selected.  How would I roll through the query and insert each item?
Just a recordset processing loop like:
dim rs as dao.recordset
dim strList as string

'open the recordset
set rs = currentdb.openrecordset("<your query>")

'process all rows
while not rs.eof
   'concatenate all found fields in one string
   strList = strlist & vbCRLF & rs!listfieldname

'remove first character as that's a vbCRLF
strList = MID(strlist,2)

'place in document
call fncReplace("$$list$$", strList)

Getting the idea ?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Martine_Author Commented:
Thanks for responding, I'm looking at your code carefully trying to learn from it. I put your code in (with my tables and fields, of course) and got an error message on the line  Dim rs As dao.Recordset "User defined type not defined"

I have a couple of questions which were not answered by my book or Access help so bear with me:
1) Why are we creating a new connection to the database when we're already inside the database? I've looked up opening the recordset and it says that this is used for remote data sources.
2) When would I use DAO vs. ADO?

Martine_Author Commented:
I'm in Access 2000, btw.
Make sure you have a reference to the Microsoft DAO 3.x Object Library. That is why you are getting the error.

Martine_Author Commented:
Why would DAO be referenced? Why is a remote connection opened inside the database itself?
You need a Microsoft DAO library referenced to let Access know what objects are available.
The version number doesn't really matter, normally one or more versions will be available.
Just open Tools/References when your in the VBA "mode" and make sure a checked reference to a Microsoft DAO library exists or check one.

DAO or ADO do have different possibilities and ADO will replace DAO in the future, but it will work OK for your purpose.

Martine_Author Commented:
There's no check box for Microsoft DAO library in VB Tools/Options/...
Open some VBA code and select Tools/References

Martine_Author Commented:
Good. Thanks, the text had to be selected or else References was greyed out.
Martine_Author Commented:
OK, when the code is run, it gets to the function fncReplace line,
Dim doc As Word.Document
and then gives a "user defined type not defined."
Oops, you need also a reference set to Microsoft word, thought you already had that active....

Martine_Author Commented:
OK, I'm getting a RTE 5854, "string parameter too long" error message at:
rng.Find.Replacement.Text = strTo
Oops, too much data for insertion using a string :-(

Makes things a bit different. Now we'll have to insert each part "straight away".
Haven't done that yet. You'll be able to use the replace for the first replace.
Next however we'll need to insert each list string and vbCRLF using the established position in the document.

I'm however too busy at the moment to dive into this directly. Can you start checking it yourself and post again when stuck.

Martine_Author Commented:
OK, I'm trying to insert the string directly into the Word document. How do I move the cursuor down? It's overwritting the previous entry.

Here's the code:

While Not rs.EOF
   strList = rs.RecordCount & ". " & rs!SugDesc & vbCrLf
   Selection.Text = strList
Martine_Author Commented:
OK, I've figured out how to move the cursur down, thanks to the MSDN site.
There are still two minor problems:
1) it adds an extra blank numbered line
2) it doesn't remove the "suggestion" bookmark. This isn't a problem for my other bookmarks, maybe it's the loop.

Here's the code:

        Set rs = CurrentDb.OpenRecordset("qrySug")
        .Selection.Text = ""
            While Not rs.EOF
              .Selection.Text = rs!SugDesc & vbCrLf
              .Selection.MoveDown (wdParagraph)
Martine_Author Commented:
   .Selection.MoveLeft (wdCharacter)
    .Selection.Delete (wdCharacter)
Solves both problems.
Thanks for your help nico.
Great job Martine !

Sorry but my work didn't allow me to dive into this as deep as I would have liked.
I know on forehand that it can take (several) hours to solve...
So I also need to thank you !

Success with the appication !

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.