Merge with Word: memo field too long for string

  • Thread starter Thread starter DRBE
  • Start date Start date
D

DRBE

Thanks for all previous help, and patience with my lack of skills. I am
using the code below to insert fields from my database into a Preformatted
Word Doc.
It has been working great, except now, the fields I need to insert are from
"memo" fields and greater than 255 chars. It works fine if shorter, but as
soon as there are too many characters, when I run the command, instead of
opening the "merged" document, I am asked if I want to "save as" document1.
If I do this, the document opens, but the field that is too long, has not
been inserted, and when I close the document I get the error "string
parameter too long".

Any advice on how to get around this would be appreciated.

Thanks in anticipation
Kind regards

Bruce

This is in a module, and then the function called from the form.

Public Sub PrintMLReport(vID As Long, vID2 As Long, vFilename As String)

Dim objWord As Word.Application

Dim ExpIDrep, RepIDrep, WorkDetrst As DAO.Recordset

On Error GoTo ErrorCode

'Start MS Word and open specified document
Set objWord = New Word.Application
objWord.Documents.Add vFilename
objWord.ScreenUpdating = True

Set AccidRst = CurrentDb.OpenRecordset("SELECT * " _
& "FROM [2AccidentDetailsTable] WHERE ReportID = " & vID2)

ReplaceText objWord, "[AccidentSumm]", Nz(AccidRst![Summary])

objWord.ScreenUpdating = True
objWord.ActiveDocument.Saved = True
objWord.Visible = True
Set objWord = Nothing
Exit Sub

ErrorCode:
objWord.Quit
MsgBox Err.Description

End Sub

Public Sub ReplaceText(obj As Word.Application, vSource As String, vDest As
String)

'Replace all occurences of vSource with vDest in Word doc

obj.ActiveDocument.Content.Find.Execute FindText:=vSource, _
ReplaceWith:=vDest, Format:=True, _
Replace:=wdReplaceAll

End Sub
 
Hi Bruce,

what are you wanting to do?

1. truncate memo to 255 chars?
or
2. put all the text in there?

#1 is easier ... just use

Left(Nz(AccidRst![Summary],""),255)


~~~

you need to close and release your recordset

AccidRst.close
set AccidRst = nothing


~~~

since you are only using one field from the recordset, you should
specify it instead of using SELECT *



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
Thanks for the comments,
I am wanting the full text, not trunncated version. Basically there are
multiple fields from multiple tables initially, (to identify the client, the
agency that referred them ) and then the body of the document which has 1
table per subsections, with only a summary field. so the specify would work
if I can figure out how to do it.

I also tried using the Merge option in "Tools" "Office links", but this only
seems to allow data to be sent to Word from 1 table, and my document needs
fields from about 13 linked tables to be inserted into the final Word
Document.

Thanks again for your advice.

Bruce

strive4peace said:
Hi Bruce,

what are you wanting to do?

1. truncate memo to 255 chars?
or
2. put all the text in there?

#1 is easier ... just use

Left(Nz(AccidRst![Summary],""),255)


~~~

you need to close and release your recordset

AccidRst.close
set AccidRst = nothing


~~~

since you are only using one field from the recordset, you should
specify it instead of using SELECT *



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Thanks for all previous help, and patience with my lack of skills. I am
using the code below to insert fields from my database into a Preformatted
Word Doc.
It has been working great, except now, the fields I need to insert are from
"memo" fields and greater than 255 chars. It works fine if shorter, but as
soon as there are too many characters, when I run the command, instead of
opening the "merged" document, I am asked if I want to "save as" document1.
If I do this, the document opens, but the field that is too long, has not
been inserted, and when I close the document I get the error "string
parameter too long".

Any advice on how to get around this would be appreciated.

Thanks in anticipation
Kind regards

Bruce

This is in a module, and then the function called from the form.

Public Sub PrintMLReport(vID As Long, vID2 As Long, vFilename As String)

Dim objWord As Word.Application

Dim ExpIDrep, RepIDrep, WorkDetrst As DAO.Recordset

On Error GoTo ErrorCode

'Start MS Word and open specified document
Set objWord = New Word.Application
objWord.Documents.Add vFilename
objWord.ScreenUpdating = True

Set AccidRst = CurrentDb.OpenRecordset("SELECT * " _
& "FROM [2AccidentDetailsTable] WHERE ReportID = " & vID2)

