Show or hide a column in a report based on a field

[Kari]

I'm having trouble figuring out how to hide a column in a report (actually
it's in a subreport, but if I knew how to do it in a report I could probably
figure out the rest).

I have a Purchase Order report with a subreport of Order Details. Some
orders have a field [Item No], but others do not. If the field is blank I
would like to hide the column and make another column wider. An order would
either have an Item No for each line, or not have any at all. (barring human
error!

I could have two subreports, one with [Item No] and one without, and use
some code to choose which one to display. That seems like a clunky way to
achieve what I'm after. Is there a better way?

Thanks for your help!

Kari

 

[Duane Hookom]

If a field is blank, there should be no reason to hide it. To change the
width of another text box in the report, you would add code to the On Format
event of the report section containing the controls.

If IsNull(Me.txtItemNo) Then
Me.txtAnotherColumn.Width = 2000 'wider
Else
Me.txtAnotherColumn.Width = 1000 ' narrower
End If

 

[Kari]

Duane,

Thanks for the tip. I need to hide the field, and associated heading, so I
can move the other columns to the left and make the other fields wider (and
still fit on the page).

Your code looks like what I need, except using the OnFormat event isn't
working. That event only triggers when going to print preview view (and I
would assume printing the report). However, I want to be able to view the
report in Report view. I've tried moving the code to the OnLoad event for
the report, but I'm having trouble. I still have some things to try in that
department.

I'm using Access 2007 which may be causing this difficulty.

BTW what units does the Width setting use? It looks like the same units
that datasheet columns use, which I"ve never been able to figure out.

I will repost after I've working on getting the OnLoad event to work for me.

Thanks for your help.

Kari

If a field is blank, there should be no reason to hide it. To change the
width of another text box in the report, you would add code to the On Format
event of the report section containing the controls.

If IsNull(Me.txtItemNo) Then
Me.txtAnotherColumn.Width = 2000 'wider
Else
Me.txtAnotherColumn.Width = 1000 ' narrower
End If


--
Duane Hookom
Microsoft Access MVP

I'm having trouble figuring out how to hide a column in a report (actually
it's in a subreport, but if I knew how to do it in a report I could probably
figure out the rest).

I have a Purchase Order report with a subreport of Order Details. Some
orders have a field [Item No], but others do not. If the field is blank I
would like to hide the column and make another column wider. An order would
either have an Item No for each line, or not have any at all. (barring human
error!

I could have two subreports, one with [Item No] and one without, and use
some code to choose which one to display. That seems like a clunky way to
achieve what I'm after. Is there a better way?

Thanks for your help!

Kari

 

[Duane Hookom]

Please re-state your actual requirements. Your first attempt was a bit
lacking in details. Do you expect multiple columns to grow/shrink or just
move left?

How would this affect labels in header sections?
--
Duane Hookom
Microsoft Access MVP

Duane,

Thanks for the tip. I need to hide the field, and associated heading, so I
can move the other columns to the left and make the other fields wider (and
still fit on the page).

Your code looks like what I need, except using the OnFormat event isn't
working. That event only triggers when going to print preview view (and I
would assume printing the report). However, I want to be able to view the
report in Report view. I've tried moving the code to the OnLoad event for
the report, but I'm having trouble. I still have some things to try in that
department.

I'm using Access 2007 which may be causing this difficulty.

BTW what units does the Width setting use? It looks like the same units
that datasheet columns use, which I"ve never been able to figure out.

I will repost after I've working on getting the OnLoad event to work for me.

Thanks for your help.

Kari

If a field is blank, there should be no reason to hide it. To change the
width of another text box in the report, you would add code to the On Format
event of the report section containing the controls.

If IsNull(Me.txtItemNo) Then
Me.txtAnotherColumn.Width = 2000 'wider
Else
Me.txtAnotherColumn.Width = 1000 ' narrower
End If


--
Duane Hookom
Microsoft Access MVP

I'm having trouble figuring out how to hide a column in a report (actually
it's in a subreport, but if I knew how to do it in a report I could probably
figure out the rest).

