Solved

Stuck with a Word field problem in Access VBA

Posted on 2009-05-06
8
461 Views
Last Modified: 2013-11-28
Hi

I am writing an app which replaces, in a Word document, Word fields with corresponding data fields from an Access DB. My code is running from Access. It opens the Word document, goes through all fields in turn and gets their values. That works perfectly well... until I decided to thry to solve a slightly extended problem.

My setup was: all Word fields are strightforward MERGEFIELD fields of the type:
  { MERGEFIELD <symbolic DB table and fieldname> \* MERGEFORMAT }
and what I did is extract the symbolic DB table and fieldname from the Word field through parsing the whole field.Code.Text, find the DB field, and then just say:
 field.Result.Text = <DB field content>
(where field is a variable of type Word.Field in a For Each field loop)
Works perfectly.

But I have a couple of cases where the result is not just the value of the DB field, but rather of the type:
  if DB field value = "Yes"  then Word field = "car" else Word field = "bike"

No problem, I thought, there is in Mailmerge not only MERGEFIELD, but also IF as keyword. So I built a Word field like this:
  { IF { MERGEFIELD vehicletype } = "Yes" "car" "bike" \* MERGEFORMAT }
Then I extracted the vehicletype just as above and accessed the field in my DB and... NO WAY will the Word field say "car" if the DB field value is "Yes" ! The result is ALWAYS "bike", i.e. as if the vehicletype is not defined and therefore False. I can even see that the value of the inner MERGEFIELD is "Yes", but the IF still doesn't select the True answer !

Can anyone help me with that one ? This is highly irritating, and I must be close to the solution, but I just can't see were the mistake is.
The thing that puzzles me though is that the "For each field" loop does NOT detect the inner { MERGEFIELD vehicletype } as a Word field, it only detects the outer IF Word field. Is that part of the problem ?

Thanks
Bernard
0
Comment
Question by:bthouin
  • 4
  • 2
  • 2
8 Comments
 
LVL 4

Expert Comment

by:paisleym
ID: 24321007
Hi

Although the field type in Access is Yes/No, it is actually stored as -1 / 0 thus in queries and vba you need to test for -1

hth
Marcelle
0
 
LVL 1

Author Comment

by:bthouin
ID: 24322739
Thanks, Marcelle, I know that, but that's not the problem. The particular DB field I'm trying this IF business on is not a Boolean, it is a text field having the values "T or "F" for True or False in the DB, and after reading it I change it (in a low level routine) to "Yes" or "No", and that's before setting the Word field with field.Result.Text. The reason for the Yes/No is that there are many of these "T"/"F" fields in the DB, and in most cases a Yes or No is the right text to show in Word)

Bernard
0
 
LVL 4

Expert Comment

by:paisleym
ID: 24323016
Hi Bernard

So is vehicletype the database field value, i.e. { MERGEFIELD vehicletype }, or the converted field value?

Marcelle
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:bthouin
ID: 24323528
vehicletype is the database field value.

Maybe to make things clearer, let me explain exactly what I do:
- the Word field originally is { IF { MERGEFIELD DB1.Tb1.vehicletype } = "Yes" "car" "bike" \* MERGEFORMAT }
- when I get to that field in my loop on all Word fields, I:
  - extract the text of the field (through field.Code.Text), trim it, replace any double spaces by single spaces, so I get "IF { MERGEFIELD DB1.Tb1.vehicletype } = "Yes" "car" "bike" \* MERGEFORMAT"
  - I check on IF as keyword. If set, I skip the IF, eliminate the curly brackets keep the rest, so I have "MERGEFIELD DB1.Tb1.vehicletype = "Yes" "car" "bike" \* MERGEFORMAT"
  - I split that using Split(rest, " ")
  - I extract the database stuff, DB1.Tb1.vehicletype
  - split that again to get the DB name (DB1), the table name (Tb1), and the field name (vehicletype)
  - I then make a normal SELECT on that table in that database, and get the vehicletype field value (it's slightly more complex than this, as I have to have the row key, but tha'ts irrelevant for this discussion)
  - the SELECT statement returns a "T" value
  - I map/change that to "Yes"
  - I set that value in the Word field by using field.Return.Text = <mapped DB field value, i.e. "Yes">
  - the field.Return.Text is effectively "Yes" (dixit debug.print),  so it looks like the IF says "IF "Yes" = "Yes" ", so I'd expect "car" to be shown in Word, but the text appearing as a result of all this is always the "condition false" text of the IF, i.e. "bike"

Where is the error ?

Bernard
0
 
LVL 23

Expert Comment

by:irudyk
ID: 24325021
From tests that I have done, it would appear that the value you set in the merge field does not hold after it is set and the IF field is updated thereafter.  After your step:
- I map/change that to "Yes"
you could try the following:
- select the field
  e.g. field.Select
  to get cursor in the desired location
- delete the field
  e.g. field.Delete
- type the text you retrieved from your database
  e.g. ActiveDocument.ActiveWindow.Selection.TypeText <mapped DB field value>
Then when the IF field is updated, you should get the results you are looking for.
 
0
 
LVL 1

Author Comment

by:bthouin
ID: 24340049
Well, if you tell me how I canselect a field within a field with VBA code, I'll gladly do what you say. The thing is that my VBA code which loops on all Word fields ONLY finds the IF field, but not  the MERGEFIELD which is WITHIN the IF field, that's why I'm using code to parse the whole IF field content, including the MERGEFIELD field. So if I say field.select and field.delete, I guess that will select and delete the IF field, and therefore my whole IF field will be gone and therefore no results achieved...

Any thoughts ?
0
 
LVL 23

Accepted Solution

by:
irudyk earned 250 total points
ID: 24342049
You say you are looping through all the fields in the document, so this looping your code does should eventually get to the MERGEFIELD field that is within the IF field.  It'd be useful if you would post the code you are using so that there is something specific to your situation upon which to build upon/alter.
0
 
LVL 1

Author Comment

by:bthouin
ID: 24547238
Hi
Sorry for long pause, but I had all sorts of other worries for a while. You gave me the right hint without me realising right away. You said: "this looping your code does should eventually get to the MERGEFIELD field that is within the IF field". That made me realise after a while that my code tried to handle everything when encountering the IF field, instead of IGNORING the IF and only handling MERGEFIELDs first, and updating all fields at the end of the whole processing (i.e. after all MERGEFIELDs were processed), so that the IF would be triggered and yield a result ! So you gave me the solution and you get the points :-).

Thanks
Bernard
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question