Problem when exporting data from access to word document with bookmarks

[Alex123]

i have created this code within one of my forms as a command button. i aleady created a word document with bookmarks called FACULTY.doc and when the button is pressed the data which is in the active form are exported to the word document but the data from the field Housename doesn't get exported since its not a field in the active form but a field in a related table.

So does anyone know how to get the data from a field in a related table to be export to a word document within a form which doesn't contain that field?

Here's the code:

Private Sub Command36_Click()
Dim wdApp As Word.Application, wdDoc As Word.Document

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open("C:\Users\Alex\Desktop\FACULTY.doc")

With wdApp.ActiveDocument.Bookmarks
.Item("Firstnamebm").Range.Text = [Firstname]
.Item("Surnamebm").Range.Text = [Surname]
.Item("Housenamebm").Range.Text = [Housename]

End With
End Sub

 

[beckyr]

Dont know if this is the best way to do it but i recently had something similar. I got about it by creating a variable that looked up the value i wanted (using a dlookup) and then inserted that value into the bookmark.

 

[Alex123]

it is possible for you to show me how you went about using a dlookup please ?

because im not that great at vb

 

[beckyr]

Sure i'll show you what i did and then you can adapt it to your tables. Firstly i declared replacedept as a string. Then the following dlookup found the department name from tblDepartment where the department number was equal to the number on the form. So maybe you could look up the housename from the table its in where the surname on the form is equal to the surname in the house table??

replacedept = DLookup("[DP_NAME]", "tblDepartment", "[DP_NO] =" & Forms![frmTutor]!TU_DP_NO)

Then i just used this to insert the department name:
With wdApp.ActiveDocument.Bookmarks
.Item("bmk1").Range.Text = replacedept
end with

Hope this helps!

 

[Alex123]

i tried the dlookup but for some reason i get the error '2001 you canceled the previous operation'

this is what it looks like:

Dim bri As String

bri = DLookup("[Housename]", "Application", "[Surname] = " & Forms![License]![Surname])

Housename, surname are text fields.

 

[beckyr]

Think it has something to do with quotation marks. Im not the best with this stuff but maybe try:

"[Surname] = '" & Forms![License]![Surname] & "'")