Automatic generation of the Word doc linked to Access db

Gd day,
Strategic question. There is Access application (FrontEnd-BackEnd-Security).
There are several tasks inside of the database, where the user is keeping track of some activities (repairs, outstanding ToDos, "ticket"etc.). Now for these purposes we are using Memo fields in most of the cases. When the Memo field is not enough (for example, engineer need to insert the pictures), we are generating the Word file and link it to the database.

Concerns about the above arrangement:
- Sometimes memo field can be rather big (there are "tickets" followed several years).  We are afraid of the database speed issues after some time.
- Creation of Word doc/ saving it under some name /linking  etc. is becoming a mess after certain number of repetitions during the day
- Big number of errors during the creation of word doc (wrong folder/wrong name/ forget to link etc);
- The formatting capacity of the Memo field is quite limited. Quite often we have to generate nice looking document even if the history consist of only two sentences.

Question: Is there a way to bring automation to the process?

Perfect case:
- At the moment of creation of new "ticket" (new record created with the form), word document is generated automatically based on the existing word template.
- The word document is automatically named (can be primary key of the record, for example) and saved in protected folder;
- Would be nice if the word.doc can automatically pick-up some fields values from the access record (date, object, engineer in charge etc.)

May be there is another well-known way to treat similar issues?

Thanks in advance,
hotelguestAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
Try this sample. Place unzipped files to some folder and run DB. In word template are added 2 bookmarks: Date and UName. Both will be filled with data from form when you press button. File will be saved in the same folder as UName & Date
DBWord.zip
0
hotelguestAuthor Commented:
Good day,
Initially looks great. To be on the safe side in terms of the sample understood:
- Edit of [Username] will result in generation of new Word file?
-  Edit of [Date] will update the Word file but the file name will remain unchanged? Correct?
- Important point in terms of further link: same command button will work as a link to the file already created .doc until not touching the [Username]
-Any further editing of Word file will not edit the db?

Additional questions in case above understanding is right:
- In real database, I will have to use primary key [ID_ToDo_Item] from table playing a role of [Username] in your sample. What will be the correct syntax? This primary key is the only unique field, which will not be edited after.
- I will have also to specify the different folder for this word generated files including the template. It will be  subfolder inside of the db folder location. Where is the correct place to specify the path?
- Can I increase the number of bookmarks ?

Thanks in advance,
0
Jeffrey CoachmanMIS LiasonCommented:
Investigate what als315 posted first.


<Sometimes memo field can be rather big (there are "tickets" followed several years).  We are afraid of the database speed issues after some time.>
Then this sounds like this memo data should be "normalized" out to a separate table.
This way you can sort and filter by date.