I have a Purchase Order report with a subreport of Order Details. Some
orders have a field [Item No], but others do not. If the field is blank I
would like to hide the column and make another column wider. An order would
either have an Item No for each line, or not have any at all. (barring human
error!

I could have two subreports, one with [Item No] and one without, and use
some code to choose which one to display. That seems like a clunky way to
achieve what I'm after. Is there a better way?

Thanks for your help!

Kari

 

[Kari]

Duane,

Sorry for the lack of details; I've had my nose in this report so long it
seems like it's common knowledge.

My subreport is structured to look like a datasheet. When there are no
[Item no]'s I want to hide that column (the first column on the left), shift
the other columns left, and make 2 columns wider. (All of this is due to
limited real estate: the subreport barely fits, width wise, on an 8-1/2 x 11
sheet of paper. Some columns have been squished too much to show all their
data. So, when the extra space is available, i.e. there is no [Item No], I
want to use the space for the columns that need it.)

I will also need to adjust the column headings in the Page Header section,
and the lines I drew (also in the Page Header section).

This whole operation was much easier in datasheet view of a form, but using
a form as a subreport has it's own headaches.

Are things as clear as mud now?

Honestly, I really appreciate your help and stand ready to answer any
questions that will get us to a resolution quickly.

I'm still having trouble figuring out where to put the code. I can't put it
in any of the events associated with the main report because the subreport
isn't open yet (so I can's test a field there or change field widths there).

When the report/subreport is opened I can't get any of the subreport events
to fire except for OnOpen. I checked all relevant events by putting a
message box in each one (e.g. "subreport OnActivate") and then opening the
report. The OnFormat events don't trigger when looking just at Report view
(for the main and the subreport) so I can't use them.

I'm really stuck. What am I doing wrong?

Thank you so much for your continued help.

Kari

Please re-state your actual requirements. Your first attempt was a bit
lacking in details. Do you expect multiple columns to grow/shrink or just
move left?

How would this affect labels in header sections?
--
Duane Hookom
Microsoft Access MVP

Duane,

Thanks for the tip. I need to hide the field, and associated heading, so I
can move the other columns to the left and make the other fields wider (and
still fit on the page).

Your code looks like what I need, except using the OnFormat event isn't
working. That event only triggers when going to print preview view (and I
would assume printing the report). However, I want to be able to view the
report in Report view. I've tried moving the code to the OnLoad event for
the report, but I'm having trouble. I still have some things to try in that
department.

I'm using Access 2007 which may be causing this difficulty.

BTW what units does the Width setting use? It looks like the same units
that datasheet columns use, which I"ve never been able to figure out.

I will repost after I've working on getting the OnLoad event to work for me.

Thanks for your help.

Kari

If a field is blank, there should be no reason to hide it. To change the
width of another text box in the report, you would add code to the On Format
event of the report section containing the controls.

If IsNull(Me.txtItemNo) Then
Me.txtAnotherColumn.Width = 2000 'wider
Else
Me.txtAnotherColumn.Width = 1000 ' narrower
End If


--
Duane Hookom
Microsoft Access MVP


:

I'm having trouble figuring out how to hide a column in a report (actually
it's in a subreport, but if I knew how to do it in a report I could probably
figure out the rest).

I have a Purchase Order report with a subreport of Order Details. Some
orders have a field [Item No], but others do not. If the field is blank I
would like to hide the column and make another column wider. An order would
either have an Item No for each line, or not have any at all. (barring human
error!

I could have two subreports, one with [Item No] and one without, and use
some code to choose which one to display. That seems like a clunky way to
achieve what I'm after. Is there a better way?

Thanks for your help!

Kari

 

[Chuck]

Duane,

Sorry for the lack of details; I've had my nose in this report so long it
seems like it's common knowledge.

My subreport is structured to look like a datasheet. When there are no
[Item no]'s I want to hide that column (the first column on the left), shift
the other columns left, and make 2 columns wider. (All of this is due to
limited real estate: the subreport barely fits, width wise, on an 8-1/2 x 11
sheet of paper. Some columns have been squished too much to show all their
data. So, when the extra space is available, i.e. there is no [Item No], I
want to use the space for the columns that need it.)

I will also need to adjust the column headings in the Page Header section,
and the lines I drew (also in the Page Header section).

This whole operation was much easier in datasheet view of a form, but using
a form as a subreport has it's own headaches.

Are things as clear as mud now?

Honestly, I really appreciate your help and stand ready to answer any
questions that will get us to a resolution quickly.

I'm still having trouble figuring out where to put the code. I can't put it
in any of the events associated with the main report because the subreport
isn't open yet (so I can's test a field there or change field widths there).

When the report/subreport is opened I can't get any of the subreport events
to fire except for OnOpen. I checked all relevant events by putting a
message box in each one (e.g. "subreport OnActivate") and then opening the
report. The OnFormat events don't trigger when looking just at Report view
(for the main and the subreport) so I can't use them.

I'm really stuck. What am I doing wrong?

Thank you so much for your continued help.

Kari

