[AlecJames]

I want to build a document that is a specification.

There will be sections for introduction, requirements and appendicies.

I want each requirement in the requirements section to come from an Access
database (so it looks as if I had inserted an Access report into the word
document).

I will update / maintain the database and then requery from the word
document to release new versions of the specification. I will also be able
to have different views of the specification in different word documents that
query the same DB.

So in the word document I will have a layout that is repeated for each row
my query returns, with the format and style set in word.

Can this be done? I find that mail merge only offers me mailing type
documents, even the directory type wants a recipient list.

If I use Insert-Field-Database it is close to what I want but this is a
one off insertion (if the database is updated I have to delete / reinsert, I
want to update field to requery) and I cannot change the way that the data
is put in the table (a single table is used with one row for each query row,
I want a table per query row where the data is laid out on more than one
table row.)

Any suggestions?

Thanks
Alec

[AlecJames]

BTW I'm using Office 2003.

I've found that I can do what I want with the database tool bar functions.
I can design a table, insert database, then insert merge fields into the
table cells. Next problem -

My access query combines multiple tables - requirements + notes. each
requirement can have none or many notes which means I can have 1+ records for
each requirement. In the merge document I want to fill out my table with the
first record, then loop through the next records adding notes under my table.
Then fill out another table when the requirement ID changes. However,

I cannot find a way to loop while merging (no while, for each, etc.), can
this be done?
I cannot find a way to call vba while merging or create a user defined field
code, can this be done?

The only way I can see to do this is to use if then else and limit the
number of comments to the number of if then else nests I add.

Any suggestions?
Thanks
Alec

"AlecJames" wrote:

I want to build a document that is a specification.

There will be sections for introduction, requirements and appendicies.

I want each requirement in the requirements section to come from an Access
database (so it looks as if I had inserted an Access report into the word
document).

I will update / maintain the database and then requery from the word
document to release new versions of the specification. I will also be able
to have different views of the specification in different word documents that
query the same DB.

So in the word document I will have a layout that is repeated for each row
my query returns, with the format and style set in word.

Can this be done? I find that mail merge only offers me mailing type
documents, even the directory type wants a recipient list.

If I use Insert-Field-Database it is close to what I want but this is a
one off insertion (if the database is updated I have to delete / reinsert, I
want to update field to requery) and I cannot change the way that the data
is put in the table (a single table is used with one row for each query row,
I want a table per query row where the data is laid out on more than one
table row.)

Any suggestions?

Thanks
Alec

[Peter Jamieson]

