How to populate word/fill Word from multiple tables using Access VBA

Question

I'm an Access novice; I customized the code below. The problem is it prints the primary key instead of the name of the field referred to.

For example, in one statement I want Institution Name to be printed in Word but instead it returns the primary key from a particula rinstitute in my table.

FormFields("txtNQFLevel").Result = Me.NQFLLEVEL I would like this field from another table but in the same query to return string values referred not its opposite primary key value

FormFields("txtInstitute").Result = Me.Institute_Name I would like this field which is from another table but in the same query to return string values referred not its opposite primary key value

Function BursaryContractYear2()
    Dim appWord As Word.Application
    Dim doc As Word.Document
    Dim Path As String

    On Error Resume Next
    Error.Clear

    Path = "C:\Users\Motlatsi Motlhamme\Desktop\FillWordAccess.docx "
    Set appWord = GetObject(, "Word.Application")
    If Err.Number <> 0 Then
        Set appWord = New Word.Application
        appWord.Visible = True
    End If

    Set doc = appWord.Documents.Open(Path, , True)
    With doc
        .FormFields("txtFirstName").Result = Me.FIRST_NAMES
        .FormFields("txtSurname").Result = Me.SURNAME
        .FormFields("txtNQFLevel").Result = Me.NQFLLEVEL
        .FormFields("txtInstitute").Result = Me.Institute_Name
        appWord.Visible = True
        appWord.Activate
    End With

    Set doc = Nothing
    Set appWord = Nothing

End Function

It's best not to use `On Error Resume Next`... in general, but especially while troubleshooting a problem. You're telling VBA is ignore all errors. — ashleedawg, Aug 01 '18 at 11:25
You don't provide enough information. What is `Me.`? Is it a Form? — Cindy Meister, Aug 01 '18 at 12:19

score 0 · Answer 1 · answered Aug 01 '18 at 11:25

The data being inserted into the txtInstitute field in the Word document is Me.Institute_Name. Me refers to the context the VBA code is running in, which your question doesn't identify. However, the fact that you are getting primary keys in your Word document suggests that Institute_Name is the name of a column in the table bound to the current Access context, which contains a foreign key to an Institutes table.

In Access, it might look like the Institute_Field column contains a name, not a primary key. Access calls this a lookup field. Lookup fields can be easier to understand than the traditional database concept, foreign keys. But when you're working under the hood of a lookup field, eventually you need to understand the foreign key relationship that Access has been hiding from you.

If the name you want is found in the Name column of the Institutes table, you can probably fix your code by changing that line to:

.FormFields("txtInstitute").Result = DLookup("Name", "Institutes", "ID = " & Me.Institute_Name)

If you need to perform this procedure many times, you should be aware that it is inefficient because of the N+1 SELECT query issue. The more efficient way to achieve this result is to ensure that Me is bound to a context, like a joined table, which contains the data you want.

How to populate word/fill Word from multiple tables using Access VBA

1 Answers1