none
Help with VBA code to transfer data to/from Word/Access? RRS feed

  • Question

  • I have an Access form which acts as my main point of data entry. I need to add two buttons to this form; one to export data from the Access form to a Word form and one to import data from the Word form into the Access form. In both instances I need to import/export data for the record currently being viewed only. Ideally I'd simply work out of the Access form, but I need to email my Access form to customers in a "fillable" format and easily import the data they provide into my database.

    I'm really struggling with this. I found the following article helpful and sort of got somewhere: http:// www.techrepublic.com/blog/how-do-i-dynamically-fill-microsoft-word-fields-using-access-data/

    The code I've modified from the link to import data returns a 3464 error at Set rst = db.OpenRecordset(strSQL). I've triple checked all my reference names for my Access fields/tables and my Word form field bookmarks and they match my code. I'm assuming I making a simple mistake.

    Customer and Product are stored as text while PONumber is an Access generated autonumber.

    Sub FillDependentFields()

     

    Dim db As DAO.Database

    Dim was As DAO.Workspace

    Dim rst As DAO.Recordset

    Dim strSQL As String

    Dim strPath As String

    Dim doc As Document

     

    Set ws = DBEngine.Workspaces(0)

    Set doc = ThisDocument

    strSQL = "SELECT Customer, Product FROM PurchaseOrders " _

    & "WHERE PONumber = ' " & doc.FormFields("PONumber").Result & " ' "

     

    strPath = ("C:\MyFolder\MyDBName.accdb")

    Set db = ws.OpenDatabase(strPath, False, False, "MS Access;PWD=MyDBName")

    Set rst = db.OpenRecordset(strSQL)

     

    'Ignore Null values from Access data.

    On Error Resume Next

    doc.FormFields("Customer").Result = rst(0).Value

    doc.FormFields("Product").Result = rst(1).Value

     

    Set db = Nothing

    Set rst = Nothing

     

    End Sub

    In addition to correcting the 3464 error I need to add into the code to automatically attach the word document to a new Outlook email as can be done through a macro in Access. Is this possible?

    How about importing the data from my Word form to Access? How do I do this?

     






    • Edited by GaryBrt Wednesday, September 18, 2013 12:04 AM
    Wednesday, September 18, 2013 12:00 AM

All replies

  • Gary,

    This errors (3464) description tells that there is a type mismatch.

    In your code is that the numeric (Long) from PONumber in Access and the text from the formfields result.

    You could try this for the SQL string:

    strSQL = "SELECT Customer, Product FROM PurchaseOrders " _
    & "WHERE PONumber=" & Clng(doc.FormFields("PONumber").Result)

    Jan


    • Edited by jgkzdl Wednesday, September 18, 2013 5:23 PM
    Wednesday, September 18, 2013 1:44 PM