Solved

Stuck with a Word field problem in Access VBA

Posted on 2009-05-06
8
459 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now