Using Access data for Word fields

[BruceM]

I don't know where to post this, so I'm trying a group from each of the
applications in question.

I wonder if if would be possible for Word form fields to "see" data in an
Access pop-up form. I have Access code that searches for and opens a
specific Word document. I could have it open the pop-up form first, then
open Word after the user is done entering data into the pop-up form (I would
have them click a button to hide the form and continue with opening the Word
document). Once the Word document is open I would like to have data from
the pop-up form (or its underlying table) appear on the Word document. If
it is possible I assume I would use form fields, but if I am incorrect in
this I am quite willing to be flexible.

The data would be on the order of a word or number, most likely ten
characters or less in most cases, if that matters. There would be three or
four such fields.

If it matters I have user-level security on the database. I would not be
saving the data that is added to the Word document.

This would have to be for either Office 2000 or Office 2003. It would be
the same version on each individual computer.

 

[Mr. B]

Take a look at Albert Kallal's "Super Easy Word Merge"

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

Scroll down a little on the page.

This should help you do exactly what you want to do.

 

[GeoffG]

When you say "form fields", do you mean:

1. {FORMTEXT} fields in the document;
or
2. Textboxes on a UserForm?

In Microsoft Word, press ALT-F9 in the document to see/hide
{FORMTEXT} fields. These are inserted using the FORMS toolbar.

If you do mean {FORMTEXT} fields, then I think you'd probably be
better off not using them, but rather using Bookmarks instead. I
say this because you want to copy only the data currently in the
Access form to the Word document and it's very simple to
reference a Word Bookmark in VBA. As you say, you could use a
COPY button on the Access form to copy data from the Access form
to the bookmarks in the Word document. The bookmarks expand to
include the text you insert, so it should be easy for the user to
return to the Access pop-up form, change their minds and use the
COPY button again.

If you mean TextBoxes on a UserForm, then I think this is more
problematic.

I think you have a range of solutions. You may need to
experiment to discover what works best in practice for you.

Post back if you need help with coding.

Geoff

 

[BruceM]

I have looked at that before, and just now looked at it again, and can still
not understand how to make it work. The worst part is that Albert says most
users can use it without training, so I can only suppose I am more dense
than most users when it comes to Mail Merge. Perhaps the assumption is that
users are already skilled at Mail Merge.

In any case, the instructions say to save the document, which is absolutely
not what I want to do. The idea is that a read-only document is open. The
user would enter Job Number and a few other items into a bound pop-up Access
form. The information would be saved in Access and copied to appropriate
locations on one page of the document. That page would then be printed.
The only part of this I cannot do is to copy the information from Access.
If this was all done in Access I would set the Control Source of a text box
on a report to Forms!MyForm!MyField1, where MyForm is the pop-up form I
mentioned. Essentially, I would like to do the same thing except with a
Word document rather than an Access report.

 

[BruceM]

OK, I'm open to the idea of using Bookmarks. I mention Formtext fields,
inserted via the Forms toolbar, because I am familiar with them. I am
familiar with bookmarks too, just not in this context. In any case I am not
talking about a userform.

The idea is that data typed into a bound Access form would be copied to the
document, which would then be printed, but the copied information would not
be saved in the Word document, as it would be different the next time the
document is used.

You have stated what it is I want to accomplish: press a button on the
Access form to copy the data to the Word document. The question is how do I
do that? How do I reference the Word document from the Access from, or
conversely how do I get the Word document to "see" the data on the Access
form? I guess the question is about the syntax for referencing one
application from the other.

 

[Doug Robbins - Word MVP]

Do it the other way around. See the last of the following series of
articles:

http://www.mousetrax.com/techpage.html#autoforms
Please Fill Out This Form
Part 1: Create professional looking forms in Word
http://www.computorcompanion.com/LPMArticle.asp?ID=22

Part 2: Adding Automation to your Word forms.
http://www.computorcompanion.com/LPMArticle.asp?ID=46

Part 3: Learn more VBA (macros) to automate your forms.
http://www.computorcompanion.com/LPMArticle.asp?ID=119

Part 4: Use custom dialog boxes in your Word forms
http://www.computorcompanion.com/LPMArticle.asp?ID=127

