Link to home
Start Free TrialLog in
Avatar of Adlerm
Adlerm

asked on

Shrink print space if line is blank

I have developed a Quote application where the client can ask for a quote for a product with respect for a specific quantity (max of 6 quantities).  In other words the client may ask for a quote on say 2000 units, 3000 units etc upto a maximium of 6 quantities.
The quote request is entered into a single reord that has the format
ProductName, Quantity 1, Quantity 1 Price, Quantity 2, Quantity 2 Price, ...., Quantity 6, Quantity Price 6
All quantity and quantity price fields are initially set to zero so if the client only asks for a quote on two quantities then Quantity 1, Quantity Price 1, Quantity 2, Quantity Price 2 will be filled in while Quantities 3 to 6 and Quantity 3 to 6 Prices will still contain zero.
The quote report has the following format in the Detail section
Product Name
Quantity 1   Quantity 1 Price
Quantity 2   Quantity 2 Price
Quantity 3   Quantity 3 Price
Quantity 4   Quantity 4 Price
Quantity 5   Quantity 5 Price
Quantity 6   Quantity 6 Price
On Costs.
If a quantity is zero I basically set that quantity and its corresponding price to blank so it doesn't appear on the report.
My question is - How can I get the rest of the report in the details section following the Quantity , Quantity Price details to move up and occupy the space if the Quantity and Quantity Price is zero (blank).
So for example if the customer has only requested a quote for two quantities then the lines for Quantities/Quantity Prices  3 to 6  are to be ignored and the On Cost details would now occupy the  Quantity/Quantity Price 3 line.
Can I use the On Format property of the Detail section to somehow flag a line to indicate that it is not needed so as to allow the Can Grow/Can Shrink properties to take effect.
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

This is an another example of the disadvantages of using an "Padded Record" design approach.

In a standard appraoch, only valid qty/price records are ever entered, hence no need to "Pad", ... then "Hide" zero value (virtually meaningless) records...

Like so:
 
tblQuotes
ProductID
Qty
Price

An example would be:
ProductID,Quantity,Price
15,2000,32.21
15,3000,30.25

In other words, if a customer only wanted a quote for only 2 quantities, only 2 records would be created.
Not 6 records consisting of 2 real and 4 Fake, padded, meaningless records (that require custom code to be hidden in the report anyway)
Then this entire issue goes away AFAICT...

Remember that the structure and contents of the reports Recordsource is unknown to us, as well as the reasoning for needing it in this manner (6 hardcoded records) , so I can't say what the "Best" approach would be.

But to be fair, you can try something like this on the detail format event:

If me.Quantity=0 then
    me.section(0).visible=False
else
    me.section(0).visible=True
end if

...This will hide the entire detail section if the Qty is zero, thus there should be no need to worry about Can grow/shrink


JeffCoachman
Avatar of Adlerm
Adlerm

ASKER

Hi Jeff
Thanks for your comments.
Forgive me if I'm wrong but I think what you have suggested will hide the entire detail section - something I don't want to occur -. I want  to see (ie print) quantity/prices if the quantity is not zero and for other print line details  (in the detail section) to move up and utilize the space vacated by a quantity/price line  if it is zero.
Then if I am understanding your request, this is not possible.