ReplaceText objWord, "[AccidentSumm]", Nz(AccidRst![Summary])

objWord.ScreenUpdating = True
objWord.ActiveDocument.Saved = True
objWord.Visible = True
Set objWord = Nothing
Exit Sub

ErrorCode:
objWord.Quit
MsgBox Err.Description

End Sub

Public Sub ReplaceText(obj As Word.Application, vSource As String, vDest As
String)

'Replace all occurences of vSource with vDest in Word doc

obj.ActiveDocument.Content.Find.Execute FindText:=vSource, _
ReplaceWith:=vDest, Format:=True, _
Replace:=wdReplaceAll

End Sub
Click to expand...
Click to expand...
 
Hi Bruce,

I will get back to you later with some code ...

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Thanks for the comments,
I am wanting the full text, not trunncated version. Basically there are
multiple fields from multiple tables initially, (to identify the client, the
agency that referred them ) and then the body of the document which has 1
table per subsections, with only a summary field. so the specify would work
if I can figure out how to do it.

I also tried using the Merge option in "Tools" "Office links", but this only
seems to allow data to be sent to Word from 1 table, and my document needs
fields from about 13 linked tables to be inserted into the final Word
Document.

Thanks again for your advice.

Bruce

strive4peace said:
Hi Bruce,

what are you wanting to do?

1. truncate memo to 255 chars?
or
2. put all the text in there?

#1 is easier ... just use

Left(Nz(AccidRst![Summary],""),255)


~~~

you need to close and release your recordset

AccidRst.close
set AccidRst = nothing


~~~

since you are only using one field from the recordset, you should
specify it instead of using SELECT *



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Thanks for all previous help, and patience with my lack of skills. I am
using the code below to insert fields from my database into a Preformatted
Word Doc.
It has been working great, except now, the fields I need to insert are from
"memo" fields and greater than 255 chars. It works fine if shorter, but as
soon as there are too many characters, when I run the command, instead of
opening the "merged" document, I am asked if I want to "save as" document1.
If I do this, the document opens, but the field that is too long, has not
been inserted, and when I close the document I get the error "string
parameter too long".

Any advice on how to get around this would be appreciated.

Thanks in anticipation
Kind regards

Bruce

This is in a module, and then the function called from the form.

Public Sub PrintMLReport(vID As Long, vID2 As Long, vFilename As String)

Dim objWord As Word.Application

Dim ExpIDrep, RepIDrep, WorkDetrst As DAO.Recordset

On Error GoTo ErrorCode

'Start MS Word and open specified document
Set objWord = New Word.Application
objWord.Documents.Add vFilename
objWord.ScreenUpdating = True

Set AccidRst = CurrentDb.OpenRecordset("SELECT * " _
& "FROM [2AccidentDetailsTable] WHERE ReportID = " & vID2)

ReplaceText objWord, "[AccidentSumm]", Nz(AccidRst![Summary])

objWord.ScreenUpdating = True
objWord.ActiveDocument.Saved = True
objWord.Visible = True
Set objWord = Nothing
Exit Sub

ErrorCode:
objWord.Quit
MsgBox Err.Description

End Sub

Public Sub ReplaceText(obj As Word.Application, vSource As String, vDest As
String)

'Replace all occurences of vSource with vDest in Word doc

obj.ActiveDocument.Content.Find.Execute FindText:=vSource, _
ReplaceWith:=vDest, Format:=True, _
Replace:=wdReplaceAll

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Bruce,

have not forgotten about you -- will need to experiment first before I
give you code so I need to wait until I have a little extra time to do
that. To make it faster for me, email me your database so I don't have
to make stuff up for testing. Put "Merge with Word" in the subject
line. Compact/Repair and zip it up before you send it.

strive4peace2006 at yahoo.com

Thank you

I will reply back to this thread with the code

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Thanks for the comments,
I am wanting the full text, not trunncated version. Basically there are
multiple fields from multiple tables initially, (to identify the client, the
agency that referred them ) and then the body of the document which has 1
table per subsections, with only a summary field. so the specify would work
if I can figure out how to do it.

I also tried using the Merge option in "Tools" "Office links", but this only
seems to allow data to be sent to Word from 1 table, and my document needs
fields from about 13 linked tables to be inserted into the final Word
Document.