There's a square peg/round hole aspect to trying to do this kind of
thing using either Mailmerge or Database fields. For example, the next
thing you'll probably be asking is how to get mailmerge to do the
automatic paragraph numbering you want your specification to have.
(well, OK, that's obviously a guess, but if you do want to do that it
probably won't be easy.)

You can do a certain amount of parent/child record processing using
merge - have a look at macropod's tutorial on the subject at
http://www.wopr.com/index.php?showtopic=731107

That may be enough to point you where you need to go, but IMO you may be
better off thinking in terms of retriving the records you need using
e.g. Word VBA and ADO, and inserting them one by one into your document.
You'll have far more control over what is going on.

I cannot find a way to loop while merging (no while, for each, etc.),
can
this be done?

you can't really loop using the field language, no. What macropod's
tutorial shows is just about the best you can do purely with fields.

The other field-based technique requires you to have a "parent" table as
the mail merge data source and a child table inserted using a DATABASE
field. By referencing the parent table's key fields in the WHERE clause
of the SQL statement in the DATABASE field, you can restrict the child
records to be the ones that are chidren of the record currently being
merge. But you get little control over table layout, there can be naming
clashes between the merge and DATABASE field data sources, and so on.


I cannot find a way to call vba while merging

Yes, you can use Word's MailMerge Events to invoke VBA as each data
source record is processed, and so on. However, because merge needs a
single "flat file" as a data source, you typically end up having to
create a query that generates a denormalised set of records, then you
have to Event code to detect where the records belonging to one parent
record end and another begins, and /that/ can be tricky too.

A viable combination might be to have a Mail Merge with the parent table
as the data source, + MailMerge Event processing that uses ADO to fetch
child records belonging to each parent record,

However, I suspect that going for a non-Mailmerge solution will end up
being simpler.

or create a user defined field
code, can this be done?

It depends on what you mean. You can use VBA to create Document
variables, which have a name and a value, and which persist in the
document. You can then insert these values - with some limitations using
{ DOCVARIABLE fields }. You can have a heck of a lot of Document
Variables (the practical limit has more to do with the overall document
size and processing slowdowns). However, you can't for example have a
field code that does something "new", such as executing an arbitrary
piece of Word VBA code. (Well, you can, or you used to be able to, but
you have to write a text format converter, learn to use RTF, live with
some nasty but not wholly unreasonable security constraints, and more or
less hijack the INCLUDETEXT field. But again, it's probably a whole lot
simpler to write some VBA to get the data and insert it with the
formatting you want.


Peter Jamieson

http://tips.pjmsn.me.uk

AlecJames wrote:
BTW I'm using Office 2003.

I've found that I can do what I want with the database tool bar functions.
I can design a table, insert database, then insert merge fields into the
table cells. Next problem -

My access query combines multiple tables - requirements + notes. each
requirement can have none or many notes which means I can have 1+ records for
each requirement. In the merge document I want to fill out my table with the
first record, then loop through the next records adding notes under my table.
Then fill out another table when the requirement ID changes. However,

I cannot find a way to loop while merging (no while, for each, etc.), can
this be done?
I cannot find a way to call vba while merging or create a user defined field
code, can this be done?

The only way I can see to do this is to use if then else and limit the
number of comments to the number of if then else nests I add.

Any suggestions?
Thanks
Alec

"AlecJames" wrote:

I want to build a document that is a specification.

There will be sections for introduction, requirements and appendicies.

I want each requirement in the requirements section to come from an Access
database (so it looks as if I had inserted an Access report into the word
document).

I will update / maintain the database and then requery from the word
document to release new versions of the specification. I will also be able
to have different views of the specification in different word documents that
query the same DB.

So in the word document I will have a layout that is repeated for each row
my query returns, with the format and style set in word.

Can this be done? I find that mail merge only offers me mailing type
documents, even the directory type wants a recipient list.

If I use Insert-Field-Database it is close to what I want but this is a
one off insertion (if the database is updated I have to delete / reinsert, I
want to update field to requery) and I cannot change the way that the data
is put in the table (a single table is used with one row for each query row,
I want a table per query row where the data is laid out on more than one
table row.)

Any suggestions?

Thanks
Alec

[AlecJames]

Thanks for your help Peter

It will take some time to work through the very useful suggestions you have
made.

I agree with you that Word VBA is a more elegent solution. I'm going
another way for 2 reasons - (1) I always wanted to try this (2) I have to
produce different views of the specification based on the same base data.

The word merge was getting complex because I had a simple access query to
give me tblRequirements and tblNotes linked by an requirement ID field. Then
I tried to sort out the result in word.
I've changed this by creating an access query and vba function that gives
me the data exactly as I need it in word. Now the word merge is simple with
no need for loops or other logic. (I must thank you again for posting
http://help.wugnet.com/office/Querie...rderasc-8.html which helped me get my query + vba working)

I see what you mean about para numbering. I changed the merge type to
'directory' and used a heading style with numbering. This seems to work.

I have one problem I'm struggling with - I have a merge field that is longer
than 255 characters (the first record of the query). It displays Ok up till
character 255 and then word shows ??? ??? ??? to the end of the string.
Any ideas as to why

Thanks
Alec

"Peter Jamieson" wrote:

There's a square peg/round hole aspect to trying to do this kind of
thing using either Mailmerge or Database fields. For example, the next
thing you'll probably be asking is how to get mailmerge to do the
automatic paragraph numbering you want your specification to have.
(well, OK, that's obviously a guess, but if you do want to do that it
probably won't be easy.)

You can do a certain amount of parent/child record processing using
merge - have a look at macropod's tutorial on the subject at
http://www.wopr.com/index.php?showtopic=731107

That may be enough to point you where you need to go, but IMO you may be
better off thinking in terms of retriving the records you need using
e.g. Word VBA and ADO, and inserting them one by one into your document.
You'll have far more control over what is going on.

I cannot find a way to loop while merging (no while, for each, etc.),
can
this be done?

you can't really loop using the field language, no. What macropod's
tutorial shows is just about the best you can do purely with fields.

The other field-based technique requires you to have a "parent" table as
the mail merge data source and a child table inserted using a DATABASE
field. By referencing the parent table's key fields in the WHERE clause
of the SQL statement in the DATABASE field, you can restrict the child
records to be the ones that are chidren of the record currently being
merge. But you get little control over table layout, there can be naming
clashes between the merge and DATABASE field data sources, and so on.


I cannot find a way to call vba while merging

Yes, you can use Word's MailMerge Events to invoke VBA as each data
source record is processed, and so on. However, because merge needs a
single "flat file" as a data source, you typically end up having to
create a query that generates a denormalised set of records, then you
have to Event code to detect where the records belonging to one parent
record end and another begins, and /that/ can be tricky too.

A viable combination might be to have a Mail Merge with the parent table
as the data source, + MailMerge Event processing that uses ADO to fetch
child records belonging to each parent record,

However, I suspect that going for a non-Mailmerge solution will end up
being simpler.

or create a user defined field
code, can this be done?

It depends on what you mean. You can use VBA to create Document
variables, which have a name and a value, and which persist in the
document. You can then insert these values - with some limitations using
{ DOCVARIABLE fields }. You can have a heck of a lot of Document
Variables (the practical limit has more to do with the overall document
size and processing slowdowns). However, you can't for example have a
field code that does something "new", such as executing an arbitrary
piece of Word VBA code. (Well, you can, or you used to be able to, but
you have to write a text format converter, learn to use RTF, live with
some nasty but not wholly unreasonable security constraints, and more or
less hijack the INCLUDETEXT field. But again, it's probably a whole lot
simpler to write some VBA to get the data and insert it with the
formatting you want.


Peter Jamieson

http://tips.pjmsn.me.uk

AlecJames wrote:
BTW I'm using Office 2003.

I've found that I can do what I want with the database tool bar functions.
I can design a table, insert database, then insert merge fields into the
table cells. Next problem -

My access query combines multiple tables - requirements + notes. each
requirement can have none or many notes which means I can have 1+ records for
each requirement. In the merge document I want to fill out my table with the
first record, then loop through the next records adding notes under my table.
Then fill out another table when the requirement ID changes. However,

I cannot find a way to loop while merging (no while, for each, etc.), can
this be done?
I cannot find a way to call vba while merging or create a user defined field
code, can this be done?

The only way I can see to do this is to use if then else and limit the
number of comments to the number of if then else nests I add.

Any suggestions?
Thanks
Alec

"AlecJames" wrote:

I want to build a document that is a specification.

There will be sections for introduction, requirements and appendicies.

I want each requirement in the requirements section to come from an Access
database (so it looks as if I had inserted an Access report into the word
document).

I will update / maintain the database and then requery from the word
document to release new versions of the specification. I will also be able
to have different views of the specification in different word documents that
query the same DB.

So in the word document I will have a layout that is repeated for each row
my query returns, with the format and style set in word.

Can this be done? I find that mail merge only offers me mailing type
documents, even the directory type wants a recipient list.

If I use Insert-Field-Database it is close to what I want but this is a
one off insertion (if the database is updated I have to delete / reinsert, I
want to update field to requery) and I cannot change the way that the data
is put in the table (a single table is used with one row for each query row,
I want a table per query row where the data is laid out on more than one
table row.)

Any suggestions?

Thanks
Alec

[Peter Jamieson]

Hi Alec

I have one problem I'm struggling with - I have a merge field that is
longer
than 255 characters (the first record of the query). It displays Ok
up till
character 255 and then word shows ??? ??? ??? to the end of the string.
Any ideas as to why

Not in this case. Historically, there have been character-count limit
problems when Word gets data from Access memo fields (e.g. the limit for
ODBC connections was increased many years ago, I haven't researched the
Access limitations" problem anything like as much as the equivalent
problem in Excel. As far as I know, OLE DB connections to Access
retrieve memo fields complete with Unicode characters intact; DDE used
to be able to retrieve reasonably large memo fields, but won't do
Unicode in the general case. Since you're using DDE, the only
possibilities I can think of off the top of my head a
a. Word /has/ determined a data type based on (say) the first eight
values in the column, and has picked a "majority" text type rather than
a memo type if the next 7 records have fewer than 255 columns. (This is
not quite how this particular thing works in Excel, but it's not far
off). I rather doubt it
b. is there a problem with all 255 memos? Or just the first one?
c. if you are only retrieving one memo field, does it make any
difference whether it is listed at the beginning or end of the field
list in the SELECT?

Peter Jamieson

http://tips.pjmsn.me.uk

AlecJames wrote:
Thanks for your help Peter

It will take some time to work through the very useful suggestions you have
made.

I agree with you that Word VBA is a more elegent solution. I'm going
another way for 2 reasons - (1) I always wanted to try this (2) I have to
produce different views of the specification based on the same base data.

The word merge was getting complex because I had a simple access query to
give me tblRequirements and tblNotes linked by an requirement ID field. Then
I tried to sort out the result in word.
I've changed this by creating an access query and vba function that gives
me the data exactly as I need it in word. Now the word merge is simple with
no need for loops or other logic. (I must thank you again for posting
http://help.wugnet.com/office/Querie...rderasc-8.html which helped me get my query + vba working)

I see what you mean about para numbering. I changed the merge type to
'directory' and used a heading style with numbering. This seems to work.

I have one problem I'm struggling with - I have a merge field that is longer
than 255 characters (the first record of the query). It displays Ok up till
character 255 and then word shows ??? ??? ??? to the end of the string.
Any ideas as to why

Thanks
Alec

"Peter Jamieson" wrote:

There's a square peg/round hole aspect to trying to do this kind of
thing using either Mailmerge or Database fields. For example, the next
thing you'll probably be asking is how to get mailmerge to do the
automatic paragraph numbering you want your specification to have.
(well, OK, that's obviously a guess, but if you do want to do that it
probably won't be easy.)

You can do a certain amount of parent/child record processing using
merge - have a look at macropod's tutorial on the subject at
http://www.wopr.com/index.php?showtopic=731107

That may be enough to point you where you need to go, but IMO you may be
better off thinking in terms of retriving the records you need using
e.g. Word VBA and ADO, and inserting them one by one into your document.
You'll have far more control over what is going on.

I cannot find a way to loop while merging (no while, for each, etc.),
can
this be done?

you can't really loop using the field language, no. What macropod's
tutorial shows is just about the best you can do purely with fields.

The other field-based technique requires you to have a "parent" table as
the mail merge data source and a child table inserted using a DATABASE
field. By referencing the parent table's key fields in the WHERE clause
of the SQL statement in the DATABASE field, you can restrict the child
records to be the ones that are chidren of the record currently being
merge. But you get little control over table layout, there can be naming
clashes between the merge and DATABASE field data sources, and so on.


I cannot find a way to call vba while merging

Yes, you can use Word's MailMerge Events to invoke VBA as each data
source record is processed, and so on. However, because merge needs a
single "flat file" as a data source, you typically end up having to
create a query that generates a denormalised set of records, then you
have to Event code to detect where the records belonging to one parent
record end and another begins, and /that/ can be tricky too.

A viable combination might be to have a Mail Merge with the parent table
as the data source, + MailMerge Event processing that uses ADO to fetch
child records belonging to each parent record,

However, I suspect that going for a non-Mailmerge solution will end up
being simpler.

or create a user defined field
code, can this be done?

It depends on what you mean. You can use VBA to create Document
variables, which have a name and a value, and which persist in the
document. You can then insert these values - with some limitations using
{ DOCVARIABLE fields }. You can have a heck of a lot of Document
Variables (the practical limit has more to do with the overall document
size and processing slowdowns). However, you can't for example have a
field code that does something "new", such as executing an arbitrary
piece of Word VBA code. (Well, you can, or you used to be able to, but
you have to write a text format converter, learn to use RTF, live with
some nasty but not wholly unreasonable security constraints, and more or
less hijack the INCLUDETEXT field. But again, it's probably a whole lot
simpler to write some VBA to get the data and insert it with the
formatting you want.


Peter Jamieson

http://tips.pjmsn.me.uk

AlecJames wrote:
BTW I'm using Office 2003.

I've found that I can do what I want with the database tool bar functions.
I can design a table, insert database, then insert merge fields into the
table cells. Next problem -

My access query combines multiple tables - requirements + notes. each
requirement can have none or many notes which means I can have 1+ records for
each requirement. In the merge document I want to fill out my table with the
first record, then loop through the next records adding notes under my table.
Then fill out another table when the requirement ID changes. However,

I cannot find a way to loop while merging (no while, for each, etc.), can
this be done?
I cannot find a way to call vba while merging or create a user defined field
code, can this be done?

The only way I can see to do this is to use if then else and limit the
number of comments to the number of if then else nests I add.

Any suggestions?
Thanks
Alec

"AlecJames" wrote:

I want to build a document that is a specification.

There will be sections for introduction, requirements and appendicies.

I want each requirement in the requirements section to come from an Access
database (so it looks as if I had inserted an Access report into the word
document).

I will update / maintain the database and then requery from the word
document to release new versions of the specification. I will also be able
to have different views of the specification in different word documents that
query the same DB.

So in the word document I will have a layout that is repeated for each row
my query returns, with the format and style set in word.

Can this be done? I find that mail merge only offers me mailing type
documents, even the directory type wants a recipient list.

If I use Insert-Field-Database it is close to what I want but this is a
one off insertion (if the database is updated I have to delete / reinsert, I
want to update field to requery) and I cannot change the way that the data
is put in the table (a single table is used with one row for each query row,
I want a table per query row where the data is laid out on more than one
table row.)

Any suggestions?

Thanks
Alec

[AlecJames]

Still working on this,
b. is there a problem with all 255 memos? Or just the first one?
I tried 2 and got the same results

c. if you are only retrieving one memo field, does it make any
difference whether it is listed at the beginning or end of the field
list in the SELECT?
The test was on the first and second records in the record set.

My hunch at the moment is that this is a problem in my access query. I have
an expression that calls a vba function to collate records from all of the
notes resulting in a long field. I think that access defaults to text fields
for expression results.

Thanks
Alec


"Peter Jamieson" wrote:

Hi Alec

I have one problem I'm struggling with - I have a merge field that is
longer
than 255 characters (the first record of the query). It displays Ok
up till
character 255 and then word shows ??? ??? ??? to the end of the string.
Any ideas as to why

Not in this case. Historically, there have been character-count limit
problems when Word gets data from Access memo fields (e.g. the limit for
ODBC connections was increased many years ago, I haven't researched the
Access limitations" problem anything like as much as the equivalent
problem in Excel. As far as I know, OLE DB connections to Access
retrieve memo fields complete with Unicode characters intact; DDE used
to be able to retrieve reasonably large memo fields, but won't do
Unicode in the general case. Since you're using DDE, the only
possibilities I can think of off the top of my head a
a. Word /has/ determined a data type based on (say) the first eight
values in the column, and has picked a "majority" text type rather than
a memo type if the next 7 records have fewer than 255 columns. (This is
not quite how this particular thing works in Excel, but it's not far
off). I rather doubt it
b. is there a problem with all 255 memos? Or just the first one?
c. if you are only retrieving one memo field, does it make any
difference whether it is listed at the beginning or end of the field
list in the SELECT?

Peter Jamieson

http://tips.pjmsn.me.uk

AlecJames wrote:
Thanks for your help Peter

It will take some time to work through the very useful suggestions you have
made.

I agree with you that Word VBA is a more elegent solution. I'm going
another way for 2 reasons - (1) I always wanted to try this (2) I have to
produce different views of the specification based on the same base data.

The word merge was getting complex because I had a simple access query to
give me tblRequirements and tblNotes linked by an requirement ID field. Then
I tried to sort out the result in word.
I've changed this by creating an access query and vba function that gives
me the data exactly as I need it in word. Now the word merge is simple with
no need for loops or other logic. (I must thank you again for posting
http://help.wugnet.com/office/Querie...rderasc-8.html which helped me get my query + vba working)

I see what you mean about para numbering. I changed the merge type to
'directory' and used a heading style with numbering. This seems to work.

I have one problem I'm struggling with - I have a merge field that is longer
than 255 characters (the first record of the query). It displays Ok up till
character 255 and then word shows ??? ??? ??? to the end of the string.
Any ideas as to why

Thanks
Alec

"Peter Jamieson" wrote:

There's a square peg/round hole aspect to trying to do this kind of
thing using either Mailmerge or Database fields. For example, the next
thing you'll probably be asking is how to get mailmerge to do the
automatic paragraph numbering you want your specification to have.
(well, OK, that's obviously a guess, but if you do want to do that it
probably won't be easy.)

You can do a certain amount of parent/child record processing using
merge - have a look at macropod's tutorial on the subject at
http://www.wopr.com/index.php?showtopic=731107

That may be enough to point you where you need to go, but IMO you may be
better off thinking in terms of retriving the records you need using
e.g. Word VBA and ADO, and inserting them one by one into your document.
You'll have far more control over what is going on.

I cannot find a way to loop while merging (no while, for each, etc.),
can
this be done?

you can't really loop using the field language, no. What macropod's
tutorial shows is just about the best you can do purely with fields.

The other field-based technique requires you to have a "parent" table as
the mail merge data source and a child table inserted using a DATABASE
field. By referencing the parent table's key fields in the WHERE clause
of the SQL statement in the DATABASE field, you can restrict the child
records to be the ones that are chidren of the record currently being
merge. But you get little control over table layout, there can be naming
clashes between the merge and DATABASE field data sources, and so on.


I cannot find a way to call vba while merging

Yes, you can use Word's MailMerge Events to invoke VBA as each data
source record is processed, and so on. However, because merge needs a
single "flat file" as a data source, you typically end up having to
create a query that generates a denormalised set of records, then you
have to Event code to detect where the records belonging to one parent
record end and another begins, and /that/ can be tricky too.

A viable combination might be to have a Mail Merge with the parent table
as the data source, + MailMerge Event processing that uses ADO to fetch
child records belonging to each parent record,

However, I suspect that going for a non-Mailmerge solution will end up
being simpler.

or create a user defined field
code, can this be done?

It depends on what you mean. You can use VBA to create Document
variables, which have a name and a value, and which persist in the
document. You can then insert these values - with some limitations using
{ DOCVARIABLE fields }. You can have a heck of a lot of Document
Variables (the practical limit has more to do with the overall document
size and processing slowdowns). However, you can't for example have a
field code that does something "new", such as executing an arbitrary
piece of Word VBA code. (Well, you can, or you used to be able to, but
you have to write a text format converter, learn to use RTF, live with
some nasty but not wholly unreasonable security constraints, and more or
less hijack the INCLUDETEXT field. But again, it's probably a whole lot
simpler to write some VBA to get the data and insert it with the
formatting you want.


Peter Jamieson

http://tips.pjmsn.me.uk

AlecJames wrote:
BTW I'm using Office 2003.

I've found that I can do what I want with the database tool bar functions.
I can design a table, insert database, then insert merge fields into the
table cells. Next problem -

My access query combines multiple tables - requirements + notes. each
requirement can have none or many notes which means I can have 1+ records for
each requirement. In the merge document I want to fill out my table with the
first record, then loop through the next records adding notes under my table.
Then fill out another table when the requirement ID changes. However,

I cannot find a way to loop while merging (no while, for each, etc.), can
this be done?
I cannot find a way to call vba while merging or create a user defined field
code, can this be done?

The only way I can see to do this is to use if then else and limit the
number of comments to the number of if then else nests I add.

Any suggestions?
Thanks
Alec

"AlecJames" wrote:

I want to build a document that is a specification.

There will be sections for introduction, requirements and appendicies.

I want each requirement in the requirements section to come from an Access
database (so it looks as if I had inserted an Access report into the word
document).

I will update / maintain the database and then requery from the word
document to release new versions of the specification. I will also be able
to have different views of the specification in different word documents that
query the same DB.

So in the word document I will have a layout that is repeated for each row
my query returns, with the format and style set in word.

Can this be done? I find that mail merge only offers me mailing type
documents, even the directory type wants a recipient list.

If I use Insert-Field-Database it is close to what I want but this is a
one off insertion (if the database is updated I have to delete / reinsert, I
want to update field to requery) and I cannot change the way that the data
is put in the table (a single table is used with one row for each query row,
I want a table per query row where the data is laid out on more than one
table row.)

Any suggestions?

Thanks
Alec