Please re-state your actual requirements. Your first attempt was a bit
lacking in details. Do you expect multiple columns to grow/shrink or just
move left?

How would this affect labels in header sections?
--
Duane Hookom
Microsoft Access MVP

Duane,

Thanks for the tip. I need to hide the field, and associated heading, so I
can move the other columns to the left and make the other fields wider (and
still fit on the page).

Your code looks like what I need, except using the OnFormat event isn't
working. That event only triggers when going to print preview view (and I
would assume printing the report). However, I want to be able to view the
report in Report view. I've tried moving the code to the OnLoad event for
the report, but I'm having trouble. I still have some things to try in that
department.

I'm using Access 2007 which may be causing this difficulty.

BTW what units does the Width setting use? It looks like the same units
that datasheet columns use, which I"ve never been able to figure out.

I will repost after I've working on getting the OnLoad event to work for me.

Thanks for your help.

Kari

:

If a field is blank, there should be no reason to hide it. To change the
width of another text box in the report, you would add code to the On Format
event of the report section containing the controls.

If IsNull(Me.txtItemNo) Then
Me.txtAnotherColumn.Width = 2000 'wider
Else
Me.txtAnotherColumn.Width = 1000 ' narrower
End If


--
Duane Hookom
Microsoft Access MVP


:

I'm having trouble figuring out how to hide a column in a report (actually
it's in a subreport, but if I knew how to do it in a report I could probably
figure out the rest).

