none
Fill in Word 2007 Forms Using Information Stored in Access 2007 RRS feed

  • Question

  • I found a "How do I...Dynamically fill Microsoft Word fields using Access Data?" from August 23, 2007.  This is the exact solution needed for a project at work, however, I'm using Access 2007 and Word 2007 and when I attempt to load the form, I receive: "Run-time error '3343': Unrecognized database format...." I suspect that the code is written using another version of Windows as well as Access, as both referenced the Word document extension as .doc, and Access extension as .mdb.   The Access '07 extension is .accdb, and Word '07 is .docx. 

    Here is the VBA code I found, and modified to my particular data source.  Also, I'm using Windows XP and the DAO 3.6 Object Reference Library.

    Private Sub Document_Open()
    
      'Populate Provider dropdown field.
    
      Dim db As DAO.Database
    
      Dim rst As DAO.Recordset
    
      Dim strSQL As String
    
      Dim strPath As String
    
      Dim doc As Document
    
      Set doc = ThisDocument
    
      strSQL = "SELECT [Provider Name] FROM [Center Days of Operation] ORDER BY [Provider Name]"
    
      strPath = "C:\080709_091209.accdb"
    
      'Update path to database file.
    
      Set db = OpenDatabase(strPath)
    
      Set rst = db.OpenRecordset(strSQL)
    
      Do While Not rst.EOF
    
        With doc.FormFields("ComboBox1").DropDown.ListEntries
    
          .Add Name:=rst(0)
    
        End With
    
        rst.MoveNext
    
      Loop
    
      Set db = Nothing
    
      Set rst = Nothing
    
    End Sub

    Please help. 

    iamh1

    Sunday, February 12, 2012 6:48 PM

Answers

  • Hi iamh1,

    Got the same error, I am not sure what's causing the error.

    I got it to work using ADO instead. Set reference to Microsoft ActiveX Data Object 6.0 Library.

    Then I use the UserForm's Initialize event to populate the Combobox with the Access data.

    See for more info on the technique, below thread:

    http://www.fontstuff.com/vba/vbatut10.htm

    So in your case the code looks like this:

    Private Sub UserForm_Initialize()
        On Error GoTo UserForm_Initialize_Err
        
        Dim cnn As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                 "Data Source=C:\080709_091209.accdb"
        rst.Open "SELECT [Provider Name] FROM [Center Days of Operation] ORDER BY [Provider Name];", _
                 cnn, adOpenStatic
        rst.MoveFirst
       
    
        With Me.ComboBox1
            .Clear
            Do
                .AddItem rst(0)
                rst.MoveNext
            Loop Until rst.EOF
        End With
    
    UserForm_Initialize_Exit:
        
        On Error Resume Next
        rst.Close
        cnn.Close
        Set rst = Nothing
        Set cnn = Nothing
        Exit Sub
    
    UserForm_Initialize_Err:
        MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
        Resume UserForm_Initialize_Exit
    
    End Sub

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    • Marked as answer by iamh1 Wednesday, February 15, 2012 11:56 PM
    Monday, February 13, 2012 12:59 AM
    Moderator
  • Yes, I thought you are using FormFields, as you are referring to them in your code, so that was misleading.

    Sorry about that.

    So below example on how to refer to ActiveX Objects (not residing on a Form, in that case it will be different) but in Document (InlineShape);

    Sub test()
    
    
    On Error GoTo Initialize_Err
        
        Dim cnn As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        
        Dim oCtl As InlineShape
        Dim oTB
        
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                 "Data Source=C:\TestDB\database2.accdb"
        rst.Open "SELECT [Test] FROM [tblTest] WHERE [ID]=3;", _
                 cnn, adOpenStatic
        
        rst.MoveFirst
        
    
            Do
               ' loop through all ActiveX Textboxes in document
               For Each oCtl In ActiveDocument.InlineShapes
                 ' find txtAgencyName
                 If oCtl.OLEFormat.Object.Name = "txtAgencyName" Then
    
                      Set oTB = oCtl.OLEFormat.Object
                      ' assign value
                      oTB.Text = rst!test
    
                End If
                
                Next
    
                rst.MoveNext
             
             Loop Until rst.EOF
    
        
    Initialize_Exit:
        
        On Error Resume Next
        rst.Close
        cnn.Close
        Set rst = Nothing
        Set cnn = Nothing
        Exit Sub
    
    Initialize_Err:
        MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
        Resume Initialize_Exit
    
    End Sub
    

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    • Marked as answer by iamh1 Friday, February 17, 2012 6:21 PM
    Friday, February 17, 2012 5:02 PM
    Moderator

