Need help with creating word reports (1 Viewer)

Spricar

New member
Local time
Today, 06:41
Joined
Mar 4, 2013
Messages
7
Hello!

I'm an access newbie and would really appreciate some help on, what is probably, a simple matter;

I have an access form that fills an access table (MS Access 2003). Also, I have a Word (2010) document/template with some template text and empty fields which should be filled with data from access. Is it possible to insert a button in access form which would open a word document and populate appropriate fields with data from that form?

Many thanks in advance

regards
S.
 

DavidAtWork

Registered User.
Local time
Today, 13:41
Joined
Oct 25, 2011
Messages
699
Try this code here and paste it into the On-Click event of your button
remember to set a reference to Microsoft Word in Tools/References

Dim appWord As Word.Application
Dim doc As Word.Document

Set appWord = GetObject(, "Word.Application")
appWord.Visible = True
Set doc = appWord.Documents.Open("C:\myFolder\myDocTemplate")
doc.FormFields("ForenameVal").Result = Me.Forename
doc.FormFields("SurnameVal").Result = Me.Surname
doc.FormFields("DOBval").Result = Me.Dob
doc.SaveAs "C:\myFolder\myNewDoc.doc"

This will work when your template has formfields and bookmarked with approriate names such as the ones I've used above and these just need to pointed at the fields on your form.

David
 

Spricar

New member
Local time
Today, 06:41
Joined
Mar 4, 2013
Messages
7
Thanks for your help!

So, if I understand this correctly, this will only write/save in a word document? Or will it open it and fill appropriate fields, so document can be printed?

another question, how can I link blank fields in word template with access data? Should I replace "SurnameVal" with name of the field in template, while "Me.Forename" is a name of the field in access?

I'm sorry if this are stupid questions, but as I've said, I'm an access noob:(
 

DavidAtWork

Registered User.
Local time
Today, 13:41
Joined
Oct 25, 2011
Messages
699
The code above will start an instance of Word, make it visible and then open your template/doc and pre-populate any form fields you've created in your template/doc
"another question, how can I link blank fields in word template with access data?"
You need to create your word document that you'll use as a template, add form fields to the document (have the Form toolbar visible and click on the 'ab' icon), go to the properties by right-clicking on the object and select the data type and give them a meaningful name in the bookmark box. These can then be referenceed as above and you can point them to object values on your Access form as I have done above,
doc.FormFields("ForenameVal").Result = Me.Forename
here the text form field in your document is named ForenameVal and your Access form field is Me.Forename.

David
 

Spricar

New member
Local time
Today, 06:41
Joined
Mar 4, 2013
Messages
7
The code above will start an instance of Word, make it visible and then open your template/doc and pre-populate any form fields you've created in your template/doc
"another question, how can I link blank fields in word template with access data?"
You need to create your word document that you'll use as a template, add form fields to the document (have the Form toolbar visible and click on the 'ab' icon), go to the properties by right-clicking on the object and select the data type and give them a meaningful name in the bookmark box. These can then be referenceed as above and you can point them to object values on your Access form as I have done above,
doc.FormFields("ForenameVal").Result = Me.Forename
here the text form field in your document is named ForenameVal and your Access form field is Me.Forename.

David

OK!
This should help, you really explained it well so even I can understand what to do;)
Again, thanks for your help, will try that;)
 

Kosako

New member
Local time
Today, 10:41
Joined
Jul 14, 2013
Messages
6
Sorry, hello everyone, but I am trying to get a code that:

1. populate a textbox with data display in a listbox, which by the way, I can select multiple rows, in which case, duplicated values should be shown one time only;
2. populate a word with data from those textbox , but only Word document open that I am seeing with bookmark box, in which case, if it doesn't have any, don't do anything. What I am trying is to avoid to get that "Set doc = appWord.Documents.Open("C:\myFolder\myDocTemplate") " because I have 479 Word reports to allocated and reference then.

Much apreciated
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:41
Joined
May 7, 2009
Messages
19,169
you need to Open the word document in order to find out if it has the Bookmark you
are after. you don't open the 479 at one time, you open them one at a time.
 

Kosako

New member
Local time
Today, 10:41
Joined
Jul 14, 2013
Messages
6
Thanks arnelgp for your answer. The thing is that I want populate, the word are all bookmarked with the same data, 3 fields that I want to populate individually from the access. So I don't wanna located in the VBA code each Word report.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:41
Joined
Feb 28, 2001
Messages
26,999
Kosako, there are only two ways to do what you describe.

EITHER you build every document to do a MailMerge (which is a WORD function) that can draw on different data sources - and you would have to individually define the sources,

OR you write code that visits every Word document to update it by finding bookmarks. If all of the Word documents are in the same folder, this isn't so terrible since you can use the FileSystemObject to scan a directory for files of a given file type - like .DOCX for modern Word files.

Either way, you have to touch every file, to set it up initially with MailMerge or to populate files individually using Word Application Objects and VBA automation one file at a time.
 

Kosako

New member
Local time
Today, 10:41
Joined
Jul 14, 2013
Messages
6
Hello The_Doc_Man, thanks for your reply. I am now wonder how could I use the "FileSystemObject to scan a directory for files of a given file type" and get my access to populate a given Word report. For instance, is it viable to put in my FormsName table a hyperlink to each filename? Any example?
 

mike60smart

Registered User.
Local time
Today, 13:41
Joined
Aug 6, 2017
Messages
1,899
Hi

Have you thought of creating Access Reports to replace your Word Documents?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:41
Joined
Feb 28, 2001
Messages
26,999
I am now wonder how could I use the "FileSystemObject to scan a directory for files of a given file type"


That is the "top" of a reference to using the file system object. You can click hyperlinks within that to see what the FSO can do.


There is also a way to get a "collection" of files - which can be individually checked for names in a FOR EACH type of loop. There is an example in the linked article.

You would use the files collect and the FSO in conjunction with each other to identify and manipulate files.

is it viable to put in my FormsName table a hyperlink to each filename?

Again, to do that you have to at least once have a scan or other multi-line source of the names to PUT in that table. But if you had such a table, it would be usable to obtain a filename.

Kosako, there are probably a dozen different ways to do this. You will have to look through the suggestions you are given by me and others here to see which one makes the most sense to you. And the one that makes sense to you is the one you should probably use. Unfortunately, I have no way of knowing what will work for you because I don't know your preferences.
 

Kosako

New member
Local time
Today, 10:41
Joined
Jul 14, 2013
Messages
6
Hello The Doc Man, thanks for your suggestions. Have a great day.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:41
Joined
Feb 28, 2001
Messages
26,999
You are welcome, and in this crazy world of pandemics, be safe out there!
 

Users who are viewing this thread

Top Bottom