<- The formatting capacity of the Memo field is quite limited. Quite often we have to generate nice looking document even if the history consist of only two sentences.>
What version of Access are you using?
If Access 2003 or older, you are pretty much out of luck.  But if you are using Access 2007 or higher, you have some pretty good formatting options.
So here you may have to clearly state what is lacking in the memo formatting.. (keep in mind that Access is not a Word Processor, so you may never have things like sub/superscript, before/after Spacing, hanging indents, kerning, Borders, ...etc

Note that you may also need to post, at least, a rough example of the exact output you are looking for.

JeffCoachman
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

als315Commented:
It is only sample, so don't expect too many :).
- Edit of [Username] will result in generation of new Word file?
Yes, because it is used in file name generator
-  Edit of [Date] will update the Word file but the file name will remain unchanged? Correct?
No, same behaviour as above
- Important point in terms of further link: same command button will work as a link to the file already created .doc until not touching the [Username]
You can check if file exists with dir command and open existing file or create new one.
-Any further editing of Word file will not edit the db?
Yes, Word don't know anything about DB.
0
als315Commented:
Other questions:
- I will have also to specify the different folder for this word generated files including the template. It will be  subfolder inside of the db folder location. Where is the correct place to specify the path?
In this line:
doc.Saveas (CurrentProject.Path & "\" & Me.UserName & " " & Format(Date, "yyyy-mm-dd") & ".docx")

Open in new window

- Can I increase the number of bookmarks ?
Yes, of course

You can use any field on your form as a part of file name or data for bookmark
0
hotelguestAuthor Commented:
In response to  als315

The word document associated with the db record can be generated only once. If editing of the fields will generate additional .doc, then it is not what is needed.

I don't know how to use dir command

Regards,
0
hotelguestAuthor Commented:
IN response to boag2000.
As a matter of fact I did investigate what als had posted.

Normalization of the memo field into separate table is in place for the formal investigation cases (record not editable+time stamp + PIC stamp). Very difficult to read, very slow to understand the details and status, when you need to do it fast.
Looking at consolidated memo field (not split in a number of records) is easier
to observe and easier to edit as per unanimous opinion of people here.

In most of the cases, where the memo field formatting is not enough is when people want to insert pictures and tables. People want to see pictures. Nothing I can do about it.

When opening the case, you never know if will last 5 minutes or 5 years. For 5 days memo is enough. For 5 years we have word reports of 200 pages+

There is another corner of the db, where engineers are creating repairs specification data sheets, where sometimes you need even bigger functionality (pdf screen shots from drawings).
 Main idea is to reduce the number of errors, reduce the number of unsorted word and .xls files migrating in the various folders, reduce number of clicks while linking the docks to db, reducing the double job while filling-in company forms.

Anyway, I will take a look what kind of example I can post

Best regards,
0
Jeffrey CoachmanMIS LiasonCommented:
hotelguest,

OK, please continue of with als315, as I see he is dedicated to seeing this through.

I do not want to distract form your interactions with him, so I will first thank you for posting the extra info,
...one note though:
Normalization of the memo field into separate table is in place for the formal investigation cases (record not editable+time stamp + PIC stamp). Very difficult to read, very slow to understand the details and status, when you need to do it fast.
Looking at consolidated memo field (not split in a number of records) is easier
That was my point, the data would be in separate records, but then "concatenated" together for presentation to the user...

Again, with this date data spanning multiple dates/years, there may come a time when you are asked to:
Show all notes from today
Show all of last months notes
Show all notes from 2010 to present
Sort the notes in Ascending or Descending order by the date.
...etc

just some thoughts...

Again, please continue on with als315, as he is great with these types of question.
:-)

JeffCoachman
0
hotelguestAuthor Commented:
Thanks for above Jeff,

You cant imagine the number of options we have tried already, as the issue is really critical for us.

rgds,
0
als315Commented:
Try this sample (existing file will be opened, file name is generated from record ID). Add subfolder "Word" to DB's folder and place all docx there
DBWord.accdb
0
hotelguestAuthor Commented:
Good day,

Thanks. Great. It works. But there is a small problem. Command button is opening word closed for editing (read only).
db debugger highlights the following :
Set doc = objWord.Documents.Open(path & "Test.docx")

Regards,
0
als315Commented:
Is it network folder?
Try to add folder with word files to trusted folders and allow network folders to be trusted.
trusted locations
0
hotelguestAuthor Commented:
Good evening,

Most probably, my brains capacity is not sufficient. Sorry for it, but:
- Probably I must combine sample 1 and sample 2?

Sample 1:
- creating the new Word document based on the fields in the form-OK, as requested. Very good;
- saving new Word document. Very good.

Sample 2:
- form is based on table, which is good. Easier for me to replicate your idea in the real database;
- Trusted folder issue- solved;
- for every new record in the form, the Test.docx is opening with correct bookmarks. But this not a new doc. I am always modifying template "Test". (there must be: 1 record in the table=> one word document; Test.docx template- unchanged);

Apologies once again for a mess from my side. Pls clarify.
0
als315Commented:
In the last sample existing document should be opened and only for new records it is created from template.
0
hotelguestAuthor Commented:
No. New word is not created with new record. Test.docx is opened as per new bookmarks. Cahnges saved to the same Test.docx
0
als315Commented:
Do you have any errors? If debugger is stopped anywhere, document may not be saved.
0
als315Commented:
Here is buttton's code. Please, compare it with yours:
Private Sub Command6_Click()
Dim objWord As Object
Dim doc As Object
Dim objRange As Object
Dim path As String, FileName As String
Dim Fx As Boolean
If Me.Dirty Then Me.Dirty = False
path = CurrentProject.Path & "\Word\" ' Subfolder "Word" should be in DB's path
FileName = Format(Me.ID, "000000000") & ".docx"
Set objWord = CreateObject("Word.Application")
Fx = (Dir(path & FileName) <> "")
If Fx Then
    'File exists
    Set doc = objWord.Documents.Open(path & FileName)
Else
    Set doc = objWord.Documents.Open(path & "Test.docx")
End If
Set objWord = GetObject(, "Word.Application")

'Bookmarks
Set objRange = doc.Bookmarks("Date").Range 'Bookmark name
objRange.Text = Me.Dt
doc.Bookmarks.Add "Date", objRange

Set objRange = doc.Bookmarks("UName").Range 'Bookmark name
objRange.Text = Me.UserName
doc.Bookmarks.Add "UName", objRange
If Fx Then doc.Saveas (path & FileName)
objWord.Visible = True
End Sub

Open in new window

0
als315Commented:
Sorry, there really were errors. I've also made check for already opened Word
DBWord.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hotelguestAuthor Commented:
Miracle. Thanks. Fresh air. Sample is working exactly the way I wanted.
I wonder if I would be able to transfer your code into the real db.
Very soon I will find it out.
Thanks again and best regards,
0
hotelguestAuthor Commented:
Would be nice to have a contact in case of problems while merging the sample solution to the big db. Meanwhile, there is no a single reason why I can not accept the solution.
0
als315Commented:
You can continue here if you will need any clarifications. I receive notifications from participated questions
0
hotelguestAuthor Commented:
Gd day,
Well, everything went suprisingly smooth. Thanks again. People enjoying. This was exactly the thing weneeded. There is one element missing to call the case "perfect ": Command button to DELETE generated Word file from the  form without going to the folder and searching/deleting from there.
Let me know if you want me to issue new question to award more points.
Hava a nice day
0
als315Commented:
It is very simple and you have all nesessary code in my sample. You should add only confirmation and one string:
kill (path & FileName)
Full code for button:
Dim path As String, FileName As String
Dim Fx As Boolean, i As Integer
If Me.Dirty Then Me.Dirty = False
path = CurrentProject.path & "\Word\" ' Subfolder "Word" should be in DB's path
FileName = Format(Me.ID, "000000000") & ".docx"
Fx = (Dir(path & FileName) <> "")
If Fx Then
    'File exists
    i = MsgBox("Do you like to delete " & path & FileName & " ?", vbYesNo)
    If i = vbYes Then Kill (path & FileName)
End If

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.