Solved

Access subform into numbered Word list

Posted on 2003-10-30
19
305 Views
Last Modified: 2012-06-21
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
Comment
Question by:Martine_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 7
19 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 9652711
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
 

Author Comment

by:Martine_
ID: 9652822
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
 
LVL 54

Accepted Solution

by:
nico5038 earned 250 total points
ID: 9657976
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
Independent Software Vendors: 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!

 

Author Comment

by:Martine_
ID: 9659346
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
 

Author Comment

by:Martine_
ID: 9659358
I'm in Access 2000, btw.
0
 
LVL 11

Expert Comment

by:joekendall
ID: 9659403
Make sure you have a reference to the Microsoft DAO 3.x Object Library. That is why you are getting the error.

Joe
0
 

Author Comment

by:Martine_
ID: 9659499
Why would DAO be referenced? Why is a remote connection opened inside the database itself?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 9659600
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
 

Author Comment

by:Martine_
ID: 9659695
There's no check box for Microsoft DAO library in VB Tools/Options/...
0
 
LVL 54

Expert Comment

by:nico5038
ID: 9659750
Open some VBA code and select Tools/References

Nic;o)
0
 

Author Comment

by:Martine_
ID: 9659784
Good. Thanks, the text had to be selected or else References was greyed out.
0
 

Author Comment

by:Martine_
ID: 9659830
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
 
LVL 54

Expert Comment

by:nico5038
ID: 9660038
Oops, you need also a reference set to Microsoft word, thought you already had that active....

Nic;o)
0
 

Author Comment

by:Martine_
ID: 9672678
OK, I'm getting a RTE 5854, "string parameter too long" error message at:
rng.Find.Replacement.Text = strTo
0
 
LVL 54

Expert Comment

by:nico5038
ID: 9674602
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
 

Author Comment

by:Martine_
ID: 9689816
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
 

Author Comment

by:Martine_
ID: 9750937
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
 

Author Comment

by:Martine_
ID: 9751099
   .Selection.MoveLeft (wdCharacter)
    .Selection.Delete (wdCharacter)
Solves both problems.
Thanks for your help nico.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 9751298
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question