I have a Purchase Order report with a subreport of Order Details. Some
orders have a field [Item No], but others do not. If the field is blank I
would like to hide the column and make another column wider. An order would
either have an Item No for each line, or not have any at all. (barring human
error!

I could have two subreports, one with [Item No] and one without, and use
some code to choose which one to display. That seems like a clunky way to
achieve what I'm after. Is there a better way?

Thanks for your help!

Kari

For what little it's worth. When I'm reading a data report, I want all the
pages to have the same formatting. It would be disconcerting to have columns
come and go from page to page. Could it happen in the middle of a page? No
data - leave it blank or insert "n.a." You could use a smaller font or print
landscape. Just keep the report the same beginning to end.

Chuck
--

 

[Kari]

Chuck,

You make some good points; it *would* be disconcerting to have columns come
and go. However, this is a 1 page order sheet. The problem arises because
we have 1 customer (our largest customer naturally) that uses Item Numbers.
Luckily they don't use long Color names. So if I can use the space for one
or the other field I can keep the document in portrait orientation (much
easier to read when it is e-mailed to folks) and see everything I need to.
And keep the font large enough so that people can read it reasonably well.

Thanks for your 2 cents. It does help me understand why I haven't found any
one else trying to do something similar on the boards.

Kari

:

..>

 

[Duane Hookom]

I would add a value to the tag property of every control you want to either
hide or move. For instance, select them all and enter "HasItemNum" in the tag
property. Create a new set of controls with the moved and wider text boxes.
Set the tag property of all these new text boxes to "NoItemNum".

It isn't clear if all the records in the subreport will have either Item
Numbers or not. You might need to add a text box in the subreport Header
Name: txtCountItemNum
Control Source: =Count([Item No])

Then add code to the On Format event of the subreport's detail section like:

Dim ctl as Control
For Each ctl in Me.Controls
If ctl.tag = "HasItemNum"
ctl.Visible = Me.txtCountItemNum > 0
End If
If ctl.tag = "NoItemNum"
ctl.Visible = Me.txtCountItemNum = 0
End If

 

[Kari]

Duane,

For each Order (main report) each item in Order Details (subreport) will
either have an Item No or not, i.e. it will be the same for all detail
records in the same Order. Sorry I didn't make that clear.

I will be out of the office from now until Friday morning; I will try your
suggestions then. I'm a little worried about putting the code in the
OnFormat event of the subreport's detail section since I haven't been able to
get that event to fire when using the Report view (Access 2007). In another
post Allen Browne verified that it does not fire in Report view, which is
apparantly new to Access 2007.

I will be back in the fray on Friday. Thanks for all your help so far.

Kari

I would add a value to the tag property of every control you want to either
hide or move. For instance, select them all and enter "HasItemNum" in the tag
property. Create a new set of controls with the moved and wider text boxes.
Set the tag property of all these new text boxes to "NoItemNum".

It isn't clear if all the records in the subreport will have either Item
Numbers or not. You might need to add a text box in the subreport Header
Name: txtCountItemNum
Control Source: =Count([Item No])

Then add code to the On Format event of the subreport's detail section like:

Dim ctl as Control
For Each ctl in Me.Controls
If ctl.tag = "HasItemNum"
ctl.Visible = Me.txtCountItemNum > 0
End If
If ctl.tag = "NoItemNum"
ctl.Visible = Me.txtCountItemNum = 0
End If

--
Duane Hookom
Microsoft Access MVP

Chuck,

You make some good points; it *would* be disconcerting to have columns come
and go. However, this is a 1 page order sheet. The problem arises because
we have 1 customer (our largest customer naturally) that uses Item Numbers.
Luckily they don't use long Color names. So if I can use the space for one
or the other field I can keep the document in portrait orientation (much
easier to read when it is e-mailed to folks) and see everything I need to.
And keep the font large enough so that people can read it reasonably well.

Thanks for your 2 cents. It does help me understand why I haven't found any
one else trying to do something similar on the boards.

Kari

:

.>

 

[Duane Hookom]

Allen Browne is correct as usual. The code will execute in some modes and
this is the simplest solution that I am can think of other than using two
different subreports.

--
Duane Hookom
Microsoft Access MVP

Duane,

For each Order (main report) each item in Order Details (subreport) will
either have an Item No or not, i.e. it will be the same for all detail
records in the same Order. Sorry I didn't make that clear.

I will be out of the office from now until Friday morning; I will try your
suggestions then. I'm a little worried about putting the code in the
OnFormat event of the subreport's detail section since I haven't been able to
get that event to fire when using the Report view (Access 2007). In another
post Allen Browne verified that it does not fire in Report view, which is
apparantly new to Access 2007.

I will be back in the fray on Friday. Thanks for all your help so far.

Kari

I would add a value to the tag property of every control you want to either
hide or move. For instance, select them all and enter "HasItemNum" in the tag
property. Create a new set of controls with the moved and wider text boxes.
Set the tag property of all these new text boxes to "NoItemNum".

It isn't clear if all the records in the subreport will have either Item
Numbers or not. You might need to add a text box in the subreport Header
Name: txtCountItemNum
Control Source: =Count([Item No])

Then add code to the On Format event of the subreport's detail section like:

Dim ctl as Control
For Each ctl in Me.Controls
If ctl.tag = "HasItemNum"
ctl.Visible = Me.txtCountItemNum > 0
End If
If ctl.tag = "NoItemNum"
ctl.Visible = Me.txtCountItemNum = 0
End If

--
Duane Hookom
Microsoft Access MVP

Chuck,

You make some good points; it *would* be disconcerting to have columns come
and go. However, this is a 1 page order sheet. The problem arises because
we have 1 customer (our largest customer naturally) that uses Item Numbers.
Luckily they don't use long Color names. So if I can use the space for one
or the other field I can keep the document in portrait orientation (much
easier to read when it is e-mailed to folks) and see everything I need to.
And keep the font large enough so that people can read it reasonably well.

Thanks for your 2 cents. It does help me understand why I haven't found any
one else trying to do something similar on the boards.

Kari

:

.>
For what little it's worth. When I'm reading a data report, I want all the
pages to have the same formatting. It would be disconcerting to have columns
come and go from page to page. Could it happen in the middle of a page? No
data - leave it blank or insert "n.a." You could use a smaller font or print
landscape. Just keep the report the same beginning to end.

Chuck

 

[Kari]

Duane,

I need to view this report in Report view, so I guess I will go the route of
two subreports. I'm thinking the code to decide which subreport to display
will go in the OnOpen or OnLoad event of the main report. Is there a reason
to use one over the other in this case?

Also, the field I need to check to determine which subreport to use is
located in the subreport. Since the subreport won't be open yet I guess I'll
have to use a recordset to check the field--am I headed in the right
direction?

Thanks for sharing your expertise with me.

Kari

Allen Browne is correct as usual. The code will execute in some modes and
this is the simplest solution that I am can think of other than using two
different subreports.

--
Duane Hookom
Microsoft Access MVP

Duane,

For each Order (main report) each item in Order Details (subreport) will
either have an Item No or not, i.e. it will be the same for all detail
records in the same Order. Sorry I didn't make that clear.

I will be out of the office from now until Friday morning; I will try your
suggestions then. I'm a little worried about putting the code in the
OnFormat event of the subreport's detail section since I haven't been able to
get that event to fire when using the Report view (Access 2007). In another
post Allen Browne verified that it does not fire in Report view, which is
apparantly new to Access 2007.

I will be back in the fray on Friday. Thanks for all your help so far.

Kari

I would add a value to the tag property of every control you want to either
hide or move. For instance, select them all and enter "HasItemNum" in the tag
property. Create a new set of controls with the moved and wider text boxes.
Set the tag property of all these new text boxes to "NoItemNum".

It isn't clear if all the records in the subreport will have either Item
Numbers or not. You might need to add a text box in the subreport Header
Name: txtCountItemNum
Control Source: =Count([Item No])

Then add code to the On Format event of the subreport's detail section like:

Dim ctl as Control
For Each ctl in Me.Controls
If ctl.tag = "HasItemNum"
ctl.Visible = Me.txtCountItemNum > 0
End If
If ctl.tag = "NoItemNum"
ctl.Visible = Me.txtCountItemNum = 0
End If

--
Duane Hookom
Microsoft Access MVP


:

Chuck,

You make some good points; it *would* be disconcerting to have columns come
and go. However, this is a 1 page order sheet. The problem arises because
we have 1 customer (our largest customer naturally) that uses Item Numbers.
Luckily they don't use long Color names. So if I can use the space for one
or the other field I can keep the document in portrait orientation (much
easier to read when it is e-mailed to folks) and see everything I need to.
And keep the font large enough so that people can read it reasonably well.

Thanks for your 2 cents. It does help me understand why I haven't found any
one else trying to do something similar on the boards.

Kari

:

.>
For what little it's worth. When I'm reading a data report, I want all the
pages to have the same formatting. It would be disconcerting to have columns
come and go from page to page. Could it happen in the middle of a page? No
data - leave it blank or insert "n.a." You could use a smaller font or print
landscape. Just keep the report the same beginning to end.

Chuck

 

[Duane Hookom]

I would place code to hide or display a subreport in the On Format event of
the section of the report that contains the subreport.

You should almost always be able to aggregate the value from a subreport's
record source into the main report's record source.

--
Duane Hookom
Microsoft Access MVP

Duane,

I need to view this report in Report view, so I guess I will go the route of
two subreports. I'm thinking the code to decide which subreport to display
will go in the OnOpen or OnLoad event of the main report. Is there a reason
to use one over the other in this case?

Also, the field I need to check to determine which subreport to use is
located in the subreport. Since the subreport won't be open yet I guess I'll
have to use a recordset to check the field--am I headed in the right
direction?

Thanks for sharing your expertise with me.

Kari

Allen Browne is correct as usual. The code will execute in some modes and
this is the simplest solution that I am can think of other than using two
different subreports.

--
Duane Hookom
Microsoft Access MVP

Duane,

For each Order (main report) each item in Order Details (subreport) will
either have an Item No or not, i.e. it will be the same for all detail
records in the same Order. Sorry I didn't make that clear.

I will be out of the office from now until Friday morning; I will try your
suggestions then. I'm a little worried about putting the code in the
OnFormat event of the subreport's detail section since I haven't been able to
get that event to fire when using the Report view (Access 2007). In another
post Allen Browne verified that it does not fire in Report view, which is
apparantly new to Access 2007.

I will be back in the fray on Friday. Thanks for all your help so far.

Kari

:

I would add a value to the tag property of every control you want to either
hide or move. For instance, select them all and enter "HasItemNum" in the tag
property. Create a new set of controls with the moved and wider text boxes.
Set the tag property of all these new text boxes to "NoItemNum".

It isn't clear if all the records in the subreport will have either Item
Numbers or not. You might need to add a text box in the subreport Header
Name: txtCountItemNum
Control Source: =Count([Item No])

Then add code to the On Format event of the subreport's detail section like:

Dim ctl as Control
For Each ctl in Me.Controls
If ctl.tag = "HasItemNum"
ctl.Visible = Me.txtCountItemNum > 0
End If
If ctl.tag = "NoItemNum"
ctl.Visible = Me.txtCountItemNum = 0
End If

--
Duane Hookom
Microsoft Access MVP


:

Chuck,

You make some good points; it *would* be disconcerting to have columns come
and go. However, this is a 1 page order sheet. The problem arises because
we have 1 customer (our largest customer naturally) that uses Item Numbers.
Luckily they don't use long Color names. So if I can use the space for one
or the other field I can keep the document in portrait orientation (much
easier to read when it is e-mailed to folks) and see everything I need to.
And keep the font large enough so that people can read it reasonably well.

Thanks for your 2 cents. It does help me understand why I haven't found any
one else trying to do something similar on the boards.

Kari

:

.>
For what little it's worth. When I'm reading a data report, I want all the
pages to have the same formatting. It would be disconcerting to have columns
come and go from page to page. Could it happen in the middle of a page? No
data - leave it blank or insert "n.a." You could use a smaller font or print
landscape. Just keep the report the same beginning to end.

Chuck

 

[Kari]

Duane,

The website is acting funny--I hope this isn't a duplicate post. My
apologies if it is.

I thought I had it all figured out, but no go. I put the code in the OnLoad
event, since the OnFormat event doesn't fire in the view I'm using (Report
view, Access 2007). The If..Then evaluates correctly (thanks to your tip to
put the field needed in the main report, as well as the subreport--made
things much less complex).

Now I can't figure out how to tell Access to put the right report in the
subreport "container."

This is what I have:

Dim rptSub 'I can't figure out what to declare this as; it's not a
Control, Report or Container
rptSub = Reports![PO Report]![OrderDetailsSubreport]

If IsNull(Me.[Item #]) Then
MsgBox ("no Item #")
rptSub.SourceObject = Report.[PO Report Order Details subreport]

Else
MsgBox ("Item #")
rptSub.SourceObject = [PO Report Order Details subreport w Item No]

End If


I get an error: "Runtime error 2465: MS Office Access can't find field "|"
referred to in your expression," on the line that tries to set the
SourceObject.

I think this may be just a syntax error, but I can't find any documentaion
on how to set the SourceObject via VB. Do I need to set the Master/Child
links as well? They are the same (same field names) for both subreports, so
perhaps I can just type them in and leave it at that (that's how they are
now).

Once again I'm stumped. . . . . . . . . . . . .

Kari

I would place code to hide or display a subreport in the On Format event of
the section of the report that contains the subreport.

You should almost always be able to aggregate the value from a subreport's
record source into the main report's record source.

--
Duane Hookom
Microsoft Access MVP

Duane,

I need to view this report in Report view, so I guess I will go the route of
two subreports. I'm thinking the code to decide which subreport to display
will go in the OnOpen or OnLoad event of the main report. Is there a reason
to use one over the other in this case?

Also, the field I need to check to determine which subreport to use is
located in the subreport. Since the subreport won't be open yet I guess I'll
have to use a recordset to check the field--am I headed in the right
direction?

Thanks for sharing your expertise with me.

Kari

Allen Browne is correct as usual. The code will execute in some modes and
this is the simplest solution that I am can think of other than using two
different subreports.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

For each Order (main report) each item in Order Details (subreport) will
either have an Item No or not, i.e. it will be the same for all detail
records in the same Order. Sorry I didn't make that clear.

I will be out of the office from now until Friday morning; I will try your
suggestions then. I'm a little worried about putting the code in the
OnFormat event of the subreport's detail section since I haven't been able to
get that event to fire when using the Report view (Access 2007). In another
post Allen Browne verified that it does not fire in Report view, which is
apparantly new to Access 2007.

I will be back in the fray on Friday. Thanks for all your help so far.

Kari

:

I would add a value to the tag property of every control you want to either
hide or move. For instance, select them all and enter "HasItemNum" in the tag
property. Create a new set of controls with the moved and wider text boxes.
Set the tag property of all these new text boxes to "NoItemNum".

It isn't clear if all the records in the subreport will have either Item
Numbers or not. You might need to add a text box in the subreport Header
Name: txtCountItemNum
Control Source: =Count([Item No])

Then add code to the On Format event of the subreport's detail section like:

Dim ctl as Control
For Each ctl in Me.Controls
If ctl.tag = "HasItemNum"
ctl.Visible = Me.txtCountItemNum > 0
End If
If ctl.tag = "NoItemNum"
ctl.Visible = Me.txtCountItemNum = 0
End If

--
Duane Hookom
Microsoft Access MVP


:

Chuck,

You make some good points; it *would* be disconcerting to have columns come
and go. However, this is a 1 page order sheet. The problem arises because
we have 1 customer (our largest customer naturally) that uses Item Numbers.
Luckily they don't use long Color names. So if I can use the space for one
or the other field I can keep the document in portrait orientation (much
easier to read when it is e-mailed to folks) and see everything I need to.
And keep the font large enough so that people can read it reasonably well.

Thanks for your 2 cents. It does help me understand why I haven't found any
one else trying to do something similar on the boards.

Kari

:

.>
For what little it's worth. When I'm reading a data report, I want all the
pages to have the same formatting. It would be disconcerting to have columns
come and go from page to page. Could it happen in the middle of a page? No
data - leave it blank or insert "n.a." You could use a smaller font or print
landscape. Just keep the report the same beginning to end.

Chuck

 

[Duane Hookom]

I would put both subreports in the main report and then make one visible and
the other hidden.

--
Duane Hookom
Microsoft Access MVP

Duane,

The website is acting funny--I hope this isn't a duplicate post. My
apologies if it is.

I thought I had it all figured out, but no go. I put the code in the OnLoad
event, since the OnFormat event doesn't fire in the view I'm using (Report
view, Access 2007). The If..Then evaluates correctly (thanks to your tip to
put the field needed in the main report, as well as the subreport--made
things much less complex).

Now I can't figure out how to tell Access to put the right report in the
subreport "container."

This is what I have:

Dim rptSub 'I can't figure out what to declare this as; it's not a
Control, Report or Container
rptSub = Reports![PO Report]![OrderDetailsSubreport]

If IsNull(Me.[Item #]) Then
MsgBox ("no Item #")
rptSub.SourceObject = Report.[PO Report Order Details subreport]

Else
MsgBox ("Item #")
rptSub.SourceObject = [PO Report Order Details subreport w Item No]

End If


I get an error: "Runtime error 2465: MS Office Access can't find field "|"
referred to in your expression," on the line that tries to set the
SourceObject.

I think this may be just a syntax error, but I can't find any documentaion
on how to set the SourceObject via VB. Do I need to set the Master/Child
links as well? They are the same (same field names) for both subreports, so
perhaps I can just type them in and leave it at that (that's how they are
now).

Once again I'm stumped. . . . . . . . . . . . .

Kari

I would place code to hide or display a subreport in the On Format event of
the section of the report that contains the subreport.

You should almost always be able to aggregate the value from a subreport's
record source into the main report's record source.

--
Duane Hookom
Microsoft Access MVP

Duane,

I need to view this report in Report view, so I guess I will go the route of
two subreports. I'm thinking the code to decide which subreport to display
will go in the OnOpen or OnLoad event of the main report. Is there a reason
to use one over the other in this case?

Also, the field I need to check to determine which subreport to use is
located in the subreport. Since the subreport won't be open yet I guess I'll
have to use a recordset to check the field--am I headed in the right
direction?

Thanks for sharing your expertise with me.

Kari

:

Allen Browne is correct as usual. The code will execute in some modes and
this is the simplest solution that I am can think of other than using two
different subreports.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

For each Order (main report) each item in Order Details (subreport) will
either have an Item No or not, i.e. it will be the same for all detail
records in the same Order. Sorry I didn't make that clear.

I will be out of the office from now until Friday morning; I will try your
suggestions then. I'm a little worried about putting the code in the
OnFormat event of the subreport's detail section since I haven't been able to
get that event to fire when using the Report view (Access 2007). In another
post Allen Browne verified that it does not fire in Report view, which is
apparantly new to Access 2007.

I will be back in the fray on Friday. Thanks for all your help so far.

Kari

:

I would add a value to the tag property of every control you want to either
hide or move. For instance, select them all and enter "HasItemNum" in the tag
property. Create a new set of controls with the moved and wider text boxes.
Set the tag property of all these new text boxes to "NoItemNum".

It isn't clear if all the records in the subreport will have either Item
Numbers or not. You might need to add a text box in the subreport Header
Name: txtCountItemNum
Control Source: =Count([Item No])

Then add code to the On Format event of the subreport's detail section like:

Dim ctl as Control
For Each ctl in Me.Controls
If ctl.tag = "HasItemNum"
ctl.Visible = Me.txtCountItemNum > 0
End If
If ctl.tag = "NoItemNum"
ctl.Visible = Me.txtCountItemNum = 0
End If

--
Duane Hookom
Microsoft Access MVP


:

Chuck,

You make some good points; it *would* be disconcerting to have columns come
and go. However, this is a 1 page order sheet. The problem arises because
we have 1 customer (our largest customer naturally) that uses Item Numbers.
Luckily they don't use long Color names. So if I can use the space for one
or the other field I can keep the document in portrait orientation (much
easier to read when it is e-mailed to folks) and see everything I need to.
And keep the font large enough so that people can read it reasonably well.

Thanks for your 2 cents. It does help me understand why I haven't found any
one else trying to do something similar on the boards.

Kari

:

.>
For what little it's worth. When I'm reading a data report, I want all the
pages to have the same formatting. It would be disconcerting to have columns
come and go from page to page. Could it happen in the middle of a page? No
data - leave it blank or insert "n.a." You could use a smaller font or print
landscape. Just keep the report the same beginning to end.

Chuck

 

[Kari]

Duane,

Thank you so much!

I was so focused on how to get my way to work, that I neglected to think
there might be other ways to accomplish my goal.

Using two subreports and making the correct one visible works great. Thanks
for sticking with me to the bitter end!

Kari

I would put both subreports in the main report and then make one visible and
the other hidden.

--
Duane Hookom
Microsoft Access MVP

Duane,

The website is acting funny--I hope this isn't a duplicate post. My
apologies if it is.

I thought I had it all figured out, but no go. I put the code in the OnLoad
event, since the OnFormat event doesn't fire in the view I'm using (Report
view, Access 2007). The If..Then evaluates correctly (thanks to your tip to
put the field needed in the main report, as well as the subreport--made
things much less complex).

Now I can't figure out how to tell Access to put the right report in the
subreport "container."

This is what I have:

Dim rptSub 'I can't figure out what to declare this as; it's not a
Control, Report or Container
rptSub = Reports![PO Report]![OrderDetailsSubreport]

If IsNull(Me.[Item #]) Then
MsgBox ("no Item #")
rptSub.SourceObject = Report.[PO Report Order Details subreport]

Else
MsgBox ("Item #")
rptSub.SourceObject = [PO Report Order Details subreport w Item No]

End If


I get an error: "Runtime error 2465: MS Office Access can't find field "|"
referred to in your expression," on the line that tries to set the
SourceObject.

I think this may be just a syntax error, but I can't find any documentaion
on how to set the SourceObject via VB. Do I need to set the Master/Child
links as well? They are the same (same field names) for both subreports, so
perhaps I can just type them in and leave it at that (that's how they are
now).

Once again I'm stumped. . . . . . . . . . . . .

Kari

I would place code to hide or display a subreport in the On Format event of
the section of the report that contains the subreport.

You should almost always be able to aggregate the value from a subreport's
record source into the main report's record source.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

I need to view this report in Report view, so I guess I will go the route of
two subreports. I'm thinking the code to decide which subreport to display
will go in the OnOpen or OnLoad event of the main report. Is there a reason
to use one over the other in this case?

Also, the field I need to check to determine which subreport to use is
located in the subreport. Since the subreport won't be open yet I guess I'll
have to use a recordset to check the field--am I headed in the right
direction?

Thanks for sharing your expertise with me.

Kari

:

Allen Browne is correct as usual. The code will execute in some modes and
this is the simplest solution that I am can think of other than using two
different subreports.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

For each Order (main report) each item in Order Details (subreport) will
either have an Item No or not, i.e. it will be the same for all detail
records in the same Order. Sorry I didn't make that clear.

I will be out of the office from now until Friday morning; I will try your
suggestions then. I'm a little worried about putting the code in the
OnFormat event of the subreport's detail section since I haven't been able to
get that event to fire when using the Report view (Access 2007). In another
post Allen Browne verified that it does not fire in Report view, which is
apparantly new to Access 2007.

I will be back in the fray on Friday. Thanks for all your help so far.

Kari

:

I would add a value to the tag property of every control you want to either
hide or move. For instance, select them all and enter "HasItemNum" in the tag
property. Create a new set of controls with the moved and wider text boxes.
Set the tag property of all these new text boxes to "NoItemNum".

It isn't clear if all the records in the subreport will have either Item
Numbers or not. You might need to add a text box in the subreport Header
Name: txtCountItemNum
Control Source: =Count([Item No])

Then add code to the On Format event of the subreport's detail section like:

Dim ctl as Control
For Each ctl in Me.Controls
If ctl.tag = "HasItemNum"
ctl.Visible = Me.txtCountItemNum > 0
End If
If ctl.tag = "NoItemNum"
ctl.Visible = Me.txtCountItemNum = 0
End If

--
Duane Hookom
Microsoft Access MVP


:

Chuck,

You make some good points; it *would* be disconcerting to have columns come
and go. However, this is a 1 page order sheet. The problem arises because
we have 1 customer (our largest customer naturally) that uses Item Numbers.
Luckily they don't use long Color names. So if I can use the space for one
or the other field I can keep the document in portrait orientation (much
easier to read when it is e-mailed to folks) and see everything I need to.
And keep the font large enough so that people can read it reasonably well.

Thanks for your 2 cents. It does help me understand why I haven't found any
one else trying to do something similar on the boards.

Kari

:

.>
For what little it's worth. When I'm reading a data report, I want all the
pages to have the same formatting. It would be disconcerting to have columns
come and go from page to page. Could it happen in the middle of a page? No
data - leave it blank or insert "n.a." You could use a smaller font or print
landscape. Just keep the report the same beginning to end.

Chuck