Macro in a field

[Sharon]

I want to create a If.. Then . . Else . . that inserts a
macro.

For example:

If 2Name is not blank insert . . . (I want this to be a
macro).

Thanks.

 

[Graham Mayor]

What do you mean by 'insert a macro?' Macros are pieces of code that run in
the background to perform tasks. They are not things that can be inserted in
documents. What are you trying to achieve?

--
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>
Graham Mayor - Word MVP
E-mail (e-mail address removed)
Web site www.gmayor.com
Word MVP web site www.mvps.org/word
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>

 

[Peter Jamieson]

Assuming that by "inserting a macro" what you mean is that you want Word to
run the macro and insert its results, there is no built-in facility to do
that. However, there are ways it can be done, for example:
a. if you have Access, you can set up an Access query as the data source
for either a mailmerge or a DATABASE field. If you connect using DDE you can
call user-defined functions (i.e. functions you write in Access VBA) and
return the result. If you only return a 1-row, 1-column result and do not
display headers in the DATABASE field, the DATABASE field will display a
single value rather than the table it usually displays.
b. You can do (a), but simply create expressions using standard VBA
functions in the query. You don't have to create Access VBA functions. And
that way you can connect using ODBC (or OLEDB in Word 2002 or later) rather
than DDE
c. You can do (b), but just put the query string in the DATABASE field, if
you have ODBC on your system and create a suitable .dsn file to be the "data
source" for the field. There does not even need to be a .mdb in that case. I
can provide an example if you like
d. If you are doing a mailmerge (you don't say whether you are doing one or
not) In word 2002 and later you could consider using the mailmerge events
and VBA to stuff the value you want into the mail merge main document prior
to merging each record. Not quite what you are asking for but it could
achieve the same result.

 

[sharon]

Let me be more specific so you can advise me which way is
the best (it is kind of over my head here lol).

I work for a patent law firm. Some patents have 1
Inventor, others 2, 3, 4, it depends on the patent.

I have an Access database that has 1Inventor, 1Address,
1City . . 2Inventor, 2Address, 2City . . . etc. (all the
way to 10).

In one of my Word forms there is a table that has borders
showing, etc. for the 1Inventor information. Another
table for the 2Inventor and so on. I am trying to do an
If . . Then . . function on the Word form so that if
2Inventor is not blank then insert the table for
2Inventor. If the 3Inventor is not blank then insert the
table for 3Inventor. The only way that I could think to
insert the table showing the table borders was that I
have created a Word document that has the table in it for
2Inventor; another document with the table for 3Inventor
and so on.

So, I am trying to "If 2Inventor is not blank then (run
macro to insert the 2Inventor table document).

Does any of this make sense?

I appreciate your help. I feel like I have "my eyes are
bigger than my stomach" syndrome, but instead it is "my
visons are bigger than my brain." lol

 

[Graham Mayor]

We don't need macros for this, but what do the tables contain?
You could for example use autotext to insert the tables, depending on the
result of a conditional field. You can even include a surprising amount of
formatting in the result section of a conditional field.

eg

