Using MS Access and VBA to update (Field codes: Database) field in Ms Word Document

Question

Ms Word Database field inserts the results of a database query in a Word table.

I want to populate a Table in ms word using VBA , I already did using VBA MsAccess & MsWord bookmark , the template from the data make the document very difficult to maintain. I found MSword have the option insert database , and allow you to actualize the data in msaccess and preserve the preformating during updates . The following Database field code results from a query from my Access database through ODBC using the Database command:

{ DATABASE \d "C:\My Data Sources\To test World.odc" \c "Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=\"DSN=MS Access Database;DBQ=H:\CIP BOOK\CIP2.accdb;DefaultDir=H:\CIP BOOK;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;\";Initial Catalog=H:\CIP BOOK\CIP2.accdb" \s "SELECT Description, TA, Entry Title, Memo FROM qryTransactions ExtendedWordFill_test WHERE ((Project_id = 'PCP13ZOOA06'))" \l "26" \b "191" \h * MERGEFORMAT }

First my wdFieldDatabase have Fields(Index), where Index is the index number(Field object). The index number represents the position of the field in the selection, range, or document

Second I find a Fields.Add Method (Word) This example if to add a field ListNum

 Selection.Collapse Direction:=wdCollapseEnd 
ActiveDocument.Fields.Add Range:=Selection.Range, _ 
 Type:=**wdFieldListNum**, Text:="\s 3"

What I want is tu update using VBA the Code in (Field codes: Database field) base on the index location and change the ( Project_id = 'PCP13ZOOA06). Other alternative can be add the field Database in specific range , location of the document using creating the code in VBA. I want to manage this from access , the idea is to create status reports with tables of expense for more than 2oo projects.

Field codes: Database field

z32a7ul z32a7ul · Accepted Answer · 2017-04-07T12:06:58

I can't test it right now, but wouldn't something like this work for you?

Option Explicit

Public Sub UpdateDatabaseField()
    Dim fldTarget As Field: Set fldTarget = ActiveDocument.Fields(1) ' TODO: Find your field based on your knowledge of your document
    Dim strProjectIdNew As String: strProjectIdNew = "123456" ' TODO: Determine new project code

    Dim strCodeOld As String: strCodeOld = fldTarget.Code.Text
    Dim ixBegin As Long: ixBegin = InStr(1, strCodeOld, "Project_id") + Len("Project_id")
    Dim ixEnd As Long: ixEnd = InStr(ixBegin, strCodeOld, ")")
    Dim strCodeNew As String: strCodeNew = Left(strCodeOld, ixBegin) & "='" & strProjectIdNew & "'" & Mid(strCodeOld, ixEnd)
    fldTarget.Code.Text = strCodeNew
End Sub

The final code:

Public Sub UpdateDatabaseField()
Dim fldTarget As Field: Set fldTarget = ActiveDocument.Fields(1) ' TODO: Find your field based on your knowledge of your document
Dim strProjectIdNew As String: strProjectIdNew = "PCP13ZOOA08" ' TODO: Determine new project code

Dim strCodeOld As String: strCodeOld = fldTarget.Code.Text
Dim ixBegin As Long: ixBegin = InStr(1, strCodeOld, "Project_id") + Len("Project_id") - 0
Dim ixEnd As Long: ixEnd = InStr(ixBegin, strCodeOld, ")")
Dim strCodeNew As String: strCodeNew = Left(strCodeOld, ixBegin) & "='" & strProjectIdNew & "'" & Mid(strCodeOld, ixEnd)
fldTarget.Code.Text = strCodeNew
ActiveDocument.Fields(1).Update

End Sub

Using MS Access and VBA to update (Field codes: Database) field in Ms Word Document

1 Answers