Ms access & word automation

Mohammad Alsolaiman
Mohammad Alsolaiman used Ask the Experts™
on
Hi:
I need to do the following automation scenario between ms access and word!
1-      Open ms access form based on a record (new record).
2-      Insert the fields (doc no#, letter to, letter date, title and picture to), then press a button "Edit Word"
3-      Open word template with the data (doc no#, letter to, letter date, title and picture to)-(coming from access form).
4-      After employee complete writing word letter text, and when press save(word)
5-      Export the data from word document to ms access form (like: doc text … etc)
6-       Save an instance of the word document with the name= doc no# in specific folder automatically.
Can I do such thing? Please help
Automation-Access-word.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
GrahamSkanRetired
Top Expert 2012

Commented:
That sounds like quite a project.

Which bit(s) are you having a problem with?

I suggest that the word template uses form fields for the variable data and the template be protected for forms.

You will need a way for the employee to in initiate the saving of the editable fields. I think this will have to be a Word macro, with some (ADO) code to insert the field data into the correct record of the correct table.
Mohammad Alsolaimanapplication programmer

Author

Commented:
good scenario  GrahamSkan,
any small sample to follow please
Retired
Top Expert 2012
Commented:
Here is some code to get started on filling in a new document form a text box on your form
Sub DataToWord()
    Dim wdApp As Word.Application
    'Dim bNewInstance As Boolean
    Dim wdDoc As Word.Document
    
    On Error Resume Next 'supress error checking
    Set wdApp = GetObject(, "Word.Application")
    On Error GoTo 0 'resume error checking
    
    If wdApp Is Nothing Then
        Set wdApp = CreateObject("Word.Application")
        wdApp.Visible = True
        bNewInstance = True
    End If
    
    Set wdDoc = wdApp.Documents.Add("C:\MyTemplates\MyTemplate.dot")
    
    wdDoc.FormFields("MyField").Result = txtMyField
    'If bNewInstance = True Then 'only quit the application
        'wdApp.Quit
    'End If
End Sub

Open in new window

11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

GrahamSkanRetired
Top Expert 2012

Commented:
Here is some Word macro code to show how to write back to the database table
Sub FFToDatabase()
    Dim strConnection As String
    Dim rs As New ADODB.Recordset
    Dim strSQL As String
    Dim conn As New ADODB.Connection
    
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\MyDatabases\MyDatabase.mdb;User Id=admin;Password=;"
    conn.Open strConnection
    
    strSQL = "Select * from mytable WHERE RecordID = " & ActiveDocument.FormFields("REcordID").Result

    rs.Open strSQL, conn, adOpenDynamic, adLockPessimistic, adCmdText
    rs.Fields("NewData").Value = ActiveDocument.FormFields("SomeData").Result
    rs.Fields("NewData1").Value = ActiveDocument.FormFields("MoreData").Result
    '...
    rs.Update
    rs.Close
    conn.Close
End Sub

Open in new window

Mohammad Alsolaimanapplication programmer

Author

Commented:
1-      I am newbie to making templates, I watch a video and I make one.
2-      I do the form in ms access and past the code you provide, but I get this err message 5941 Something like that (Group member is required)
3-      I don’t know how to define a variable in ms word (be careful , I use word 2010)
4-      How to show the vba editor in ms word and where to post the code
5-      Please step by step in shortcut I'll do it , I can search the net for some unclear descriptions.
thank you very much for patient on me
Mohammad Alsolaimanapplication programmer

Author

Commented:
sorry
here is attach of the sample data base
the err line is :     wdDoc.FormFields("MyField").Result = txtMyField
Automation-Access-word.accdb
GrahamSkanRetired
Top Expert 2012

Commented:
1- Creating a template is like creating a document, except that you save it as a template. It will have to be macro-enabled, so choose extension .dotm.

2- The Access code would be best triggered from a command button on the form. Have you changed the name of the text box in the code to one on your document?

3- You can Dim variables in Word VBA, just as in Access.

4- Also as in Access, you can open the VBA editor with Alt+F4

Your question asked how to achieve an objective, so I gave an overview of a method of doing so. The whole project will comprise many steps, and if you have few of the required skills you would be well advised to gain them before starting.
Mohammad Alsolaimanapplication programmer

Author

Commented:
i had attach a word doc have a pictures to illustrate what happen to me
please if you can help me with that
After-I-save-the-document.docx
Top Expert 2009
Commented:
See my old Code Sample #24 (Four Ways to Merge to Word) for a discussion of several ways to merge data from Access to Word.  My recent ebook, Working with Word (available from Office Watch) updates these techniques, and adds a few new ones, for Word versions up to 2010.

Here is a link for the code sample:

http://www.helenfeddema.com/Files/code24.zip
GrahamSkanRetired
Top Expert 2012

Commented:
It isn't clear whether you have fired the code from a button on your current form, or whether the name txtMyField matches one on that form.

I know that you have provided a copy of your current database, but I have not opened it because this is a how-to site and not a do-it-for-you site, so you will have to grasp the concepts and apply them to your situation.
Mohammad Alsolaimanapplication programmer

Author

Commented:
"It isn't clear whether you have fired the code from a button on your current form, or whether the name txtMyField matches one on that form."
here is my button code
"Private Sub btnAutomate_Click()
   Call DataToWord
End Sub"
and the name of the field on my form is "txtMyField" i copy from property and past it.
i think my problem right now is in the word template
i dont know how to define a field on it to transfare the data to it . i am newbie doing such thing in word.
GrahamSkanRetired
Top Expert 2012

Commented:
You can put Form fields on to a document from the Controls group on the Developer tab.

The set of controls immediately visible are  Content Controls. You could use these, but they are more difficult to programme for. Click on the dropdown button to reveal the Legacy Forms and the ActiveX Controls. Click on the left one and a text input form field will be placed on the document at the selection point.

The box is assigned a bookmark which is used to name it. You will need to know the name. Right click on the field and choose Properties from the pop-up. This will open the Text Form Field Options dialogue. The default name for the first one is Text1. You can change it to something meaningful if you want to. I have called them MyField, SomeData and MoreData in my snippets above.

After the document design has been done, it will need to be protected, so that the user can only fill in the form fields. There is a 'Protect Document' button on the Developer tab. This opens the 'Restrict Formatting and Editing' task pane. Tick the check in the second section and choose 'Filling in forms' and the the 'Yes, Start Enforcing Protection' button.
Mohammad Alsolaimanapplication programmer

Author

Commented:
Thank you very much  GrahamSkan
My big point was "How to: Show the Developer Tab on the Ribbon" which I found it here
http://msdn.microsoft.com/en-us/library/bb608625.aspx
if there any good reference for programming ms word please post it here
one small question:
where to post this code in word?
"Sub FFToDatabase()
    Dim strConnection As String
    Dim rs As New ADODB.Recordset
    Dim strSQL As String
    Dim conn As New ADODB.Connection
   
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\MyDatabases\MyDatabase.mdb;User Id=admin;Password=;"
    conn.Open strConnection
   
    strSQL = "Select * from mytable WHERE RecordID = " & ActiveDocument.FormFields("REcordID").Result

    rs.Open strSQL, conn, adOpenDynamic, adLockPessimistic, adCmdText
    rs.Fields("NewData").Value = ActiveDocument.FormFields("SomeData").Result
    rs.Fields("NewData1").Value = ActiveDocument.FormFields("MoreData").Result
    '...
    rs.Update
    rs.Close
    conn.Close
End Sub"
Mohammad Alsolaimanapplication programmer

Author

Commented:
Thank you very much  Helen_Feddema
I start study your code, it is wonderful
Can I reuse it in my apps. please
Mohammad Alsolaimanapplication programmer

Author

Commented:
thanks to all of you very much
GrahamSkanRetired
Top Expert 2012

Commented:
You should post the Code into a module of your template. For testing purposes you can run the code by putting the cursor anywhere in the procedure and pressing F5. Eventually you will want to create a keystroke shortcut or an QAT button to run it.

This is done via Customize in the Word Options dialogue.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial