Coding causing detail section in report to not grow/shrink

[prairiewind via AccessMonster.com]

I've posted my problem in the Reports/Printing section of the forum, but
after some time it seems like it is a VBA issue. I am using VBA to populate
5 text boxes in my report. (The text boxes are one below the other.) My
problem is when record A has 2 lines of data (only txtLine1 & txtLine2), then
record B will only show 2 lines even if it has 3 (txtLine3 has data that
doesn't show.). Then if record C has 2 lines of data, there will be a blank
line being that record B had 3 lines. It seems to take the conditions of the
previous record and apply it to the current record.

For example:
Record A:
Tom Sawyer
123 Main St, Anytown, AA 12345

Record B:
Huck Fynn
Property Managment
(234 Side St, Here, BB 23456) - this line doesn't show

Record C:
Willow Switch
345 Boxcar Ave, There, CC 34567
This is a blank line that should NOT be
there.

I'll include the coding that I have for behind the On Print property of the
detail section of the report. As you'll see, what goes in txtLine1, txtLine2,
etc. is dependent on what information and how much information is in two
different fields.

If IsNull(Me.Children & Me.ExtraAddressInformation) Then
Me.txtLine1 = Me.Lastname & ", " & Me.Firstname
Me.txtLine2 = Me.FullAddress1
Me.txtLine3 = Me.FullAddress2
ElseIf IsNull(Me.Children) And Len(Me.ExtraAddressInformation) > 1 Then
If Len(Me.Lastname & Me.Firstname & Me.ExtraAddressInformation) > Me.
NameLineLength Then
Me.txtLine1 = Me.Lastname & ", " & Me.Firstname
Me.txtLine2 = Me.ExtraAddressInformation
Me.txtLine3 = Me.FullAddress1
Me.txtLine4 = Me.FullAddress2
Else
Me.txtLine1 = Me.Lastname & ", " & Me.Firstname & " " & Me.
ExtraAddressInformation
Me.txtLine2 = Me.FullAddress1
Me.txtLine3 = Me.FullAddress2
End If
ElseIf Len(Me.Children) > 1 And IsNull(Me.ExtraAddressInformation) Then
If Len(Me.Lastname & Me.Firstname & Me.Children) > Me.NameLineLength Then
If Len(Me.Children) > Me.NameLineLength Then
Me.txtLine1 = Me.Lastname & ", " & Me.Firstname & "; " & Me.
SplitChildrenLeft
Me.txtLine2 = Me.SplitChildrenRight
Me.txtLine3 = Me.FullAddress1
Me.txtLine4 = Me.FullAddress2
Else
Me.txtLine1 = Me.Lastname & ", " & Me.Firstname
Me.txtLine2 = Me.Children
Me.txtLine3 = Me.FullAddress1
Me.txtLine4 = Me.FullAddress2
End If
Else
Me.txtLine1 = Me.Lastname & ", " & Me.Firstname & "; " & Me.Children
Me.txtLine2 = Me.FullAddress1
Me.txtLine3 = Me.FullAddress2
End If
ElseIf Len(Me.Children) > 1 And Len(Me.ExtraAddressInformation) > 1 Then
If Len(Me.Lastname & Me.Firstname & Me.Children) > Me.NameLineLength Then
If Len(Me.Children) > Me.NameLineLength Then
If Len(Me.Lastname & Me.Firstname & Me.ExtraAddressInformation) >
Me.NameLineLength Then
Me.txtLine1 = Me.Lastname & ", " & Me.Firstname & "; " & Me.
SplitChildrenLeft
Me.txtLine2 = Me.SplitChildrenRight
Me.txtLine3 = Me.ExtraAddressInformation
Me.txtLine4 = Me.FullAddress1
Me.txtLine5 = Me.FullAddress2
Else
Me.txtLine1 = Me.Lastname & ", " & Me.Firstname & "; " & Me.
SplitChildrenLeft
Me.txtLine2 = Me.SplitChildrenRight
Me.txtLine3 = Me.ExtraAddressInformation
Me.txtLine4 = Me.FullAddress1
Me.txtLine5 = Me.FullAddress2
End If
Else
If Len(Me.Lastname & Me.Firstname & Me.ExtraAddressInformation)

Me.NameLineLength Then

Me.txtLine1 = Me.Lastname & ", " & Me.Firstname
Me.txtLine2 = Me.Children
Me.txtLine3 = Me.ExtraAddressInformation
Me.txtLine4 = Me.FullAddress1
Me.txtLine5 = Me.FullAddress2
Else
Me.txtLine1 = Me.Lastname & ", " & Me.Firstname & " " & Me.
ExtraAddressInformation
Me.txtLine2 = Me.Children
Me.txtLine3 = Me.FullAddress1
Me.txtLine4 = Me.FullAddress2
End If
End If
Else
If Len(Me.Lastname & Me.Firstname & Me.ExtraAddressInformation) > Me.
NameLineLength Then
Me.txtLine1 = Me.Lastname & ", " & Me.Firstname & "; " & Me.
Children
Me.txtLine2 = Me.ExtraAddressInformation
Me.txtLine3 = Me.FullAddress1
Me.txtLine4 = Me.FullAddress2
Else
Me.txtLine1 = Me.Lastname & ", " & Me.Firstname & "; " & Me.
Children
Me.txtLine2 = Me.ExtraAddressInformation
Me.txtLine3 = Me.FullAddress1
Me.txtLine4 = Me.FullAddress2
End If
End If
End If



What is causing the detail section of the report to not grow/shrink as
desired.? If I make the text box overly large and set the Can Shrink
property to No, then everything shows, but that leaves a lot of blank space
as well.

I am using Access 2007 on Windows XP.

Thanks,
Jeffrey

 

[David H]

1) Have you confirmed that all of the text boxes and the detail section have
their CanGrow/CanShrink properties set to True?

2)In glancing over the code, I'd recommend going with a single unbound
control and then in code build the text that's going to be displayed in it.
It will cut down on the total amount of code dramatically.

 

[David H]

To clarify...

I'd recommend using If...Then's to build a string and then set the unbound
control to the value of the string using the .Text property (if I recall the
correct property) as in...

If [ ] then strDisplayText = strDisplayText & [Address1]
If [ ] then strDisplayText = strDisplayText & [Address2]
If [ ] then strDisplayText = strDisplayText & [Address3]
If [ ] then strDisplayText = strDisplayText & [AddressCity] & ", " &
[AddressState]

 

[tina]

this seems like an enormous amount of trouble to go to, and results in
inconsistent presentation of data - sometimes children are on the name line,
sometimes on the line below; and ditto handling of extra address info.

seems like it would be a whole lot easier to not use any code at all - just
hardcode the controls' ControlSource in the report's Detail section, as

=Lastname & ", " & FirstName
Children
ExtraAddressInformation
FullAddress1
FullAddress2

butt the controls up against each other, vertically, but don't overlap. drag
all the controls' right edges to the maximum length you want to see. set all
the controls' CanGrow AND CanShrink properties to True. and set the *Detail
section's* CanGrow and CanShrink properties to True also. as long as you
don't have zero-length string values in any of these fields in the table, it
should work fine - allowing only as much space as is needed to display the
available data from each record.

the only other thing i might do would be to add a calculated field to the
report's RecordSource, as

FullName: Lastname & ", " & FirstName

then you could simply set that name control's ControlSource to the
calculated fieldname, as

FullName

hth

 

[David H]

Given the variety of possibilities, TOP is better off dynamically creating
the address and then setting the unbound controls .text property.

For TOP, this is the sort of thing that I had in mind...its a code snippet
from a project a few years back, but you should be able to get the idea...

Dim tmpAccountName As String
tmpAccountName = ""
If (IsNull(strFirstName) = False And Len(strFirstName) <> 0) And
(IsNull(strLastName) = False And Len(strLastName) <> 0) Then
tmpAccountName = tmpAccountName & strFirstName & " " &
strLastName & Chr(13) & Chr(10)
End If
If (IsNull(strAddressLine1) = False And Len(strAddressLine1) <>
0) Then
tmpAccountName = tmpAccountName & strAddressLine1 & Chr(13)
& Chr(10)
End If
If (IsNull(strAddressLine2) = False And Len(strAddressLine2) <>
0) Then
tmpAccountName = tmpAccountName & strAddressLine2 & Chr(13)
& Chr(10)
End If
If (IsNull(strCity) = False And Len(strCity) <> 0) And
(IsNull(strState) = False And Len(strState) <> 0) And (IsNull(strZipCode) =
False And Len(strZipCode) <> 0) Then
tmpAccountName = tmpAccountName & strCity & ", " & strState
& " " & strZipCode
End If

 

[David H]

A calculated field in the Recordset? Tell me NO! NO! ... Luke! Resist the
Dark Side of the Force!

this seems like an enormous amount of trouble to go to, and results in
inconsistent presentation of data - sometimes children are on the name line,
sometimes on the line below; and ditto handling of extra address info.

seems like it would be a whole lot easier to not use any code at all - just
hardcode the controls' ControlSource in the report's Detail section, as

=Lastname & ", " & FirstName
Children
ExtraAddressInformation
FullAddress1
FullAddress2

butt the controls up against each other, vertically, but don't overlap. drag
all the controls' right edges to the maximum length you want to see. set all
the controls' CanGrow AND CanShrink properties to True. and set the *Detail
section's* CanGrow and CanShrink properties to True also. as long as you
don't have zero-length string values in any of these fields in the table, it
should work fine - allowing only as much space as is needed to display the
available data from each record.

the only other thing i might do would be to add a calculated field to the
report's RecordSource, as

FullName: Lastname & ", " & FirstName

then you could simply set that name control's ControlSource to the
calculated fieldname, as

FullName

hth

 

[Tony Toews [MVP]]

It seems to take the conditions of the
previous record and apply it to the current record.

Try your VBA code in behind the OnFormat event rather than the OnPrint
event.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[prairiewind via AccessMonster.com]

Tony,

Thank you for answering my question. A lot of people have offered other
ideas which would make the coding easier, but that is not what was needed.
The question was not to make the coding easier, rather to solve the issue of
the growing and shrinking. Many suggestions were helpful, but not the answer
to the question. Putting the VBA code behind the OnFormat worked. After a
lot of ideas, finally an answer.

Thanks,
Jeffrey

 

[prairiewind via AccessMonster.com]

1) Yes, all the text boxes and detail section have been confirmed.

2) The problem with a single unbound control is that how does a person indent
the 2-5 lines? I know that there is a lot of coding the way it is, but I
don't see another way to do it to get the results that are required for the
layout of the report.

As Tony Toews suggested, putting the code behind the OnFormat event fixed
problem.

Thanks, Jeffrey

1) Have you confirmed that all of the text boxes and the detail section have
their CanGrow/CanShrink properties set to True?

2)In glancing over the code, I'd recommend going with a single unbound
control and then in code build the text that's going to be displayed in it.
It will cut down on the total amount of code dramatically.

I've posted my problem in the Reports/Printing section of the forum, but
after some time it seems like it is a VBA issue. I am using VBA to populate

[quoted text clipped - 124 lines]

Thanks,
Jeffrey

 

[prairiewind via AccessMonster.com]

I understand the thought behind your suggestion, but the desired layout of
the report leaves me with few or no other options. For one, how does a
person get the 2nd-5th lines to indent other than to add a " " in the code?
When the amount of data is large and the report needs to be as compact as
possible, sometimes it means going to extreme measures.

As Tony Toews suggested, the answer was putting the coding in the OnFormat
event rather than the OnPrint.

Thanks,
Jeffrey

this seems like an enormous amount of trouble to go to, and results in
inconsistent presentation of data - sometimes children are on the name line,
sometimes on the line below; and ditto handling of extra address info.

seems like it would be a whole lot easier to not use any code at all - just
hardcode the controls' ControlSource in the report's Detail section, as

=Lastname & ", " & FirstName
Children
ExtraAddressInformation
FullAddress1
FullAddress2

butt the controls up against each other, vertically, but don't overlap. drag
all the controls' right edges to the maximum length you want to see. set all
the controls' CanGrow AND CanShrink properties to True. and set the *Detail
section's* CanGrow and CanShrink properties to True also. as long as you
don't have zero-length string values in any of these fields in the table, it
should work fine - allowing only as much space as is needed to display the
available data from each record.

the only other thing i might do would be to add a calculated field to the
report's RecordSource, as

FullName: Lastname & ", " & FirstName

then you could simply set that name control's ControlSource to the
calculated fieldname, as

FullName

hth

I've posted my problem in the Reports/Printing section of the forum, but
after some time it seems like it is a VBA issue. I am using VBA to populate

[quoted text clipped - 59 lines]

Else
Me.txtLine1 = Me.Lastname & ", " & Me.Firstname & "; " & Me.Children
Me.txtLine2 = Me.FullAddress1
Me.txtLine3 = Me.FullAddress2

[quoted text clipped - 37 lines]

Else
If Len(Me.Lastname & Me.Firstname & Me.ExtraAddressInformation) > Me.
NameLineLength Then
Me.txtLine1 = Me.Lastname & ", " & Me.Firstname & "; " & Me.

[quoted text clipped - 21 lines]

Thanks,
Jeffrey

 

[tina]

how does a

person get the 2nd-5th lines to indent other than to add a " " in the

code?

well, to answer the question: you're using controls in the report. if you
want the 2nd, 3rd, 4th, and 5th controls to be offset to the 1st control,
then just drag the left edge of those controls to the desired "indented"
position. easy enough.

As Tony Toews suggested, the answer was putting the coding in the OnFormat
event rather than the OnPrint.

