Access form Details to Word Template (1 Viewer)

stu_c

Registered User.
Local time
Today, 22:57
Joined
Sep 20, 2007
Messages
489
Hi all
I have got an access form that a user fills out, once complete I would like the user to click a button that automatically puts the data into a word template with the fields going to the correct places on the word document is this possible? I presume I may have to use bookmarks on word but no idea on the process of this working :confused:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:57
Joined
May 7, 2009
Messages
19,169
create a document in word.eg:

the quick brown fox [1] over the lazy [2].

highlight the 1, including the bracket.
on the word ribbon, Insert->Bookmark.
name your bookmark same as the field in your table where data will come.

do the same with 2, in the example above.
save your doc.

test it:
Code:
Private Sub test()
    Dim oWd  As Object 'Word.Application
    Dim oDoc As Object 'Word.Document
    Dim bm   As Object 'Word.Bookmark
    
    'Set oWd = New Word.Application
    Set oWd = CreateObject("Word.Application")
    Set oDoc = oWd.Documents.Open("path where you save the .docx + the filenae")
    
    Set bm = oDoc.Bookmarks("nameOfBookmarkyYouMade")
    bm.Range.Text = "arnelgp"
    oWd.Visible = True
End Sub
 

stu_c

Registered User.
Local time
Today, 22:57
Joined
Sep 20, 2007
Messages
489
Hi guys
Thank you for your help, I have managed to get it to work! just have a few teething issues

1: how can you format the text so its all in CAPS when been copies onto the work documents?

2: I have a field with a start time (24hrs), even when the time has been entered as HH:MM it automatically does it as HH:MM:SS on the word template, how do I default this to HH:MM
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:57
Joined
Sep 21, 2011
Messages
14,048
Use the UCASE and Format functions as you insert

Code:
? ucase("test")
TEST

? format(now(),"hh:nn")
14:03

HTH
 

stu_c

Registered User.
Local time
Today, 22:57
Joined
Sep 20, 2007
Messages
489
Hi guys,
sorry to bring this back up, I got this working great following on from my question above but now wish to try and expand this?, how would I pick up the data from a Tabular sub form (SFRMStaffInvolvements) and put each entry onto a different line within the word document?

I haven't put all the fields in as its basically repeating each step over, form names are
FRMComplaints - Main Form
SFRMStaffInvolvements - Sub Form

Thanks in advance

Code:
Private Sub BTNCreateReport_Click()
Dim oWd  As Object 'Word.Application
    Dim oDoc As Object 'Word.Document
    Dim bm   As Object 'Word.Bookmark
   
    'Set oWd = New Word.Application
    Set oWd = CreateObject("Word.Application")
    Set oDoc = oWd.Documents.Add("C:\WORD\TEMPLATES\AccessReport.docx")
   
‘ DATA FROM MAIN FORM
    Set bm = oDoc.Bookmarks("COMPLAINTNO")
    bm.Range.Text = UCase(COMPLAINTNO)
    oWd.Visible = True
   
    Set bm = oDoc.Bookmarks("REPORTINGDATE")
    bm.Range.Text = UCase(REPORTINGDATE)
    oWd.Visible = True

    Set bm = oDoc.Bookmarks("CUSTOMERNAME")
    bm.Range.Text = UCase(CUSTOMERFORENAME & " " & CUSTOMERSURNAME)
    oWd.Visible = True

'---------------------------------------------------------------------------------------------------
‘ DATA FROM SUBFORM
 
Set bm = oDoc.Bookmarks("STAFFINVOLVEMENTS")
  bm.Range.Text = UCase([Forms]![SFRMStaffInvolvements]![STAFFSURNAME])
oWd.Visible = True

End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:57
Joined
Sep 21, 2011
Messages
14,048
Check out how The_Doc_Man did it.

 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:57
Joined
Feb 28, 2001
Messages
27,001
OK, what is the problem? I know your goal, but what happens when you try that would make you seek further help? I need some kind of symptoms to understand better what you tried and what went wrong with it.
 

stu_c

Registered User.
Local time
Today, 22:57
Joined
Sep 20, 2007
Messages
489
Hello,
thanks for the reply, I have tried the following code

bm.Range.Text = UCase([Forms]![FRMComplaints]![SFRMStaffInvolvements].[Form]![STAFFSURNAME])

shows the error message Run-time error '13':
Type mismatch



