CODEWITHSUNDEEP
ms-accessms-wordvba
Ian Jackson
Ian Jackson

Reputation: 41

Writing Linked Table Data to Word (from Access)

I modified some code I saw which opens a word template and populates it by replacing bookmarks with the current record. Everything works fine until I try to write content from a linked (lookup) table, e.g.

Table A:

ID
Name
Description
JobType (lookup drop down list by JobTypeID in Table B)

Table B:

JobTypeID
JobType

Form C:

ID
Name
Description
JobType

This all works as planned in the form, and works for most when I click a button with:

'code for connecting to word and opening templates works fine *snip*

With WordApp.Selection
    .Goto what:=wdGoToBookmark, Name:="bmName"
    .TypeText [Name]

But when I use:

With WordApp.Selection
    .Goto what:=wdGoToBookmark, Name:="bmJobType"
    .TypeText [JobType]

it doesn't write the job type thats stored in the table or displayed in the drop down box on the form.... it writes the JobTypeID (1,2,3,4....) used in the link.

Ive also tried using me.JobTypeComboBox.value from the form, but this also displays the ID. Can someone point me in the right direction please.

Upvotes: 2

Views: 290

Answers (1)

HansUp
HansUp

Reputation: 97101

A lookup field stores one value but displays a different value. In your case, you seem to want the displayed value instead of the stored value. Two ways to deal with that ...

  1. Use a query where you INNER JOIN Table B to Table A. Then including [Table B].JobType in the query SELECT list should give you what you want.
  2. Use a DLookup expression to retrieve the [Table B].JobType value for the current [Table A].JobType

The situation is confusing because [Table A].JobType is actually a number which corresponds to [Table B].JobTypeID. And in [Table B], the field named JobType is something else ... perhaps a text field.

Hopefully those details are less confusing for you than they are for me. If this was my application, I would rename [Table A].JobType to [Table A].JobTypeID and leave it as a simple numeric field, not a lookup field. And probably also create a relationship between the 2 tables based on JobTypeID.

Upvotes: 1

Related Questions