Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2015
    Posts
    17

    help creating a form to transfer data from word to access

    Hi everyone, I'm trying to create a form in access with a combox that when I
    choose one of the 3 word files from the source folder will add the data into the
    tblNewStudents table. When I try it I get a error message saying the document


    you selected does not contain the required form fields. Can anyone please help
    me?? . I have attach the database and the 3 word files. Thank you
    243CCA04NewC.zip

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The error is correct.

    A form field is not the same as a table. The example word docs only have tables.

    See http://wiki.smartsimple.com/wiki/Add..._Word_Document

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want to pull data from Word into Access table? Why is Word involved?

    The Word documents have tables, not merge fields. AFAIK, code will have to open and manipulate the Word table as an object. Review http://www.pcreview.co.uk/threads/wo...ss-db.1674814/
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    this is the code I used:

    Code:
    Dim strDocName As String
    Dim appWord As Word.Application
    Dim doc As Word.Document
    Dim docTable As Table
    Dim iRow As Long
    Dim sSQL As String
    Dim sValue As String
    Dim db
    
    'in case of problems: whether Word document is open, is correct format etc.
    'On Error GoTo ErrorHandling
    strDocName = CurrentProject.Path & cstrFolder & strFileName
    Set appWord = CreateObject("Word.Application")
    Set doc = appWord.Documents.Open(strDocName)
    
    For Each docTable In doc.Tables
        sSQL = "INSERT INTO tblNewStudents (fldStudentNo, fldFirstName, fldLastName, fldTelephone) VALUES ("
        For iRow = 2 To docTable.Rows.Count
            sValue = Trim(docTable.Cell(iRow, 2).Range.Text)
            sSQL = sSQL & "'" & Left(sValue, Len(sValue) - 2) & "',"
        Next iRow
        sSQL = Left(sSQL, Len(sSQL) - 1) & ")"
        Debug.Print sSQL
        Set db = CurrentDb
        db.Execute sSQL
        Set db = Nothing
    Next docTable
    doc.Close
    Set doc = Nothing
    Set appWord = Nothing
    You can add whatever you want but if you're trying to load one document at a time that may be tedious and you can cycle through all files in a specific folder to do what you want as well using fileystemobject commands.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Also note... this code will bomb out if the number of rows goes on after the phone number, and as ssanfu pointed out, you are using field names expecting them to relate to your word table which they do not, you have to reference them by the row and column number.

  6. #6
    Join Date
    Jan 2015
    Posts
    17
    Hello everyone,
    Thank you for the information, so I follow the instructions on the links but I'm still getting the same error I also tried the code that rpeare suggested but gives me that error too any more suggestions? I have attach the new sample word documents and the database with the form. Thank you
    243CCA04NewC.doc243CCA04NewB.doc243CCA04NewA.doc243CCA04.zip

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    rather than saying 'it gives an error' or 'it doesn't work' how about saying where in the code it bombs out and what the error message is. I used your example files so I know the code works, if you are not adapting it to your actual table/field names it will fail.

  8. #8
    Join Date
    Jan 2015
    Posts
    17
    Hi rpeare sorry I didn't explain it very well,
    at first I was getting: the document
    you selected does not contain the required form fields, now when I try to select a document the list is blank so I edit the list of items and select the document but it tells me again the document you selected does not contain the required files and if I close the form the list appears blank again.
    Click image for larger version. 

Name:	error.PNG 
Views:	15 
Size:	6.8 KB 
ID:	19789
    Click image for larger version. 

Name:	rowsource.PNG 
Views:	15 
Size:	14.2 KB 
ID:	19790

  9. #9
    Join Date
    Jan 2015
    Posts
    17
    when I try your code I get this error:
    243CCA04 (2).zip
    I attach the form with your code. Thanks for all the help
    Attached Thumbnails Attached Thumbnails error1.PNG  

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You have to add a reference to Microsoft DAO 3.6 Object Library (I think that's the correct name) and, possibly, the MS word object library too but I can't recall the name of it off the top of my head.

    Secondly, if your list of files is a value list (you have to update it for each new file) you're crazy! You can get filesystem object to cycle through files in a folder and list them either in a combo box or list box and then select the ones you want to process.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-01-2013, 01:43 PM
  2. Transfer data from access to word
    By TOMMY.MYERS668 in forum Programming
    Replies: 11
    Last Post: 02-23-2013, 08:45 AM
  3. Difficult Word/Access transfer
    By DannyM93 in forum Import/Export Data
    Replies: 4
    Last Post: 06-12-2012, 02:36 PM
  4. Transfer data off the form to word micorsotf
    By BorisGomel in forum Forms
    Replies: 4
    Last Post: 05-29-2012, 01:37 PM
  5. Open a word dotx from acess
    By frcastro in forum Access
    Replies: 9
    Last Post: 06-17-2010, 12:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums