On Format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have seen several items regarding Cancel - On format. I am trying to show
line items on my inventory reports only if the sum of a projected quantity
falls below 0. I have not been able to acheive a running sum in my query as
there is no sequential ID number because my query pulls from several tables.
My Projected quantity is a result of an expression in my underlying query.

I am self-taught and need some guidance. Do I use cancel, visible, display
when, etc? and HOW?
 
Lori said:
I have seen several items regarding Cancel - On format. I am trying to show
line items on my inventory reports only if the sum of a projected quantity
falls below 0. I have not been able to acheive a running sum in my query as
there is no sequential ID number because my query pulls from several tables.
My Projected quantity is a result of an expression in my underlying query.

I am self-taught and need some guidance. Do I use cancel, visible, display
when, etc? and HOW?
Click to expand...


If the projected quantity is known/calculated in the query,
then it's best to use it in the query's WHERE clause to
filter it out before it ever gets to the report. Generally,
all it takes is to put <0 in the calculated field's
Criteria.

If you must suppress the record in the report, then you can
use Cancel about as easily as any other way. Use a line of
code in the section's Format event procedure:

Cancel = (Me.[ProjectedQuantity] < 0)
 
Thank you Marshall - The Cancel On Format did work exactly as I needed it to.
Guess I should go to a Visual Basic Class
--
Lori


Marshall Barton said:
Lori said:
I have seen several items regarding Cancel - On format. I am trying to show
line items on my inventory reports only if the sum of a projected quantity
falls below 0. I have not been able to acheive a running sum in my query as
there is no sequential ID number because my query pulls from several tables.
My Projected quantity is a result of an expression in my underlying query.

I am self-taught and need some guidance. Do I use cancel, visible, display
when, etc? and HOW?
Click to expand...


If the projected quantity is known/calculated in the query,
then it's best to use it in the query's WHERE clause to
filter it out before it ever gets to the report. Generally,
all it takes is to put <0 in the calculated field's
Criteria.

If you must suppress the record in the report, then you can
use Cancel about as easily as any other way. Use a line of
code in the section's Format event procedure:

Cancel = (Me.[ProjectedQuantity] < 0)
Click to expand...
 
Marshall

I'm hoping you can guide me once again. The Cancel = Me (etc) worked for the
"Activity section" of my report and I can get it to have the same affect on
my part number group header EXCEPT for the first group on the report - then
the part number group header info does not print just the activity detail.

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Cancel = (Me.RunProject >= 0)

End Sub

Private Sub GroupHeader2_Format(Cancel As Integer, FormatCount As Integer)
Cancel = (Me.RunProject >= 0)

End Sub

Any Suggestions?


--
Lori


Marshall Barton said:
Lori said:
I have seen several items regarding Cancel - On format. I am trying to show
line items on my inventory reports only if the sum of a projected quantity
falls below 0. I have not been able to acheive a running sum in my query as
there is no sequential ID number because my query pulls from several tables.
My Projected quantity is a result of an expression in my underlying query.

I am self-taught and need some guidance. Do I use cancel, visible, display
when, etc? and HOW?
Click to expand...


If the projected quantity is known/calculated in the query,
then it's best to use it in the query's WHERE clause to
filter it out before it ever gets to the report. Generally,
all it takes is to put <0 in the calculated field's
Criteria.

If you must suppress the record in the report, then you can
use Cancel about as easily as any other way. Use a line of
code in the section's Format event procedure:

Cancel = (Me.[ProjectedQuantity] < 0)
Click to expand...
 
Lori said:
I'm hoping you can guide me once again. The Cancel = Me (etc) worked for the
"Activity section" of my report and I can get it to have the same affect on
my part number group header EXCEPT for the first group on the report - then
the part number group header info does not print just the activity detail.

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Cancel = (Me.RunProject >= 0)

End Sub

Private Sub GroupHeader2_Format(Cancel As Integer, FormatCount As Integer)
Cancel = (Me.RunProject >= 0)

End Sub
Click to expand...


I don't understand. Is the problem that the part header
doesn't print or that the details do print???

