Concatenate and can grow do not work

[ken]

I have a text box (txtLegalName) on an 800 page report that
concatenates some information together. I am setting the value of the
text box (which includes the concatenation) on the print event of the
detail section. Originally, I had it in the underlying query of the
report but the report took forever to run. It now runs much faster as
the concatenation happens as each page prints instead of for each
record of all 800 pages before the report opens.

Here is the problem. The Can Grow property does not work on
txtLegalName in this instance so the concatenated string gets cut off
in some cases. I have Can Grow set on both txtLegalName and the Detail
section itself. I don't want to increase the height of the text box
and turn Can Grow off as this will add unnecessary white space to the
report. I know the dynamic setting of the value of the text box in the
Print event is the problem because removing the concatenation allows
Can Grow to work properly.

Here is what I have in the Detail Print event:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim qdf As QueryDef
Dim strSql As String

'further filter the subquery before concatenating.
Set qdf = CurrentDb.QueryDefs("qryCoFundsWithoutCount")
strSql = Trim(qdf.SQL)

qdf.SQL = Left(qdf.SQL, (Len(qdf.SQL) - 3)) & " AND
qryRsTblCo.fldCoID=" & Me.fldCoID

'set the string that will populate the text box
strFunds = Me.[fldCoName] & IIf(IsNull(Me.[fldCoTaxType]) = False,
_
" (" & Me.[fldCoTaxType] & ") ", "") & " - " &
ConcatenateChar("qryCoFundsWithoutCount", "fldFundID", "[fldCoID] = "
& _
Me.fldCoID, "", "/ ")

'populate the text box
Me.txtLegalName = strFunds

'reset the querydef
qdf.SQL = strSql
Set qdf = Nothing

End Sub

Any ideas on how to make txtLegalName grow to the proper height with
the Print Event set as it currently is? I'm trying to speed up the
printing of this report and everything works fine except that this
text box is not growing as it should.

Thanks-

Ken

 

[Rob Parker]

Hi Ken,

Your problem is caused because the Print event fires after the Format event,
and it's in the format event that the size of CanGrow/CanShrink
controls/sections is set - and there may be several passes through the
report to get the formatting correct. You must do the concatenation before
that - ie. in the Format event, rather than the Print event.

An alternative may be to do your concatenation in the report's query. I
haven't looked in detail at what your doing for that.

HTH,

Rob

 

[ken]

Hi Ken,

Your problem is caused because the Print event fires after the Format event,
and it's in the format event that the size of CanGrow/CanShrink
controls/sections is set - and there may be several passes through the
report to get the formatting correct.  You must do the concatenation before
that - ie. in the Format event, rather than the Print event.

An alternative may be to do your concatenation in the report's query.  I
haven't looked in detail at what your doing for that.

HTH,

Rob

I have a text box (txtLegalName) on an 800 page report that
concatenates some information together. I am setting the value of the
text box (which includes the concatenation) on the print event of the
detail section. Originally, I had it in the underlying query of the
report but the report took forever to run. It now runs much faster as
the concatenation happens as each page prints instead of for each
record of all 800 pages before the report opens.

Here is the problem. The Can Grow property does not work on
txtLegalName in this instance so the concatenated string gets cut off
in some cases. I have Can Grow set on both txtLegalName and the Detail
section itself. I don't want to increase the height of the text box
and turn Can Grow off as this will add unnecessary white space to the
report. I know the dynamic setting of the value of the text box in the
Print event is the problem because removing the concatenation allows
Can Grow to work properly.

Here is what I have in the Detail Print event:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  Dim qdf As QueryDef
  Dim strSql As String

  'further filter the subquery before concatenating.
  Set qdf = CurrentDb.QueryDefs("qryCoFundsWithoutCount")
  strSql = Trim(qdf.SQL)

  qdf.SQL = Left(qdf.SQL, (Len(qdf.SQL) - 3)) & " AND
qryRsTblCo.fldCoID=" & Me.fldCoID

  'set the string that will populate the text box
  strFunds = Me.[fldCoName] & IIf(IsNull(Me.[fldCoTaxType]) = False,
_
     " (" & Me.[fldCoTaxType] & ") ", "") & " - " &
ConcatenateChar("qryCoFundsWithoutCount", "fldFundID", "[fldCoID] = "
& _
     Me.fldCoID, "", "/ ")

  'populate the text box
  Me.txtLegalName = strFunds

  'reset the querydef
  qdf.SQL = strSql
  Set qdf = Nothing

End Sub

Any ideas on how to make txtLegalName grow to the proper height with
the Print Event set as it currently is? I'm trying to speed up the
printing of this report and everything works fine except that this
text box is not growing as it should.

Ken- Hide quoted text -

- Show quoted text -

Rob-

I had the concatenation in the report query but the query took forever
to run so that is not an option. I'm trying to optimize the speed of
the report and that is why i moved the concatenation to the Print
event. Now each page of the report prints in several seconds versus 20
- 30 minutes for the report to preview when the concatenation was in
the report query.

I had the code above in the Format event but it ran very slowly and
that is why i moved it to the Print event where it ran much faster. My
goal is to speed up the printing of the report so i'm trying to move
the concatenation around since it is the culprit for speed issue.

Now that you've explained why the text box is not resizing (print
event after format event), i'll need to explore some alternatives. Any
suggestions would be appreciated.

Thanks-

Ken

 

[Rob Parker]

Ken,

Sadly, I'm not aware of any alternatives. It's a fact of Access life that
if you are wanting to resize controls in a report on-the-fly, either by
using their CanShrink/CanGrow properties, or by manually coding to resize
things, you have to do this in the Format event, rather than the Print
event. As you surmise, the reason things appear to happen quicker when you
set the contents of your textbox to the concatenated string in the print
event is that it's happening for each record as it gets printed. If you did
manually code to resize the textbox in the print event (and it could be
tricky to determine exactly what height it should be), then that resizing
could cause the report format to "blow up" - eg. the textbox might expand to
the next page, or push other controls to the next page, or overlap other
controls, or cause the page numbering to become incorrect, or ...

I also suspect that, with the situation as you currently have it, if you
click on the Last Page arrow in print preview mode when your report opens,
you'll find that you're back to the long wait.

I think you'll either have to accept some spare white space in your report
if you insist on doing the concatenation in the Print event (by setting the
textbox to the maximum size it will ever be), or live with the long time if
you must use CanGrow in the Format event. Failing that, perhaps you could
optimise the concatenation process in your query to get that to run faster.

Sorry I can't be more helpful,

Rob