the bottom line is getting it to work, and without causing other problems in
the db. good that Tony's solution worked for you, and no reason to think
there are side effects, so it's a win-win.

I understand the thought behind your suggestion, but the desired layout of
the report leaves me with few or no other options. For one, how does a
person get the 2nd-5th lines to indent other than to add a " " in the code?
When the amount of data is large and the report needs to be as compact as
possible, sometimes it means going to extreme measures.

As Tony Toews suggested, the answer was putting the coding in the OnFormat
event rather than the OnPrint.

Thanks,
Jeffrey

this seems like an enormous amount of trouble to go to, and results in
inconsistent presentation of data - sometimes children are on the name line,
sometimes on the line below; and ditto handling of extra address info.

seems like it would be a whole lot easier to not use any code at all - just
hardcode the controls' ControlSource in the report's Detail section, as

=Lastname & ", " & FirstName
Children
ExtraAddressInformation
FullAddress1
FullAddress2

butt the controls up against each other, vertically, but don't overlap. drag
all the controls' right edges to the maximum length you want to see. set all
the controls' CanGrow AND CanShrink properties to True. and set the *Detail
section's* CanGrow and CanShrink properties to True also. as long as you
don't have zero-length string values in any of these fields in the table, it
should work fine - allowing only as much space as is needed to display the
available data from each record.

the only other thing i might do would be to add a calculated field to the
report's RecordSource, as

FullName: Lastname & ", " & FirstName

then you could simply set that name control's ControlSource to the
calculated fieldname, as

FullName

hth

I've posted my problem in the Reports/Printing section of the forum, but
after some time it seems like it is a VBA issue. I am using VBA to

populate
[quoted text clipped - 59 lines]

Else
Me.txtLine1 = Me.Lastname & ", " & Me.Firstname & "; " & Me.Children
Me.txtLine2 = Me.FullAddress1
Me.txtLine3 = Me.FullAddress2

[quoted text clipped - 37 lines]

Else
If Len(Me.Lastname & Me.Firstname & Me.ExtraAddressInformation)

Me.
NameLineLength Then
Me.txtLine1 = Me.Lastname & ", " & Me.Firstname & "; " & Me.

[quoted text clipped - 21 lines]

Thanks,
Jeffrey

 

[tina]

i think you're confusing calculated fields in recordsets with calculated
values stored in tables, David. the latter breaks normalization rules and
are certainly poor solutions; but the former are standard solutions for
displaying data, and i see no reason to avoid using them. in fact, in many
situations, setting up a calculated field in a query is preferable to doing
so in an unbound control of a form or report, following the rule of thumb to
"run calculations as close to the source data as is practical".

hth

A calculated field in the Recordset? Tell me NO! NO! ... Luke! Resist the
Dark Side of the Force!

 

[prairiewind via AccessMonster.com]

Tina,

I should have clarified my indent issue. The reason I'm using 5 different
text boxes in the report is because I'm doing exactly what you said, to
offset the boxes to the desired position. Several people have suggested
populating all the data into one text box and that is where I run into
problems. Not problems as in where I can control the line breaks, but where
the text would wrap. I haven't found a way yet to automatically intent
wrapped text. Anyway, all the help and ideas have been appreciated.

Thanks,
Jeffrey

how does a
person get the 2nd-5th lines to indent other than to add a " " in the code?

well, to answer the question: you're using controls in the report. if you
want the 2nd, 3rd, 4th, and 5th controls to be offset to the 1st control,
then just drag the left edge of those controls to the desired "indented"
position. easy enough.

As Tony Toews suggested, the answer was putting the coding in the OnFormat
event rather than the OnPrint.

the bottom line is getting it to work, and without causing other problems in
the db. good that Tony's solution worked for you, and no reason to think
there are side effects, so it's a win-win.

I understand the thought behind your suggestion, but the desired layout of
the report leaves me with few or no other options. For one, how does a

[quoted text clipped - 59 lines]

 

[David H]

Nope - I was refering to calculated fields in recordsets as in 'SELECT
LastName & ", " & FirstName As ClientName From tblClients' using the fact
that a query can be a recordset as the frame of reference. While calculated
fields in recordsets can be used, they come at the price of performance. I
stay away from them as much as banks with any degree of intelligence stayed
away from sub-prime loans. I tend to follow the general rule to let the data
be the data and the presentation be the presentation. If you have a
calculated field that combines First Name and Last Name and then need to SORT
the two, you end up with three columns in the query - one of which is
completely unnecessary as the form or report can handle the calculation.

 

[David H]