Can you post a sample of the DB that demonstrates this issue?
Then post a graphical example of the *exact* output you are expecting.
(don't "describe" the output, ...post and example of *exactly* what you want it to look like, based on the sample data)

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
Try this:
Bound the report to your table
Use unbound fields in the detail section.

In detail format event set the unbound field values.
Here is an example with table a( ID, quantity1, price1, quantity2, price2)
Expand to your required fields.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    txtquantity1 = ""
    txtprice1 = ""
    txtquantity2 = ""
    txtprice2 = ""
    If price1 > 0 Then
        txtquantity1 = quantity1
        txtprice1 = price1
    End If
    If price2 > 0 Then
        txtquantity2 = quantity2
        txtprice2 = price2
    End If

End Sub


gap.mdb

  You can do that with varying degree's of sucess (there are a number of things that can interfer with can grow/shrink).

  In the OnFormat event, hide the controls you don't need (.Visible = False) and make sure the detail sections can shrink is set to true.  

  You can also do the opposite; set all the controls to 0 height and allow them to grow if they are visible (along with the detail section).

  Jeff is correct though; the better approch is to have one detail section per line to be printed, then you simply skip it if the qty is 0.

Jim.
Yeah Jim,
I am still a bit hazy about the request,
(AFAIK, you cant "Move up" data from a record below, into the above record...?, if this is what is being requested)
...to a so you and hnasr  may have the more applicable approaches...
<<I am still a bit hazy about the request,
(AFAIK, you cant "Move up" data from a record below, into the above record...?, if this is what is being requested)>>

  I think what's lacking is the understanding by Adlerm that the Detail section fires repeatedly down the page (once for each record read).  I believe instead he's envisioning the detail section as simply being the middle of the page and something that gets printed once for the page.

  I may be wrong on that, but that's what it sounds like.

Jim.
I think an answer would be to format a text box with the line values:
  Product Name, Quantity,  & Price   each with can grow/can shrink set to YES.
  When the quantity is 0, then set each text box to NULL.
The effect will be to 'move up' other controls on the section.
Very quick, very dirty, it can be done.
See the attached sample.

Your data is denormalized, which may come to bite you in the hiney.
" find all prices quoted for 3000 of product x"
since they are spread over a bunch of columns.

But the sample shows how to move and hide/unhide unwanted controls
UnusedLines.mdb
This is a way using boag2000's idea. It looks better and neeter output and no coding.
Use recordsource as a union query of the required records.
You may add a key field for sorting.

Check this sample database.

gap-2.mdb
@JDettman and @boag2000.

The OP has created a denormalized table.
One primary, one foreign key for a product and 12 more columns, 6 for price and 6 for quantity
<The quote request is entered into a single record that has the format
ProductName, Quantity 1, Quantity 1 Price, Quantity 2, Quantity 2 Price, ...., Quantity 6, Quantity Price 6>
So he now wants to hide all the null or zero quantities controls in a single detail section.
If prices and quantities 3-6 are null, make then go away
OK,
Rigging something up now based on a solution I made while ago for a Q that required Columns to be hidden (made zero width) and for the "next" control to move over into the vacated space.
Based on what fields were selected to be printed.

I try to mod it to work for zero value fields...
As I am working on this, it became clear that if we do this, then the column headings will probably become meaningless, ...as each record would have different controls ("columns") hidden...

Still working...
errr, still confused...

Nick67:
<The quote request is entered into a single record that has the format
ProductName, Quantity 1, Quantity 1 Price, Quantity 2, Quantity 2 Price, ...., Quantity 6, Quantity Price 6>>
...but this does not seem to match what the OP originally posted:
<The quote report has the following format in the Detail section
Product Name
Quantity 1   Quantity 1 Price
Quantity 2   Quantity 2 Price
Quantity 3   Quantity 3 Price
Quantity 4   Quantity 4 Price
Quantity 5   Quantity 5 Price
Quantity 6   Quantity 6 Price
On Costs.>

Nick:
<So he now wants to hide all the null or zero quantities controls in a single detail section.
If prices and quantities 3-6 are null, make then go away>
...but the OP states:
<Quantity Price details to move up and occupy the space if the Quantity and Quantity Price is zero (blank)>
...Here they state "Move Up", not "Over"...

So I am still perplexed...

This is why I always request a sample file of the DB in it's current state, and also a graphic example of the *Exact* output expected, based on the sample data.

This avoids all the ambiguity and guesswork.

This is because none of us likes to spend our time working on a solution only to find that the OP had something totally different in mind.

Jeff


Adlerm,

Will you be posting a sample file as I requested?
Avatar of Adlerm

ASKER

Hi All
Sorry about the delay in getting back to you but I've been testing some of the suggestions but not achiving the results I require.
I Have attached a database (from Nick67) with some slight modifications to illustrate my requirements.
The report shows that Qty5/Price5 and Qty6/Price6 are not shown (using Nick67 Detail section On Format event) as Qty5/Price5 and Qty6/Price6 have a value of zero.
Notice under the Qty/Price area there are two other fields (1) On Costs and (2) Freight.
What I'm trying to achieve is for the On Costs to move up and occupy the position of Qty5/Price5
and Freight to move up and occupy the position Qty6/Price6 as both of these positions are now vacant.
UnusedLines.mdb
This too is doable, but as noted, the problem comes from the fact that your data is denormalized.
Here is the sample altered to do so, back to you.
Much depends on the controls having consistent sizes and spacing
UnusedLines.mdb
@boag2000.
He has a single detail section with Price / Quantity pairs running down the detail section vertically.
Some of those pairs have null/zero values and are not to be displayed.
All controls are then to be moved up in the resulting whitespace

start of detail section
something
something
this is null let make it disappear
Something, let's move it up one row
end of detail section, let's shrink it and get rid of any whitespace
OK,

Thanks,
Now I understand the issue here...

So it seems to me that all you would have to do is calculate the total space reduction
In this case, (for two "simulated "Rows" hidden) twice the height of the one control, plus the vertical space between them
Then "move" OnCost and Frieght "Up" by that same amount.
Then finally reduce the height of the section by that same amount.
(...I  think I have that right...? :-)  LOL)

Alright then, I'll leave this to you then

;-)