Thanks again for your advice.

Bruce

strive4peace said:
Hi Bruce,

what are you wanting to do?

1. truncate memo to 255 chars?
or
2. put all the text in there?

#1 is easier ... just use

Left(Nz(AccidRst![Summary],""),255)


~~~

you need to close and release your recordset

AccidRst.close
set AccidRst = nothing


~~~

since you are only using one field from the recordset, you should
specify it instead of using SELECT *



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Thanks for all previous help, and patience with my lack of skills. I am
using the code below to insert fields from my database into a Preformatted
Word Doc.
It has been working great, except now, the fields I need to insert are from
"memo" fields and greater than 255 chars. It works fine if shorter, but as
soon as there are too many characters, when I run the command, instead of
opening the "merged" document, I am asked if I want to "save as" document1.
If I do this, the document opens, but the field that is too long, has not
been inserted, and when I close the document I get the error "string
parameter too long".

Any advice on how to get around this would be appreciated.

Thanks in anticipation
Kind regards

Bruce

This is in a module, and then the function called from the form.

Public Sub PrintMLReport(vID As Long, vID2 As Long, vFilename As String)

Dim objWord As Word.Application

Dim ExpIDrep, RepIDrep, WorkDetrst As DAO.Recordset

On Error GoTo ErrorCode

'Start MS Word and open specified document
Set objWord = New Word.Application
objWord.Documents.Add vFilename
objWord.ScreenUpdating = True

Set AccidRst = CurrentDb.OpenRecordset("SELECT * " _
& "FROM [2AccidentDetailsTable] WHERE ReportID = " & vID2)

ReplaceText objWord, "[AccidentSumm]", Nz(AccidRst![Summary])

objWord.ScreenUpdating = True
objWord.ActiveDocument.Saved = True
objWord.Visible = True
Set objWord = Nothing
Exit Sub

ErrorCode:
objWord.Quit
MsgBox Err.Description

End Sub

Public Sub ReplaceText(obj As Word.Application, vSource As String, vDest As
String)

'Replace all occurences of vSource with vDest in Word doc

obj.ActiveDocument.Content.Find.Execute FindText:=vSource, _
ReplaceWith:=vDest, Format:=True, _
Replace:=wdReplaceAll

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Bruce

still waiting for you to send a database to experiment with (oh! and I
also need your Word template)... basically, what needs to happen is this:

do a search in Word
when the phrase is found, insert the contents of the memo field --
will have to see if it can be done all at once or it needs to be done in
255-char chunks


strive4peace2006 at yahoo.com

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Thanks for the comments,
I am wanting the full text, not trunncated version. Basically there are
multiple fields from multiple tables initially, (to identify the client, the
agency that referred them ) and then the body of the document which has 1
table per subsections, with only a summary field. so the specify would work
if I can figure out how to do it.

I also tried using the Merge option in "Tools" "Office links", but this only
seems to allow data to be sent to Word from 1 table, and my document needs
fields from about 13 linked tables to be inserted into the final Word
Document.

Thanks again for your advice.

Bruce

strive4peace said:
Hi Bruce,

what are you wanting to do?

1. truncate memo to 255 chars?
or
2. put all the text in there?

#1 is easier ... just use

Left(Nz(AccidRst![Summary],""),255)


~~~

you need to close and release your recordset

AccidRst.close
set AccidRst = nothing


~~~

since you are only using one field from the recordset, you should
specify it instead of using SELECT *



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Thanks for all previous help, and patience with my lack of skills. I am
using the code below to insert fields from my database into a Preformatted
Word Doc.
It has been working great, except now, the fields I need to insert are from
"memo" fields and greater than 255 chars. It works fine if shorter, but as
soon as there are too many characters, when I run the command, instead of
opening the "merged" document, I am asked if I want to "save as" document1.
If I do this, the document opens, but the field that is too long, has not
been inserted, and when I close the document I get the error "string
parameter too long".

Any advice on how to get around this would be appreciated.

Thanks in anticipation
Kind regards

Bruce

This is in a module, and then the function called from the form.

Public Sub PrintMLReport(vID As Long, vID2 As Long, vFilename As String)

Dim objWord As Word.Application

Dim ExpIDrep, RepIDrep, WorkDetrst As DAO.Recordset

On Error GoTo ErrorCode