Did you consider my suggestion of only using one unbound control and then
building the text for it in code? Line returns are accomplished by adding
Chr(13) to the end of the line with a simple " " at the start of the next?
Given that you're building the final string based whether or not each value
should be included, it eliminates the need to set each individual control to
the appropriate source.

I understand the thought behind your suggestion, but the desired layout of
the report leaves me with few or no other options. For one, how does a
person get the 2nd-5th lines to indent other than to add a " " in the code?
When the amount of data is large and the report needs to be as compact as
possible, sometimes it means going to extreme measures.

As Tony Toews suggested, the answer was putting the coding in the OnFormat
event rather than the OnPrint.

Thanks,
Jeffrey

this seems like an enormous amount of trouble to go to, and results in
inconsistent presentation of data - sometimes children are on the name line,
sometimes on the line below; and ditto handling of extra address info.

seems like it would be a whole lot easier to not use any code at all - just
hardcode the controls' ControlSource in the report's Detail section, as

=Lastname & ", " & FirstName
Children
ExtraAddressInformation
FullAddress1
FullAddress2

butt the controls up against each other, vertically, but don't overlap. drag
all the controls' right edges to the maximum length you want to see. set all
the controls' CanGrow AND CanShrink properties to True. and set the *Detail
section's* CanGrow and CanShrink properties to True also. as long as you
don't have zero-length string values in any of these fields in the table, it
should work fine - allowing only as much space as is needed to display the
available data from each record.

the only other thing i might do would be to add a calculated field to the
report's RecordSource, as

FullName: Lastname & ", " & FirstName

then you could simply set that name control's ControlSource to the
calculated fieldname, as

FullName

hth

I've posted my problem in the Reports/Printing section of the forum, but
after some time it seems like it is a VBA issue. I am using VBA to populate

[quoted text clipped - 59 lines]

Else
Me.txtLine1 = Me.Lastname & ", " & Me.Firstname & "; " & Me.Children
Me.txtLine2 = Me.FullAddress1
Me.txtLine3 = Me.FullAddress2

[quoted text clipped - 37 lines]

Else
If Len(Me.Lastname & Me.Firstname & Me.ExtraAddressInformation) > Me.
NameLineLength Then
Me.txtLine1 = Me.Lastname & ", " & Me.Firstname & "; " & Me.

[quoted text clipped - 21 lines]

Thanks,
Jeffrey

 

[tina]

i think this is the first time i've seen an argument *against* using
calculated fields in queries or form/report SQL statements. but then there
are a whole lot of newsgroup posts, and other forums, that i never see. i'll
leave it up to the experts in these ngs to disucss the point, if any care
to.

 

[JimBurke via AccessMonster.com]

In my experience with using something like 'SELECT LastName & ", " &
FirstName ..., which I do quite a bit, I only use the concatenated field, not
the individual fields of LastName and FirstName, so there's actually one less
field in the query. Unless there's something I don't understand about how
Access accomplishes this internally. If you have no need for the individual
names and only need the concatenated value, then in a sense the concatenated
value really IS the data. If you will also need the individual LastName and
FirstName fields, that's a different story, but in all the many times I've
used this I've only needed the conc. field.

Nope - I was refering to calculated fields in recordsets as in 'SELECT
LastName & ", " & FirstName As ClientName From tblClients' using the fact
that a query can be a recordset as the frame of reference. While calculated
fields in recordsets can be used, they come at the price of performance. I
stay away from them as much as banks with any degree of intelligence stayed
away from sub-prime loans. I tend to follow the general rule to let the data
be the data and the presentation be the presentation. If you have a
calculated field that combines First Name and Last Name and then need to SORT
the two, you end up with three columns in the query - one of which is
completely unnecessary as the form or report can handle the calculation.

i think you're confusing calculated fields in recordsets with calculated
values stored in tables, David. the latter breaks normalization rules and

[quoted text clipped - 49 lines]

 

[John W. Vinson]

In my experience with using something like 'SELECT LastName & ", " &
FirstName ..., which I do quite a bit, I only use the concatenated field, not
the individual fields of LastName and FirstName, so there's actually one less
field in the query. Unless there's something I don't understand about how
Access accomplishes this internally. If you have no need for the individual
names and only need the concatenated value, then in a sense the concatenated
value really IS the data. If you will also need the individual LastName and
FirstName fields, that's a different story, but in all the many times I've
used this I've only needed the conc. field.

Tina, about the only advantage to including the individual fields in the query
is that you can use them in an ORDER BY clause to sort the records. Assuming
that (as they should!) the fields are indexed, this will sort the records much
faster than sorting the calculated concatenated field would. The individual
fields would not need to be shown (certainly not in the combo box, or even in
the query's SELECT clause).