Jeff
Adlerm,

In case it has not been made clear by us, ...your data is not "Normalized" in the traditional sense.
;-)

Can I ask why not?
Then this becomes ridiculously easy to do by hiding the zero "Records".

Just look at all the logic and code Nick had to create, ...just to get it to that point. ...And still more code is needed to "Move up" the remaining controls.

Nick has really gone above and beyond here.
My hat goes of to him, because I had not the patience to work with data in this format.

Your challenge going forward will be to continually post questions like this to workaround this non-standard format.
Up to you really...

To be clear, I have no issue with you accepting a solution by Nick if it answers your question (as posted) directly.
As long as my suggestion about normalizing the data, then simply hiding the zero rows is noted, ...then it's all good AFAIC.

;-)

JeffCoachman
ASKER CERTIFIED SOLUTION
Avatar of Nick67
Nick67
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Adlerm

ASKER

To Nick67

Your solution works a treat. many thanks for your code. I know my data wasn't normalized but I had my reasons to design it like I did. I'm well aware of the laws of Normalization (Date/Code etc) but it made other part of my application much easier to work with. It was only the Quote report where I ran into problems. In fact I didn't have any problems with the Quote report it was ony after the user  asked if I could shunt line(s) up and utilize the space on the report
Avatar of Adlerm

ASKER

See my other comments
Adlerm,

<<I'm well aware of the laws of Normalization (Date/Code etc) but it made other part of my application much easier to work with. >>

  I'm not sure your thinking about the same thing we are; normalization has nothing to do with dates or codes.

  Your app not being normalized will make it more difficult to work with, not easier.  Access and SQL are designed to work with normalized relational DBs.  Step away from that and things become much more difficult.   This one example is proof of that. If you had a normalized design, the recordsource of the report could have easily filtered out records that had 0 for the qty.  

  Another example is what happens when your quote now needs 6 items? or 10?  Your going to have to continually adjust your design.

  You really should go back and do it right.  If you don't, the other problem you'll have (other then trying to get simple things done) is that your going to bump into an Access limit and won't be able to get around it.  One limit is 255 fields limit in a table or query.  There is no work a round for that.  It's a hard limit and once you hit that, your stuck.

Jim.

 
<I'm well aware of the laws of Normalization (Date/Code etc) but it made other part of my application much easier to work with>
OK.

I knew how and what you needed because I have been in that boat.  And you are right, from a data entry point of view, your structure is simpler.  No subform, pre-existing controls, tabular layout.  I understand--because I've been there.

I'd undo it if I could.

When you go to analyze denormalized data later, it's a pain.
"how may quotes with 4 or more quantities have we made?"
"On all quotes of 2000+ of item x, what prices did we quotes?"
"What was the average price quoted for all quantities of product y?"

All of these then tend involve some very involved and poorly performing UNION ALL queries.
<I'd undo it if I could.>

But I am glad that I could provide you with
a) the answer you sought and
b) the advice that you really shouldn't use it :)

Nick67
Avatar of Adlerm

ASKER

Hi Jim
Unfortunately I misspelt Codd as Code. Both Date and Codd wrote books on relational databases and nomalisation (both of which I have read and studied)  so I do know what normalization is about . I've done courses on Normalization (6th/7th Normal form etc). I do however  appreciate your comments and that your are trying to be helpful.  
I started writing this before @adlerm's last post