Part 5: Connect your AutoForm to a database to save input time and keep
better records!
http://www.computorcompanion.com/LPMArticle.asp?ID=136


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[BruceM]

Thanks. I can see how that could be made to work. I would use a macro when
the Word document is opened to bring up a user form, where the information
would be added, then transferred to the Word document so it can be printed
and to the Access fields for storage. After printing I would clear the form
fields inthe Word Document.

Only some of the users would need to add the data as described, so I expect
I should have the code in an Add-In, which is already in place for those
same users so that they can print only certain predetermined pages from the
document at the click of a toolbar button.

The ADODB connection was the missing piece, along with the use of a user
form rather than the document itself to transfer the values.

Once I get this worked out all I need to do is apply it to a few hundred
documents (i.e. add the form fields and the user form). I can probably
automate some of that, but I will save that for another time if the powers
that be decide to give me the resources and/or the time needed to modify the
Word documents. The main thing for now is to demonstrate it can be done.
Thanks to the information you have provided, I see how that is possible.

 

[GeoffG]

I have a solution that works in Access (based on what I knew
of your requirements).

However, it sounds from your reply to Doug that your
requirements might be best achieved using Word after all.

BTW - If you use Word templates, instead of Word documents,
they should open like new each time you create a new
document from them. (You can change a document to a template
using Save As.)

Geoff

 

[BruceM]

There is no need to save the word documents each time. The documents are
technical procedures of varying levels of complexity, but all at least
fairly elaborate. Only one or two pages are printed in most cases. The
information to be printed includes such things as job number, which
currently are written by hand. They would not be saved with the document.
I agree that a template would offer some advantages (I am quite familiar
with how they work), but the down side is that each of the ten or so people
who work directly with editing and changing the documents would need to be
instructed in how to work with modifying templates, or else they would need
to understand the need to save each a revised document with a new name; then
I would convert these to templates.
The documents from which people print the necessary pages are protected
files in a protected location. I don't think I need to have any extra
network permissions to allow print-only temporary modifications (i.e.
filling in form fields), but if I do I can arrange for that. I can take
care of permissions for form fields by allowing only the ability to fill in
forms. In any case, if I can avoid converting back and forth from documents
to templates, so much the better.

 

[Doug Robbins - Word MVP]

The method in the following article on fellow MVP, Greg Maxey's website
might actually be more suitable for your requirements:

http://gregmaxey.mvps.org/Extract_Form_Data.htm

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[BruceM]

Thanks for the information. I would not be saving the Word files. Rather,
the idea is to open a Word file, insert the job number and two or three
other fields so that the Word document can be printed with that information,
and save the fields to an Access database. The information would not be
saved with the Word files, and there has never been a business need to save
the Word files, so in this case it seems the user form is the approach to
take.
However, I can see the potential of looping through files to extract data,
for the exact reason that is shown in the link (i.e. survey data), so I will
be saving the link for future reference. I expect it can be adapted for use
with Excel files, too.

 

[Mike Painter]

Thanks for the information. I would not be saving the Word files. Rather,
the idea is to open a Word file, insert the job number and
two or three other fields so that the Word document can be printed
with that information, and save the fields to an Access database. The
information would not be saved with the Word files, and there has
never been a business need to save the Word files, so in this case it
seems the user form is the approach to take.
However, I can see the potential of looping through files to extract
data, for the exact reason that is shown in the link (i.e. survey
data), so I will be saving the link for future reference. I expect
it can be adapted for use with Excel files, too.

I've missed most of this but with rare exception a report will do most of
what can be done with Word.

The hardest part is making the field fit properly.

The persons full name is Tom Jones and mumble, mumble, mumble.

is solved with Me.somewtextbox = "The persons full name is " &
trim(FullName) & " and mumble, mumble, mumble."

 

[BruceM]

I have worked extensively with both Word and Access. If you are saying an
Access report can usually serve in place of a Word document I can only say I
disagree strongly. In any case, the question was about inputting data once
so that it appears on a Word form that is to be printed but not saved, and
at the same time is stored as an Access record.

 

[Doug Robbins - Word MVP]

