Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Taking a Word form and using it to fill in parts of an Access form

Discussion in 'Information Technology' started by AmandaH, Jul 28, 2006.

  1. AmandaH

    AmandaH
    Expand Collapse
    Guest

    Hello all,

    I have a survey that is going to be passed are to the public. The
    Survey was created in Word 2003 using form fields and is protected. I
    have also created an Access form with the same questions and fields as
    the word. What I need to know is, is it possible to take the
    information from the Word form and use it as an entry into the Access
    form? Will we have to use data entry (A person manually putting in the
    data)? I know that this can be done using Excel and Bookmarks that link
    them together. Is there a similar method in Access?

    Thanks,
    ~Amanda~
     
  2. Loading...


  3. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Amanda:

    You would not insert the data directly into an Access form but into a table
    on which a form can be based. Each Word document would be one row in the
    table, so you could then analyze the data in Access. First you should create
    a reference in your Access databse to the Microsoft Word Object Library which
    you do by selecting Tools|References on the VBA menu bar while in your Access
    database. In the dialogue scroll down until you find the reference then
    check it.

    To add the data to the table you can create a procedure in a standard module
    in the databse. The following procedure illustrates a very simple example
    where data from a Word document with two form fields is inserted into a table
    called MyContacts with columns FirstName and LastName:

    Sub GetWordForm(strPath As String)

    On Error GoTo Err_Handler

    Dim objWord As Object
    Dim objDoc As Object
    Dim fld As Word.FormField
    Dim cmd As ADODB.Command
    Dim frm As Form
    Dim strSQL As String
    Dim strValueList As String

    ' if Word open return reference to it
    ' else establish reference to it
    On Error Resume Next
    Set objWord = GetObject(, "Word.Application")
    If Err.Number = 429 Then
    Set objWord = CreateObject("Word.Application")
    End If

    AppActivate "Microsoft Word"
    On Error GoTo Err_Handler

    Set objDoc = objWord.Documents.Open(strPath)

    ' loop through form fields in document and build value list
    For Each fld In objDoc.FormFields
    strValueList = strValueList & ",""" & fld.Result & """"
    Next fld
    ' remove leading comma
    strValueList = Mid(strValueList, 2)

    ' insert row into table
    strSQL = "INSERT INTO MyContacts(FirstName,LastName)" & _
    "VALUES(" & strValueList & ")"
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdText
    cmd.CommandText = strSQL
    cmd.Execute

    objDoc.Close
    Set objDoc = Nothing
    Set objWord = Nothing

    Exit_here:
    On Error GoTo 0
    Exit Sub

    Err_Handler:
    MsgBox Err.Description & " (" & Err.Number & ")"
    Resume Exit_here

    End Sub

    You'd call it by passing the path to the Word document to the procedure, e.g.

    GetWordForm "F:\SomeFolder\SomeSubFolder\SomeDocument.doc"

    You could do this in the Click event procedure of a button on a form say.
    If you do it with a buton on a form bound to the table you can add the
    following line to the procedure before the Exit Here: label:

    Me.Requery

    This would requery the form so it includes the new record in its underlying
    recordset.

    You would not hard-code the path of course in reality, but get it from
    somewhere as a variable. Opening a common dialog to browse to the file would
    be the obvious solution. I use Bill Wilson's freely available
    BrowseForFileClass class module, which can be downloaded from:

    http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=22415&webtag=ws-msdevapps

    Ken Sheridan
    Stafford, England

    "AmandaH" wrote:

    > Hello all,
    >
    > I have a survey that is going to be passed are to the public. The
    > Survey was created in Word 2003 using form fields and is protected. I
    > have also created an Access form with the same questions and fields as
    > the word. What I need to know is, is it possible to take the
    > information from the Word form and use it as an entry into the Access
    > form? Will we have to use data entry (A person manually putting in the
    > data)? I know that this can be done using Excel and Bookmarks that link
    > them together. Is there a similar method in Access?
    >
    > Thanks,
    > ~Amanda~
    >
    >
     
  4. AmandaH

    AmandaH
    Expand Collapse
    Guest

    WOW thanks for that I am sure that it is going to work, except for:

    Dim cmd As ADODB.Command

    I get an error when I try to compile this line of code. It says
    "user-defined-type not define" any ideas?
     
  5. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Re: Taking a Word form and using it to fill in parts of an Access

    Amanda:

    It sounds like you don't have a reference to the ADO object library. Select
    Tools|References on the VBA menu bar and scroll down to the Microsoft ActiveX
    Data Objects Library. Check it and exit the dialogue. If you still have
    problems I can let you have an amended version of the code using DAO which
    was the default data access technology in Access before Access 2000, but can
    still be used with later versions.

    "AmandaH" wrote:

    > WOW thanks for that I am sure that it is going to work, except for:
    >
    > Dim cmd As ADODB.Command
    >
    > I get an error when I try to compile this line of code. It says
    > "user-defined-type not define" any ideas?
    >
    >
     
  6. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Re: Taking a Word form and using it to fill in parts of an Access

    Amanda:

    One other thing I should have mentioned is that the code I sent you assumes
    all the fields in the table which are being filled with data from the form
    fields are of text data type. This is why each value in the value list is
    wrapped in quotes. If the fields being filled were of mixed data types, some
    text, some numbers , some dates say, then it would be necessary to amend the
    code to take account of this. Rather than simply building a uniform value
    list you'd need to build one where the delimiters for each field were of the
    right type for the fields data type, quotes for text, # signs for dates and
    none for numbers.

    Ken Sheridan
    Stafford, England

    "AmandaH" wrote:

    > WOW thanks for that I am sure that it is going to work, except for:
    >
    > Dim cmd As ADODB.Command
    >
    > I get an error when I try to compile this line of code. It says
    > "user-defined-type not define" any ideas?
    >
    >
     
  7. AmandaH

    AmandaH
    Expand Collapse
    Guest

    Re: Taking a Word form and using it to fill in parts of an Access

    Ken Sheridan wrote:
    > Amanda:
    >
    > One other thing I should have mentioned is that the code I sent you assumes
    > all the fields in the table which are being filled with data from the form
    > fields are of text data type. This is why each value in the value list is
    > wrapped in quotes. If the fields being filled were of mixed data types, some
    > text, some numbers , some dates say, then it would be necessary to amend the
    > code to take account of this. Rather than simply building a uniform value
    > list you'd need to build one where the delimiters for each field were of the
    > right type for the fields data type, quotes for text, # signs for dates and
    > none for numbers.
    >
    > Ken Sheridan
    > Stafford, England
    >


    How would a statments like that work?
     
  8. AmandaH

    AmandaH
    Expand Collapse
    Guest

    Re: Taking a Word form and using it to fill in parts of an Access

    Ken Sheridan wrote:
    > Amanda:
    >
    > One other thing I should have mentioned is that the code I sent you assumes
    > all the fields in the table which are being filled with data from the form
    > fields are of text data type. This is why each value in the value list is
    > wrapped in quotes. If the fields being filled were of mixed data types, some
    > text, some numbers , some dates say, then it would be necessary to amend the
    > code to take account of this. Rather than simply building a uniform value
    > list you'd need to build one where the delimiters for each field were of the
    > right type for the fields data type, quotes for text, # signs for dates and
    > none for numbers.
    >
    > Ken Sheridan
    > Stafford, England
    >


    How would a statments like that work? What would it look like
     
  9. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Re: Taking a Word form and using it to fill in parts of an Access

    Amanda:

    As you are dealing with the same set of form fields and the same table each
    time you will know the number of columns in the table and data type of each
    one, so as you loop through the FormFields collection of the Word Document
    you can increment an integer variable n. You can then examine the value of n
    each time and wrap the value in the appropriate delimiters. Extending my
    example so that the document has 4 form fields and the table has 4 columns
    FirstName, LastName, DateOfBirth and Salary the first two are text data type,
    the third date/time and the last a number, so the code would be amended like
    so:

    Sub GetWordForm(strPath As String)

    On Error GoTo Err_Handler

    Dim objWord As Object
    Dim objDoc As Object
    Dim fld As Word.FormField
    Dim cmd As ADODB.Command
    Dim frm As Form
    Dim strSQL As String
    Dim strValueList As String
    Dim n As Integer

    ' if Word open return reference to it
    ' else establish reference to it
    On Error Resume Next
    Set objWord = GetObject(, "Word.Application")
    If Err.Number = 429 Then
    Set objWord = CreateObject("Word.Application")
    End If

    AppActivate "Microsoft Word"
    On Error GoTo Err_Handler

    Set objDoc = objWord.Documents.Open(strPath)

    ' loop through form fields in document and build value list
    For Each fld In objDoc.FormFields
    n = n + 1
    Select Case n
    Case 1, 2 ' text field so delimit with quotes
    strValueList = strValueList & ",""" & fld.Result & """"
    Case 3 ' date field so delimit with hashes and
    ' format date in US short date format
    strValueList = strValueList & ",#" & _
    Format(fld.Result, "mm/dd/yyyy") & "#"
    Case 4 ' number field so no delimiters
    strValueList = strValueList & "," & fld.Result
    End Select
    Next fld
    ' remove leading comma
    strValueList = Mid(strValueList, 2)

    ' insert row into table
    strSQL = "INSERT INTO MyContacts(FirstName,LastName, DateOfBirth,
    Salary)" & _
    "VALUES(" & strValueList & ")"
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdText
    cmd.CommandText = strSQL
    cmd.Execute

    objDoc.Close
    Set objDoc = Nothing
    Set objWord = Nothing

    Exit_here:
    On Error GoTo 0
    Exit Sub

    Err_Handler:
    MsgBox Err.Description & " (" & Err.Number & ")"
    Resume Exit_here

    End Sub

    Note that the date is also formatted. This is because date literals in
    Access must be in US date format or otherwise internationally unambiguous.
    This is important to us Yurpeans, who unlike Mercans, use dd/mm/yyyy as our
    standard short date format, so without formatting Access would interpret 4
    July here as 7 April. For use with US formatted dates the formatting is not
    actually necessary, but it does no harm and it internationalizes the
    application if you leave it in.

    By looping through the FormFields collection in order it is necessary for
    the columns in the column list in the SQL statement to be in the same order
    of course.

    Ken Sheridan
    Stafford, England

    "AmandaH" wrote:
    >
    > How would a statments like that work? What would it look like
    >
    >
     

Share This Page