Solved

Access subform into numbered Word list

Posted on 2003-10-30
19
259 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
Comment Utility
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_
Comment Utility
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
Comment Utility
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_
Comment Utility
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_
Comment Utility
I'm in Access 2000, btw.
0
 
LVL 11

Expert Comment

by:joekendall
Comment Utility
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_
Comment Utility
Why would DAO be referenced? Why is a remote connection opened inside the database itself?
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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_
Comment Utility
There's no check box for Microsoft DAO library in VB Tools/Options/...
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Open some VBA code and select Tools/References

Nic;o)
0
 

Author Comment

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

Author Comment

by:Martine_
Comment Utility
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
Comment Utility
Oops, you need also a reference set to Microsoft word, thought you already had that active....

Nic;o)
0
 

Author Comment

by:Martine_
Comment Utility
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
Comment Utility
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_
Comment Utility
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_
Comment Utility
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_
Comment Utility
   .Selection.MoveLeft (wdCharacter)
    .Selection.Delete (wdCharacter)
Solves both problems.
Thanks for your help nico.
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 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

12 Experts available now in Live!

Get 1:1 Help Now