OK, what is the problem? I know your goal, but what happens when you try that would make you seek further help? I need some kind of symptoms to understand better what you tried and what went wrong with it.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:57
Joined
Feb 28, 2001
Messages
27,001
Can you put a breakpoint in the code on that failing line so that it breaks before you attempt the operation? If so, you can then open the Immediate window (from the menu bar's View option). Type a Debug.Print command twice:

Code:
Debug.Print  bm.Range.Text 
Debug.Print  [Forms]![FRMComplaints]![SFRMStaffInvolvements].[Form]![STAFFSURNAME]

You should be able to see what is in each element of the expression. If not, record what it tells you and report it here. My guess is that something is wrong with one of those, so the first step is to find out which one.
 

stu_c

Registered User.
Local time
Today, 22:57
Joined
Sep 20, 2007
Messages
489
I have finally got it to sort of work, it now copies the data onto the word document, the only issue is if I have more than one entry it only shows one of them, not sure if its overwriting the previous input or only reading the last one
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:57
Joined
Feb 28, 2001
Messages
27,001
With Word, there is an Options collection that is part of the App properties. You can choose whether you are in INSERT or OVERWRITE mode.

WordApp.Options.ReplaceSelection = False

However, there is also the issue that using bm.Range.Text = syntax is also an implied replace.

You might try something like your first assignment statement, bm.Range.Text = but then follow it with bm.Range.InsertAfter text to append. There might also be issues with formatting there, and it can be difficult. If you just add the text, Word will justify the paragraph. If you add a paragraph marker, that means you have added a paragraph too. That could in some cases be an issue.

Word is really picky on a good day from the GUI. From VBA it can be frustrating beyond belief.
 

stu_c

Registered User.
Local time
Today, 22:57
Joined
Sep 20, 2007
Messages
489
so with regards to WordApp.Options.ReplaceSelection = False , would you put this at the top of all the coding or within each data code for each field eg.

Code:
Private Sub BTNCreateReport_Click()
Dim oWd  As Object 'Word.Application
    Dim oDoc As Object 'Word.Document
    Dim bm   As Object 'Word.Bookmark

    'Set oWd = New Word.Application
    WordApp.Options.ReplaceSelection = False
    Set oWd = CreateObject("Word.Application")
    Set oDoc = oWd.Documents.Add("C:\WORD\TEMPLATES\AccessReport.docx")

OR

Code:
Private Sub BTNCreateReport_Click()
Dim oWd  As Object 'Word.Application
    Dim oDoc As Object 'Word.Document
    Dim bm   As Object 'Word.Bookmark

    'Set oWd = New Word.Application
    Set oWd = CreateObject("Word.Application")
    Set oDoc = oWd.Documents.Add("C:\WORD\TEMPLATES\AccessReport.docx")
‘ DATA FROM SUBFORM

Set bm = oDoc.Bookmarks("STAFFINVOLVEMENTS")
WordApp.Options.ReplaceSelection = False
bm.Range.Text = UCase([Forms]![SFRMStaffInvolvements]![STAFFSURNAME])
oWd.Visible = True


With Word, there is an Options collection that is part of the App properties. You can choose whether you are in INSERT or OVERWRITE mode.
However, there is also the issue that using bm.Range.Text = syntax is also an implied replace.

You might try something like your first assignment statement, bm.Range.Text = but then follow it with bm.Range.InsertAfter text to append. There might also be issues with formatting there, and it can be difficult. If you just add the text, Word will justify the paragraph. If you add a paragraph marker, that means you have added a paragraph too. That could in some cases be an issue.

Word is really picky on a good day from the GUI. From VBA it can be frustrating beyond belief.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:57
Joined
Feb 28, 2001
Messages
27,001
@stu_c - Setting the .ReplaceSelection option is a one-time thing just after you open the Word App or, at latest, after you open the word document but before you do anything else in it. Note that if you close and re-open the Word app object, you need to reassert your options. However, I believe that closing and re-opening just the document does NOT lose the App.Options settings.

The thing I was saying about "implied replacement" is the syntax of using bm.Range.Text = something. The .Range object would allow you to do .InsertBefore or .InsertAfter or .InsertParagraphBefore, etc. Those are targeted insertions to a reasonably well-defined insertion point (IP). But .Text implies the whole of the .Range unless you acted to constrain it first. Therefore, that equals sign is a replacement, or perhaps an overlay.

Sorry I didn't see this earlier. We are having moderately serious work done on the house and it is barely controlled chaos around here right now. I go for walks just to get away from the noise of saws and drills and hammering.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:57
Joined
Feb 19, 2002
Messages
42,976
Forms don't hold data. Tables hold data. Your source to complete this Word document should always be a query that selects the record or records you need. I've attached a sample that includes selecting multiple rows from a child table and placing them into a table in the word document.
 

Attachments

  • SampleWordAutomation180206WithLetters.zip
    446.9 KB · Views: 105

stu_c

Registered User.
Local time
Today, 22:57
Joined
Sep 20, 2007
Messages
489
sorry for the late reply, been rather manic
problem is there are records in 3 different tables, I have managed to get the information from the main form and the sub forms but my issue is that

Forms don't hold data. Tables hold data. Your source to complete this Word document should always be a query that selects the record or records you need. I've attached a sample that includes selecting multiple rows from a child table and placing them into a table in the word document.


Hello Doc Man
thank you for the below, I genuinely think this has gone past my knowledge of access, are you able to provide any pointers on the actual code?

Code:
Private Sub BTNCreateReport_Click()
Dim oWd  As Object 'Word.Application
    Dim oDoc As Object 'Word.Document
    Dim bm   As Object 'Word.Bookmark

    'Set oWd = New Word.Application
    Set oWd = CreateObject("Word.Application")
    Set oDoc = oWd.Documents.Add("C:\WORD\TEMPLATES\AccessReport.docx")
‘ DATA FROM SUBFORM

Set bm = oDoc.Bookmarks("STAFFINVOLVEMENTS")
WordApp.Options.ReplaceSelection = False
bm.Range.Text = UCase([Forms]![SFRMStaffInvolvements]![STAFFSURNAME])
oWd.Visible = True


@stu_c - Setting the .ReplaceSelection option is a one-time thing just after you open the Word App or, at latest, after you open the word document but before you do anything else in it. Note that if you close and re-open the Word app object, you need to reassert your options. However, I believe that closing and re-opening just the document does NOT lose the App.Options settings.

The thing I was saying about "implied replacement" is the syntax of using bm.Range.Text = something. The .Range object would allow you to do .InsertBefore or .InsertAfter or .InsertParagraphBefore, etc. Those are targeted insertions to a reasonably well-defined insertion point (IP). But .Text implies the whole of the .Range unless you acted to constrain it first. Therefore, that equals sign is a replacement, or perhaps an overlay.

Sorry I didn't see this earlier. We are having moderately serious work done on the house and it is barely controlled chaos around here right now. I go for walks just to get away from the noise of saws and drills and hammering.
 

Users who are viewing this thread

Top Bottom