Stuck with a Word field problem in Access VBA
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
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
ASKER
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
Bernard
Hi Bernard
So is vehicletype the database field value, i.e. { MERGEFIELD vehicletype }, or the converted field value?
Marcelle
So is vehicletype the database field value, i.e. { MERGEFIELD vehicletype }, or the converted field value?
Marcelle
ASKER
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
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
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.ActiveWindo
Then when the IF field is updated, you should get the results you are looking for.
- 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.ActiveWindo
Then when the IF field is updated, you should get the results you are looking for.
ASKER
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 ?
Any thoughts ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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