{IF {Mergefield 2inventor}<> "" "Put your table here followed and press the
enter key after it
"}{IF {Mergefield 3inventor}<> "" "Put your table here followed and press
the enter key after it
"}{IF {Mergefield 4inventor}<> "" "Put your table here followed and press
the enter key after it
"}

The tables will only appear if the fields are not empty.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>
Graham Mayor - Word MVP
E-mail (e-mail address removed)
Web site www.gmayor.com
Word MVP web site www.mvps.org/word
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>

 

[Sharon]

I have been able to get the information of the table in
the conditional field, but I can't get the actual format,
borders, etc. of the table.

 

[Graham Mayor]

Hopefully someone else will pick up your further questions as I am not going
to be around for a couple of months, but a way forward would be to place the
tables manually, save them as autotext entries and use the conditional field
to place them as autotext fields. Eg {IF {Mergefield 2inventor}<> ""
"{Autotext autotextname}
"}


--
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>
Graham Mayor - Word MVP
E-mail (e-mail address removed)
Web site www.gmayor.com
Word MVP web site www.mvps.org/word
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Sharon,

See my response of 15 January to the thread that you started on 13 January
in this newsgroup.

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

 

[Cindy M -WordMVP-]

Hi Sharon,

I have been able to get the information of the table in
the conditional field, but I can't get the actual format,
borders, etc. of the table.

I can follow up with you if Graham's last suggestion hasn't
helped.

My first impression is that 1. The Access database is not
set up correctly, so that 2. you can't put together the
tables correctly in Word (using the Database field Peter
mentioned).

Alarm bells will go off in any relational database person's
head when somewhen mentions fields with names such as
City1, City2, City3, etc. This usually indicates that the
data should be broken down into additional tables. For
example, you probably have a table right now for "Patents"?
Then you need another table that lists "Inventors", where
this individual information would go. The "Patents" table
should only contain information specific to the patent,
itself.

Then you'd need yet a third table that brings the ID field
for the patent together with the ID field for the Inventor,
plus any information that's specific to the combination of
Inventor+Patent (such as the percent ownership).

This is how relational databases are meant to be used, and
it has a number of advantages. For one thing, if the same
inventor is concerned with more than one patent, you don't
have to retype the information! Another is, if you ever had
a patent with more than 10 inventors, you don't have to
redesign the database. You'll also be able to keep a better
overview of the different tables, since each table will have
fewer fields.

IOW, someone needs to learn a bit more about relational
database design, then put your database together more
optimally. After that, everything will fall much better into
place. If you want to pursue this in the newsgroups, an
Access newsgroup would be more appropriate

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[Sharon]

I realize that this is not usually done in relational
databases. I have been working with my Advanced Access
Instructor to create a database for my particular needs.
He established that because of the way the forms are
setup in Word, the only way to create this database and
have it function the way that I need it to is the way we
set it up. If you would like, I could e-mail (I don't
see a way to do this in this e-mail, but could do this to
your direct e-mail with your permission) to you a copy of
a "dummy" database that shows exactly what tables,
queries, forms, etc. that I have set up and one or two of
the forms that I am merging this information into from
Word. If someone else is able to find a better way to do
what I need done, I am all for it.

 

[Peter Jamieson]

Here are a few things you could try.

First, in simple cases it is possible to use a DATABASE field with a query
that correlates data from a "child" table with a "parent" table. Typically,
you use the parent table as the data source for a merge, then insert a
DATABASE field that selects some of the data from the child table, then
change the WHERE clause so that does e.g.

WHERE matching_child_column = '{ MERGEFIELD matching_parent_column }'

However, this approach has several drawbacks (you have to cope with the "0
records" case specially, getting the columns and titles you want can be
difficult, and you are limited to the built-in table formatting available
with the DATABASE field unless you postprocess the results.

Second, if your data contains a count of the number of rows you need, you
can use an INCLUDETEXT (or perhaps AUTOTEXT) field to include a table of a
particular size. e.g. you might have a field called table_rows, then use a
nested INCLUDETEXT such as

{ INCLUDETEXT "c:\\mytable\\rows{ MERGEFIELD table_rows }.doc" }

or you might have all the tables in one document and bookmark each of them

{ INCLUDETEXT "c:\\mytable\\rows.doc" "bm{ MERGEFIELD table_rows }" }

In this case you can either keep all the data in the denormalised state you
have at the moment, or revert to a multi-row solution where you use the
correct number of { NEXT } fields in the included tables.

The trouble is that in this case you can end up with similar formatting
problems to the ones where you insert the tables directly into an IF field.
I always assume that this results from the fact that two paragraph formats
that are potentially applicable - one from the paragraph in the including
document, then all the formatting of the paragraphs in the included
document.

Third, if you are using Word 2002 or later, you could consider using VBA and
Mailmerge events, have a table_rows field as in (b) (or otherwise determine
the number of rows), then use VBA to look at the value of table_rows and
stuff the correct table layout directly into the mail merge main document.
IMO that is probably the approach that is most likely to give you the output
format you need.

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Sharon,

What other use do you make of the information in the database? If you email
me a copy of the Word form that you use ([email protected]), I will take a look
at it. My personal opinion is that your Advanced Database Instructor has
gone about this backwards.

Regards,
Doug Robbins - Word MVP