'Start MS Word and open specified document
Set objWord = New Word.Application
objWord.Documents.Add vFilename
objWord.ScreenUpdating = True

Set AccidRst = CurrentDb.OpenRecordset("SELECT * " _
& "FROM [2AccidentDetailsTable] WHERE ReportID = " & vID2)

ReplaceText objWord, "[AccidentSumm]", Nz(AccidRst![Summary])

objWord.ScreenUpdating = True
objWord.ActiveDocument.Saved = True
objWord.Visible = True
Set objWord = Nothing
Exit Sub

ErrorCode:
objWord.Quit
MsgBox Err.Description

End Sub

Public Sub ReplaceText(obj As Word.Application, vSource As String, vDest As
String)

'Replace all occurences of vSource with vDest in Word doc

obj.ActiveDocument.Content.Find.Execute FindText:=vSource, _
ReplaceWith:=vDest, Format:=True, _
Replace:=wdReplaceAll

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the offer to help with the code. Unfortunately there is too much
confidential client data imbedded in the database and word doc at present so
would need to strip it down a bit before emailing, and I am away on holiday
for a few weeks, so will need to do it on my return. Thanks again, hopefully
I'll drop you a line on my return (if you're still willing to help)

kind regards
Bruce

strive4peace said:
Hi Bruce

still waiting for you to send a database to experiment with (oh! and I
also need your Word template)... basically, what needs to happen is this:

do a search in Word
when the phrase is found, insert the contents of the memo field --
will have to see if it can be done all at once or it needs to be done in
255-char chunks


strive4peace2006 at yahoo.com

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Thanks for the comments,
I am wanting the full text, not trunncated version. Basically there are
multiple fields from multiple tables initially, (to identify the client, the
agency that referred them ) and then the body of the document which has 1
table per subsections, with only a summary field. so the specify would work
if I can figure out how to do it.

I also tried using the Merge option in "Tools" "Office links", but this only
seems to allow data to be sent to Word from 1 table, and my document needs
fields from about 13 linked tables to be inserted into the final Word
Document.

Thanks again for your advice.

Bruce

strive4peace said:
Hi Bruce,

what are you wanting to do?

1. truncate memo to 255 chars?
or
2. put all the text in there?

#1 is easier ... just use

Left(Nz(AccidRst![Summary],""),255)


~~~

you need to close and release your recordset

AccidRst.close
set AccidRst = nothing


~~~

since you are only using one field from the recordset, you should
specify it instead of using SELECT *



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




DRBE wrote:
Thanks for all previous help, and patience with my lack of skills. I am
using the code below to insert fields from my database into a Preformatted
Word Doc.
It has been working great, except now, the fields I need to insert are from
"memo" fields and greater than 255 chars. It works fine if shorter, but as
soon as there are too many characters, when I run the command, instead of
opening the "merged" document, I am asked if I want to "save as" document1.
If I do this, the document opens, but the field that is too long, has not
been inserted, and when I close the document I get the error "string
parameter too long".

Any advice on how to get around this would be appreciated.

Thanks in anticipation
Kind regards

Bruce

This is in a module, and then the function called from the form.

Public Sub PrintMLReport(vID As Long, vID2 As Long, vFilename As String)

Dim objWord As Word.Application

Dim ExpIDrep, RepIDrep, WorkDetrst As DAO.Recordset

On Error GoTo ErrorCode

'Start MS Word and open specified document
Set objWord = New Word.Application
objWord.Documents.Add vFilename
objWord.ScreenUpdating = True

Set AccidRst = CurrentDb.OpenRecordset("SELECT * " _
& "FROM [2AccidentDetailsTable] WHERE ReportID = " & vID2)

ReplaceText objWord, "[AccidentSumm]", Nz(AccidRst![Summary])

objWord.ScreenUpdating = True
objWord.ActiveDocument.Saved = True
objWord.Visible = True
Set objWord = Nothing
Exit Sub

ErrorCode:
objWord.Quit
MsgBox Err.Description

End Sub

Public Sub ReplaceText(obj As Word.Application, vSource As String, vDest As
String)

'Replace all occurences of vSource with vDest in Word doc

obj.ActiveDocument.Content.Find.Execute FindText:=vSource, _
ReplaceWith:=vDest, Format:=True, _
Replace:=wdReplaceAll

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top