Since the site that had the excellent data normalization tutorial on it, www.phlonx.com, has not be renewed and is now a parking page, I'll post the free distributable data normalization tutorial that use to be there, here.

Just in case there's any doubt as to what 'normalization' means to anyone else who may google into it afterward
It's clear that everyone here is now on the same page.
:)


Normal-Forms-nf3.pdf
<< so I do know what normalization is about . I've done courses on Normalization (6th/7th Normal form etc). >>

 Leaves me scratching my head then why you'd go for a denormalized approach, but OK...

Jim.

It makes for a simpler data entry form, is one reason.
The bound controls are all on one line, tabular
A separate table either means a subform, or some unbound controls / code.
And looking after the idiot user and ensuring that he's got the primary key created before trying to enter stuff in the subform.

There may be other reasons, but that's one.
As disgusting as I find multivalue fields and the difficulty of getting values back out of them,
https://www.experts-exchange.com/questions/27328244/How-to-add-selection-from-multi-select-combo-box-to-email-from-single-form.html?cid=748&anchorAnswerId=36709006#a36709006
the interface (that multi-select combo box) is a nice thing.

I can see why.
But I'd never do it again.
Again, I have no issue with the accepted solution, but this is seems to be basically what we all suggested from the start...

Final note to add to what Jim stated...
With transposed data it is always a nightmare to get summaries.
Summary/aggregate functions rely on normalized data.
To get a sum of "Freight" you need only to do this:
   =Sum(Freight)
So if you had 75 records, this would be all that is needed to add them all up. (and would never need to be changed, no matter how many records you had.)

With "Transposed" data like you had, you have to do this:
=NZ(Freight1)+NZ(Freight2)+NZ(Freight3)+...
...all the way to 75!

What is worse is that anytime (as Jim states) a column is added, or removed, or even renamed, you must edit all formulas like this.

But to be fair, there are no absolutes here...
In some cases (Crosstabulations, Pivots), a "Transposed Output" may be needed, but in most of those cases the raw underlying data will still be normalized.

And yes, in some cases de-normalized data will improve performance.
But these are typically rare and extreme cases where the data and relationships may be extremely complex.
Even then, the data is denormalized, not "Transposed".
(Note here that "Transposing" data is not the same thing as "de-normalized" data.)

So wile it may help in some cases to do this, it often causes more headaches than it solves...


JeffCoachman
I did it almost six years ago and I've never had to re-jig the tables.
When you do something like this, you always ask "ok, what's the biggest number of these I'll ever need?" and you put in enough fields to accommodate it.

The problem came later.
In my case, a job could have any number of inspectors--but it was highly unlikely that there will ever be more than five.
So the table has InspectorID, InspectorID2, InspectorID3, InspectorID4, and InspectorID5
The form had five comboboxes, and some AfterUpdate code that checks you haven't skipped InspectorID2 before entering InspectorID3, and that none are duplicates.
All went along merrily.

Much later, some said "hey can you tell me how many jobs each dude particiapted in this month"
Oh oh
Well, I need all the jobs between this date and that date, and then a union of all the InspectorIDx's and then from there I need to feed it a WHERE whatever I called the union field = SomeInspectorID
"hey, how often did these two dudes work together this month?
Oh oh
"Hey, how many jobs did this dude work by himself vs worked with more than one other dude?"
Oh oh

Well, you get the idea.
Yes,

With things that have "Defined" limits (7 days/Week, 12 months/Year, 50 States in the USA, 6 qty/prices, ...etc)
It is always a battle to decide which way to go.

I mean if you think about it, if you have 7 columns (one for each day), what are the chances of another "Day" being added?
;-)
The other thing that might lean you toward repeating fields is that, doing this will insure that the user will never enter more (or less) than the specified "fields" will allow.
("How could you forget Wednesday, ...It's right there!...")

However, almost every time I gave in and used repeating fields, ...I lived to regret it.

For all of the reasons Nick listed above...

Almost every operation a well designed database is designed to do, ...is based on a standard "Normalized table design"
(So using repeating fields is the "exception", not the rule)


Just FWIW...