Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

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
1.
2.
..
n.

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?
0
Martine_
Asked:
Martine_
  • 11
  • 7
1 Solution
 
nico5038Commented:
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.ClearFormatting
rng.Find.Text = strFrom
rng.Find.Replacement.ClearFormatting
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.

Nic;o)
0
 
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?
0
 
nico5038Commented:
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
   rs.movenext
wend

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

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

Getting the idea ?

Nic;o)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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?

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

Joe
0
 
Martine_Author Commented:
Why would DAO be referenced? Why is a remote connection opened inside the database itself?
0
 
nico5038Commented:
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.

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

Nic;o)
0
 
Martine_Author Commented:
Good. Thanks, the text had to be selected or else References was greyed out.
0
 
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."
0
 
nico5038Commented:
Oops, you need also a reference set to Microsoft word, thought you already had that active....

Nic;o)
0
 
Martine_Author Commented:
OK, I'm getting a RTE 5854, "string parameter too long" error message at:
rng.Find.Replacement.Text = strTo
0
 
nico5038Commented:
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.

Nic;o)
0
 
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:
 .ActiveDocument.Bookmarks("suggestion").Select

While Not rs.EOF
   strList = rs.RecordCount & ". " & rs!SugDesc & vbCrLf
   Selection.Text = strList
    rs.MoveNext
Wend
0
 
Martine_Author Commented:
OK, I've figured out how to move the cursur down, thanks to the MSDN site.  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrgrfnavigatingselectingtext.asp?frame=true&hidetoc=true
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")
        .ActiveDocument.Bookmarks("suggestion").Select
        .Selection.Text = ""
            While Not rs.EOF
              .Selection.Text = rs!SugDesc & vbCrLf
              .Selection.MoveDown (wdParagraph)
              rs.MoveNext
            Wend
        rs.Close
0
 
Martine_Author Commented:
   .Selection.MoveLeft (wdCharacter)
    .Selection.Delete (wdCharacter)
Solves both problems.
Thanks for your help nico.
0
 
nico5038Commented:
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 !

Nic;o)
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 11
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now