Solved

Access subform into numbered Word list

Posted on 2003-10-30
19
267 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_
  • 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
 

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

929 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now