I disagree very strongly with the matter about which you only disagree
strongly.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Mike Painter]

I also have worked with Word and Access and find that only the occasional
need for some special formatting prevents preparing a report that is
indistinguishable from a Word document. If the formatting is conditional,
the ball falls back into the Access court.

 

[BruceM]

If by saying you disagree with the "matter" about which I am disagreeing you
say you are agreeing with Mr. Painter that Access is a suitable substitute
for Word I would be interested in knowing more about your thinking on the
subject. If you meant I should have provided some detail to back up my
disagreement, here are some of the issues.

Access does not allow for the use of styles. Along with this, widow/orphan
control, keep with next, and other paragraph formatting are not available in
any meaningful way. Same for text formatting such as character and line
spacing.

Allowing formatting of selected text can be accomplished only through the
use of third-party utilities pre-Access 2007. I'm not sure just what is
needed in Access 2007.

Numbered lists can only be accomplished manually in Access, or maybe through
the use of various coding, whether VBA or SQL, but in any case not without
considerable extra effort.

Pre-Access 2007 a report cannot be readily saved as a pdf file or any format
that can be used outside of Access. I know that Word does not have this
built-in capability, but my point is that an Access report is not portable
in anything like the same way as a Word document.

Unless an Access report is converted to pdf or another more or less
universal format it cannot be sent (as an e-mail attachment, for instance)
without sending the entire database file. If the report relies on stored
data that means sending the tables, queries, code modules, and so forth. If
it uses unbound text boxes rather than stored data it can only be edited by
switching to design view, making the changes, and switching back to print
preview. In design view it is pretty easy for an inexperienced user to
delete an entire text box instead of just the text it contains, and things
of that sort. Once the version with unbound text boxes is edited the
previous version is lost. That is the same as with a Word document, of
course, but saving each version of the report separately can only be done
within the context of the entire database file, which could quickly lead to
a very large file, particularly if graphics are involved.

Graphics are another problem if using Access as a word processor. Except
for limited situations the graphics must be stored outside of the database,
and linked as needed. This makes portability a problem, particularly
outside of a LAN. Another problem with portability is that many e-mail
systems do not allow mdb files.

This summarizes some of the main reasons I do not see Access as a substitute
for Word. I think some of these limitations are addressed in Access 2007,
but I would be surprised if they have been addressed to the extent that
Access can be seen as a viable substitute for Word.

My reply to Mr. Painter may have seemed abrupt. In retrospect I should
simply have ignored the posting, as he said he had "missed most of this".

 

[BruceM]

It would seem your use of Word differs considerably from mine. I have
outlined in a response to Doug Robbins some of the reasons I do not see
Access as a stand-in for Word. In any case, the previous part of the thread
outlined a different situation than you may have envisioned in that I am
working with pre-existing Word documents. To provide some more detail,
those documents contain lots of tables, which are quite difficult to emulate
in Access; and graphics, which quickly bloat a database to an unsustainable
size. Access 2007 may have solved some of the graphics problems, and maybe
provides a way to put together something that looks like a table without the
need to line up a bunch of text boxes, but even if so, converting those
documents to Access reports would be difficult and time consuming at best.

 

[Doug Robbins - Word MVP]

Certainly, there are horses for courses. And, for many of the features that
you cite, Word maybe the better option. There are however a multitude of
posts to these newsgroups where the posters are trying to get Word to do
something with their Access data when using Access alone would actually be a
simpler option.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[BruceM]

I should have made it clear that there are several hundred pre-existing Word
documents, most laden with tables and graphics, many running to 40 pages or
more. Converting them to Access is not a practical option, particularly
given the number of different people who create and edit the documents. An
Access database would be ideal in some ways, but converting the documents
would be just the start. After that I would need to find a way to allow the
users to edit and format to their liking in Access or another database
program. If it can be done it would be a development project beyond the
scope of what I can begin to attempt given the other demands on my time.

Your links about the user forms seem that they would address the specific
challenge very well indeed, as I hope I made clear when you first posted
them. Without going into a lot of detail about the business situation I can
only say I am confident I am using the right tools for the job. Again, I
thank you for the help you have provided.