Maybe you're chasing down a more complicated path than the
preferred approach of setting the criteria for the
RunProject field. Did you try that? If you did, what made
you use the Cancel approach instead?
 
Hello Marshall

I am actually not printing the "detail" section of the report at all. I am
cancelling Group Header 2 (Activity) based on the RunProject field (in that
section of the report) working great. But when I try to cancel Group Header 0
(the part number section) based on the same field in Group Header 2, it works
throughout the report except on the first group of the report - it goes from
my Page header right to Group Header 2. Why would I miss only the first group?
 
Lori said:
I am actually not printing the "detail" section of the report at all. I am
cancelling Group Header 2 (Activity) based on the RunProject field (in that
section of the report) working great. But when I try to cancel Group Header 0
(the part number section) based on the same field in Group Header 2, it works
throughout the report except on the first group of the report - it goes from
my Page header right to Group Header 2. Why would I miss only the first group?
Click to expand...


I don't know why that would happen, but the report sounds
rather complicated so that's no surprise. I guess I need
more details to figure all this out.

How does this RunProject field apply to the different
groups? Normally, a value is different for each detail or
each low level group, but not the same for all records???

Lacking more details, I'll take a stab at it and guess that
the RunProject field is not bound to a text box in
GroupHeader0. Try adding a text box for this field in
each(?) header and change the code to refer to the text box
instead of directly to the field.
 
This database has been quite a project and I am down to this report to make
it possible to replace 6 separate Excel spreadsheets for inventory
management. It links to the Customer Database and Time Tracking Database I
created previously for the company. For a self-taught "programmer" it has
been quite involved!!

Regarding the purchase detail report - I have named the sections for
clarification. The report groups first by PartNumber then by ActionID (These
include Adjustments to inventory, PO's, Req's, Allocations). The "RunProject"
text box (an unbound text box) in the Action ID Header is a summed field
based on a calculated field in the query. Records are in Inventory Date Order
and I have used the Cancel On Format to show only those lines with a Negative
Value in the "RunProject" text box. This works nicely but the PartNumber
Header still prints even though there is no ActionID info. If I try to do
Cancel On Format for the PartNumber Header whenever the "RunProject" in the
ActionID Header is greater than 0, it looks at the first record in ActionID.
Because of this - I get records with negative values in "RunProject" without
the PartNumber Header because it has taken the value from the first
"RunProject".

I guess it all comes down to - can I Cancel On Format the PartNumber Header
based on the Cancel On Format for the ActionID Header?
 
Lori said:
This database has been quite a project and I am down to this report to make
it possible to replace 6 separate Excel spreadsheets for inventory
management. It links to the Customer Database and Time Tracking Database I
created previously for the company. For a self-taught "programmer" it has
been quite involved!!

Regarding the purchase detail report - I have named the sections for
clarification. The report groups first by PartNumber then by ActionID (These
include Adjustments to inventory, PO's, Req's, Allocations). The "RunProject"
text box (an unbound text box) in the Action ID Header is a summed field
based on a calculated field in the query. Records are in Inventory Date Order
and I have used the Cancel On Format to show only those lines with a Negative
Value in the "RunProject" text box. This works nicely but the PartNumber
Header still prints even though there is no ActionID info. If I try to do
Cancel On Format for the PartNumber Header whenever the "RunProject" in the
ActionID Header is greater than 0, it looks at the first record in ActionID.
Because of this - I get records with negative values in "RunProject" without
the PartNumber Header because it has taken the value from the first
"RunProject".

I guess it all comes down to - can I Cancel On Format the PartNumber Header
based on the Cancel On Format for the ActionID Header?
Click to expand...


I think I am getting lost here. You say RunProject is an
unbound text box in the ActionID Header, but then you say
the value doesn't work in the PartNumber header?? That
makes sense since it hasn't been calculated yet. OTOH, you
then say the value is taken from the first detail, so I
really don't understand where RunProject is located or
how/when it's calculated.

I will repeat once again, rather that cancelling data
formatting in the report, it almost always better to filter
out the undesirable data in the report's record source
query.
 
Back
Top