All replies

  • Hi iamh1,

    Got the same error, I am not sure what's causing the error.

    I got it to work using ADO instead. Set reference to Microsoft ActiveX Data Object 6.0 Library.

    Then I use the UserForm's Initialize event to populate the Combobox with the Access data.

    See for more info on the technique, below thread:

    http://www.fontstuff.com/vba/vbatut10.htm

    So in your case the code looks like this:

    Private Sub UserForm_Initialize()
        On Error GoTo UserForm_Initialize_Err
        
        Dim cnn As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                 "Data Source=C:\080709_091209.accdb"
        rst.Open "SELECT [Provider Name] FROM [Center Days of Operation] ORDER BY [Provider Name];", _
                 cnn, adOpenStatic
        rst.MoveFirst
       
    
        With Me.ComboBox1
            .Clear
            Do
                .AddItem rst(0)
                rst.MoveNext
            Loop Until rst.EOF
        End With
    
    UserForm_Initialize_Exit:
        
        On Error Resume Next
        rst.Close
        cnn.Close
        Set rst = Nothing
        Set cnn = Nothing
        Exit Sub
    
    UserForm_Initialize_Err:
        MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
        Resume UserForm_Initialize_Exit
    
    End Sub

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    • Marked as answer by iamh1 Wednesday, February 15, 2012 11:56 PM
    Monday, February 13, 2012 12:59 AM
    Moderator
  • Daniel,

    Thanks for the suggestion.  I'll drop the code in and see what I get.  I'll respond with the results. 

    Again, thanks!

    iamh1


    iamh1

    • Marked as answer by iamh1 Wednesday, February 15, 2012 11:07 PM
    • Unmarked as answer by iamh1 Wednesday, February 15, 2012 11:56 PM
    Monday, February 13, 2012 4:58 PM
  • Thanks!  That worked out fine, however, I'm still in need of assistance. 

    I want to capture data from the DB, and populate fields in Word.  Now I'm getting the 5941 error message.   I've set the document object as .activedocument, and attempting to fill the doc.FormFields("...").result with the recordset rst!.... 

    I've "stepped" through the code and it is when I reach this section of the code that the program breaks.   I've tried to find the correct syntax in the Word documentation on the DOM, but I guess my eyes are not seeing what's written!

    Any suggestions or advice?

    Thanks much.

    iamh1


    iamh1

    Wednesday, February 15, 2012 11:06 PM
  • Hi iamh1,

    Usely you mark the answer given by the answerer, if the answer is the answer on your question ofcourse.

    Can you post the code you have so far?

    Thanks,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Wednesday, February 15, 2012 11:09 PM
    Moderator
  • My bad!  Thanks!

    Here is the code:

    Public Sub fillAllFields()

    On Error GoTo fillAllFields_Err
       
        Dim cnn2 As New ADODB.connection
        Dim rst2 As New ADODB.Recordset
        Dim appWord As Word.Application
        Dim doc As Word.Document
        Dim ans As String
       
        Set appWord = GetObject(, "Word.application")
        Set doc = appWord.ActiveDocument
           
        cnn2.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                 "Data Source=U:\XXXXXXXXX.accdb"
                
        rst2.Open "SELECT AgencyName, AgencyAddress, AgencyCity, AgencyState, AgencyZip, " _
               & "LicenseEffectiveDate, LastInspectionDate, LicenseCapacity, XXXXXX FROM usp_ReportProviderOutput ORDER BY ProviderName;", _
               cnn2, adOpenStatic
        rst2.MoveFirst
       
        With doc
            Do
                'The code breaks when processing the next line
                doc.FormFields("txtAgencyName").Result = rst2!AgencyName

    I appreciate your assistance.  Maybe I'm missing a semicolon, or a comma?

    Thanks.  I look forward to seeing the fix.

    Best regards,

    iamh1


    iamh1

    Thursday, February 16, 2012 12:00 AM
  • Hi iamh1,

    Well, i noticed you have the "With doc" in place, so no need to use it again;

    With doc
            Do Until rst.EOF
              .FormFields("txtAgencyName").Result = rst2!AgencyName
                rst.MoveNext
            Loop
    
    End With

    Make sure that the FormField is named txtAgencyName, else it can't find the Form field in your Document.

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Thursday, February 16, 2012 12:25 AM
    Moderator
  • Daniel,

    First, I apologize for not providing enough of the code for you to see the loop structure...I had the Until rst.EOF at the bottom.  I think either structure will work but I've modified my structure to match. 

    Then, with having removed the doc dot from the equation (which, actually I'd done before reaching out), I'm still receiving the 5941 error ("the requested member of the collection does not exist"). 

    I'm using Word 2007, on XP SP2 and choosing the ActiveX control; and, the control is named as used.  I'm just not getting where I'm missing the correct syntax.

    Is it possible that I need to declare, within the code, the txtAgencyName variable?


    iamh1

    Thursday, February 16, 2012 6:03 PM
  • Hi iamh1,

    Again, check the Name of your FormField, if this is not corresponding, the error 5941 will occur.

    See below screenshot, enter the Name in your Bookmark, which should be txtAgencyName:

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Thursday, February 16, 2012 9:00 PM
    Moderator
  • Dan (If I may),

    Isn't the screenshot reflective of using a "Legacy Forms" control object?  I'm using an "ActiveX" Text Box!  Is this possibly the issue? 

    I'm going to look at using the "Legacy Forms" object as opposed to the ActiveX object. 

    In furtherance, I found a routine (through the "Help" documentation) that would audit the form and report the number of "textfields" found.  This resulted in a count of zero such objects!  Again, I inserted ActiveX objects;  named them, and subsequently referenced them, as is in the code segment. 

    I continued looking for information/documentation, and found a routine that would "programatically" add a "wdFieldFormTextObject" object to the Word document, which is where I am at this writing. 

    But, if there is something short of this you're aware of, please....

    Again, thanks much, and I look forward to hearing from you.


    iamh1

    Thursday, February 16, 2012 11:56 PM
  • Yes, I thought you are using FormFields, as you are referring to them in your code, so that was misleading.

    Sorry about that.

    So below example on how to refer to ActiveX Objects (not residing on a Form, in that case it will be different) but in Document (InlineShape);

    Sub test()
    
    
    On Error GoTo Initialize_Err
        
        Dim cnn As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        
        Dim oCtl As InlineShape
        Dim oTB
        
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                 "Data Source=C:\TestDB\database2.accdb"
        rst.Open "SELECT [Test] FROM [tblTest] WHERE [ID]=3;", _
                 cnn, adOpenStatic
        
        rst.MoveFirst
        
    
            Do
               ' loop through all ActiveX Textboxes in document
               For Each oCtl In ActiveDocument.InlineShapes
                 ' find txtAgencyName
                 If oCtl.OLEFormat.Object.Name = "txtAgencyName" Then
    
                      Set oTB = oCtl.OLEFormat.Object
                      ' assign value
                      oTB.Text = rst!test
    
                End If
                
                Next
    
                rst.MoveNext
             
             Loop Until rst.EOF
    
        
    Initialize_Exit:
        
        On Error Resume Next
        rst.Close
        cnn.Close
        Set rst = Nothing
        Set cnn = Nothing
        Exit Sub
    
    Initialize_Err:
        MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
        Resume Initialize_Exit
    
    End Sub
    

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    • Marked as answer by iamh1 Friday, February 17, 2012 6:21 PM
    Friday, February 17, 2012 5:02 PM
    Moderator
  • Yes, this does help. 

    Again, after writing you, and continued on my search with the formField objects, and wound up finding information on ActiveX objects, as InlineShape objects. 

    I was just in the process of learning how to access these objects, which the answers you've provided.   For all of you assistance and guidance, I am greatly appreciative.  I believe I can move onto some of the other functionality I need. 

    I'm aiming at having this completed by next Friday.  I'll let you know how everything shakes out.  

    That being said,...have a great weekend!

    Until the next time...!


    iamh1

    Friday, February 17, 2012 6:21 PM
  • You are very welcome! Thanks for the kind feedback, and I wish you a great weekend as well! :)

    Cheers,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Saturday, February 18, 2012 4:52 AM
    Moderator
  • danishani(MCC),

    I've got to get used to the response protocol...I should have marked "helpful" upon my last communication. 

    It's been a couple, and I'm back to the project (other tasks took precedence, as well as a possible dillema with Windows 7 and VBA), but I have a situation where I am writing a SQL "where" clause that uses a variable.  My question,...how do I write the statement to recognize the value of the variable and not throw an error?

    [Maybe this one goes to your signature quote!]

    Look forward to hearing from you.

    Thanks,

    iamh1


    iamh1

    Friday, March 16, 2012 1:02 AM
  • No worries, when you want to use a variable, you can do something like this.

    Declare the variable first, set the variable then use it in your SQL statement.

    Dim myVar As Long 
    
    myVar = 3
    
    ' <snippet> of yr code
    
    rst.Open "SELECT [Test] FROM [tblTest] WHERE [ID]=" & myVar , _
    
    ' /<snippet> rest of your code here
    
    
    
    
    

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Friday, March 16, 2012 2:21 AM
    Moderator
  • Good Day!

    I've written the statement basically the same as you've suggested, an excerpt of which follows:

    rst2.Open "SELECT [ProviderName], [AgencyName], [ProviderID] FROM [Output] " _
            & "ORDER BY [ProviderID] WHERE [ProviderID] = " & ans & ";", _
               cnn2, adOpenStatic, adLockReadOnly

    So, as you can see, I've structured the statement, I hope, syntactically correct, however, I receive this message: 

    What's your thinking on this?


    iamh1

    Friday, March 16, 2012 2:31 PM
  • Your & and _  sign is place in the wrong order, try this:

    rst2.Open "SELECT [ProviderName], [AgencyName], [ProviderID] FROM [Output] " & _
            "ORDER BY [ProviderID] WHERE [ProviderID] = " & ans & ";", _
               cnn2, adOpenStatic, adLockReadOnly

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Friday, March 16, 2012 3:02 PM
    Moderator
  • I still receive the same message!

    iamh1


    Help!!!!  I've fallen and can't ...get it to work!
    • Edited by iamh1 Friday, March 16, 2012 6:59 PM
    Friday, March 16, 2012 3:20 PM
  • Can you post your whole code you have?

    Btw is your variable declared as Long or String? In other words the field you compare ProviderID is that a Long Integer or a Text field?

    In case of a Textfield try this:

    rst2.Open "SELECT [ProviderName], [AgencyName], [ProviderID] FROM [Output] " & _
            "ORDER BY [ProviderID] WHERE [ProviderID] = '" & ans & "';", _
               cnn2, adOpenStatic, adLockReadOnly

    I tested below code, and it works well:

    Sub test()
    
    On Error GoTo Initialize_Err
        
        Dim cnn As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        
        Dim oCtl As InlineShape
        Dim oTB
        Dim myVar As Long
        Dim strSQL As String
        
        myVar = 9
        
        strSQL = "SELECT [Test] FROM [tblTest] WHERE [ID]=" & myVar
        
          
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                 "Data Source=C:\testDB\testDB.accdb"
        rst.Open strSQL, cnn, adOpenStatic
        
        rst.MoveFirst
        
    
            Do
               ' loop through all ActiveX Textboxes in document
               For Each oCtl In ActiveDocument.InlineShapes
                 ' find txtAgencyName
                 If oCtl.OLEFormat.Object.Name = "testing" Then
    
                      Set oTB = oCtl.OLEFormat.Object
                      ' assign value
                      oTB.Text = rst!test
    
                End If
                
                Next
    
                rst.MoveNext
             
             Loop Until rst.EOF
    
        
    Initialize_Exit:
        
        On Error Resume Next
        rst.Close
        cnn.Close
        Set rst = Nothing
        Set cnn = Nothing
        Exit Sub
    
    Initialize_Err:
        MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
        Resume Initialize_Exit
    
    
    End Sub

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Friday, March 16, 2012 9:15 PM
    Moderator
  • Dan,

    Your assistance is truly, greatly appreciated. 

    Anyways...the variable is declared as a Public long in the declaration section ;  the code will probably look familiar as its the same basic strategy that you'd recommended previously, but with a few changes [the ones that are probably breaking it!]. 

    The first code block is in document_open(), and loads ComboBox1 perfectly.  It is on the change event that I need to capture the ProviderID to act as an index into the list of agencies associated with that provider.   This will populate another combobox for users to choose from that list of agencies, and subsequently populate the remaining text fields.

    Here is the snippet:

    Private Sub ComboBox1_Change()
    Dim found As String
    On Error GoTo ComboBox1_Change_Err
        cnn2.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                 "Data Source=U:\Test.accdb"
                 
        ans = txtProviderID.Value
        rst2.Open "SELECT [ProviderName], [AgencyName], [ProviderID] FROM [Output] " & _
               "ORDER BY [ProviderID] WHERE [ProviderID] = '" & ans & "';", _
               cnn2, adOpenStatic, adLockReadOnly
        
        found = ComboBox1.ListIndex
        
        Debug.Print "This is where the item was found: " & found & " and its value: " & ComboBox1.Value & " and the ID: " & ans
        
        'txtProviderID.Value = rst2(2).Value
            
    On Error GoTo ComboBox1_Change_Err
    ComboBox1_Change_Exit:
       
        On Error Resume Next
        rst2.Close
        cnn2.Close
        Set rst2 = Nothing
        Set cnn2 = Nothing
        Exit Sub
    ComboBox1_Change_Err:
        MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Oops...Fix it!"
        Resume ComboBox1_Change_Exit
    End Sub
    Thanks again for all of your advice!  Talk soon...

    iamh1

    Wednesday, March 21, 2012 10:43 PM