Solved CanShrink in reports detail. (1 Viewer)

Sodslaw

Registered User.
Local time
Today, 02:06
Joined
Jun 7, 2017
Messages
81
im havimng an issue where my Boss has asked be to do something left feild and i need to know how best to go about it.

i have created a report that displays technical specifications for products for all product groups.
Basically if a product group = Accesory, PartNo starts with "AC", then i need for a textbox to shrink.
As all other items must be minimum of a full page the delault in the detail of the report is CanGrow = Yes and CanShrink = NO
and the text box fills the page. (so one product per page only, if over it will go to a second page)
This is all fine for all other groups (around 15) baring 1, Accesories.

Is there a way to add an exception via VBA, in the detail section for the Format event, or anyware, for the text box to CanShrink = Yes
If Me.PART_NO Like "AC*" then Me.OrderOptionSpec.CanShrink = True.

Currently i have tried but unsuccesfully.

the closed i got was to set Me.OrderOptionSpec.Height = x but this effected all other product groups not only the accessory but this is risky.
Code:
If Me.PART_NO Like "AC*" then
    Me.OrderOptionSpec.Height = 1 ' Just to see if it would work
End if

I know why this happened its detected during formating PART_NO Like "AC*"= True and its made everything shrink noit just accessories. Not what i need.

any help wpould be appreciated.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 02:06
Joined
Oct 29, 2018
Messages
21,357
Hi. I'm not really sure how can shrink works, but if you're trying to keep a tight space when there's an empty row, I usually make the row very short and just use can grow to make it big.
 

Sodslaw

Registered User.
Local time
Today, 02:06
Joined
Jun 7, 2017
Messages
81
Hi. I'm not really sure how can shrink works, but if you're trying to keep a tight space when there's an empty row, I usually make the row very short and just use can grow to make it big.
how can this be done only to records where Me.PART_NO = Like "AC*"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:06
Joined
Oct 29, 2018
Messages
21,357
how can this be done only to records where Me.PART_NO = Like "AC*"
You may have to show us exactly what you mean. If you don't want to see those records, then you could add a filter to your report to exclude them.
 

LarryE

Active member
Local time
Today, 02:06
Joined
Aug 18, 2021
Messages
562
What you are asking is extremely complicated and difficult if it can be done at all.

The only way to access the Height, CanGrow and CanShrink properties in a report is to open the report in Design View, change the settings, save the report and then re-open the report in Preview mode. But you cannot do that based upon some If Then Else criteria. But If Me.PART_NO Like "AC*" cannot be used in Design View because you do not have access to the report RecordSource records, so you don't know which are Like "AC" and which are not.

The only other way I can think of is to create a separate SubReport of only those records where the PART_NO is Like "AC". The subreport can then be inserted into your main reports Detail section with its CanGrow and Canshrink properties set to True and the Master/Child fields can link them together with the PART_NO in the main report. The subreport is just another control on the report, so you can set its height to any height you want but it could expand if the PART_NO is Like "AC". I am not sure this will work, but its the only other thing I can think of that MIGHT.

Good luck with this one.

FYI, here is how you access a reports property settings and set the Detail section height to 1" and the CanGrow and CanShrink to True:
  1. Open the report
  2. Run the following VBA code
Code:
With Screen.ActiveReport
  DoCmd.OpenReport Screen.ActiveReport.Name, acViewDesign, , , acWindowNormal
  Screen.ActiveReport.Section(0).Properties("Height") = 1440 '1440 Twisps=1"
  Screen.ActiveReport.Section(0).Properties("CanGrow") = True
  Screen.ActiveReport.Section(0).Properties("CanShrink") = True
  DoCmd.Close acReport, Screen.ActiveReport.Name, acSaveYes
End With
Re-open the report
 
Last edited:

Sodslaw

Registered User.
Local time
Today, 02:06
Joined
Jun 7, 2017
Messages
81
Thanks for the reply. I Had tried this apoproch (creating a seperate report for Accessoried.

ie
i had successfully been able to run this in 2 reports.
1. One report for Part numbers (filtering out accessories.)
2. A report to include only the accessories.
This worked fine and solves all of the issues, so i thought that i had it solved. But the issue is when another non Accessory items are placed after the accessory section messed up the ordering. I had blocked the form to reject parts being added post accessories, but it was rejected.

So i beleive i would need another subreport?
1. The 1st showing non accessores (In which these reports cannot shrink)
2. The 2nd showing accessories (these CanShrink)
3. the 3rd showing the remainig non accessory items after the accessory subreport.(See below, In which this reports cannot shrink)

The issue here is, i am unable to (probably due to lack ok knpowledge)
1. Report1 filter the items before the accssories
Report2 The accessories (this is OK)
2. Report3 PartNo post accessories

Any ideas?

below table is some data as an example.
How to filter so that the sub report returns partumbers positioned before the Accessories ie Report1 (ID331, 332 and 333)
how to filter Accessories thats easy, PART_NO Like "AC*" where OrderMainLink = Me.OrderID (Sort order=POSITION)
How to filter the sub report for non AC parts, post the list of Accessories (i.e ID 334,336...)

OrderMainLink, is my child link to the main report
Position is the order these reports must sort in.

FYI Its complecated But on the input form design forces accessories to be grouped together.

IDOrderMainLinkPART_NOPOSITION
331​
80​
DH
1​
332​
80​
DL-HA
2​
333​
80​
DL-HP
3​
342​
80​
AC-CP-TEL
4​
341​
80​
AC-CP-SW
5​
334​
80​
DL-LA
6​
336​
80​
DL-PP
8​
337​
80​
DL-SF
9​
338​
80​
DL-SP
10​
339​
80​
TD-680C
12​
555​
80​
DL-XF-6000
13​
 
Last edited:

Sodslaw

Registered User.
Local time
Today, 02:06
Joined
Jun 7, 2017
Messages
81
if it helps this is the vba Qry for Report 2, the accessory report.

Code:
SELECT TblOrderOption.OrderOptionID, TblOrderOption.OrderMainLink, TblPARTS.PART_NO, TblOrderOption.POSITION
FROM TblPARTS INNER JOIN (TblOrder INNER JOIN TblOrderOption ON TblOrder.OrderID = TblOrderOption.OrderMainLink) ON TblPARTS.PART_NO = TblOrderOption.PartNoOption
WHERE (((TblPARTS.PART_NO) Like "AC*"));

PS Ordering of 'Position' is tasked within the Report. (Group, Sort and Total section)

extrapolating the Poision First and Last of this report would some how solve my issue.

ie report1 = filter to the Accessory's 1st position value.
report 3 = filter from Accessory's position max value
 

LarryE

Active member
Local time
Today, 02:06
Joined
Aug 18, 2021
Messages
562
Happy that you were able to find a solution.
 

Sodslaw

Registered User.
Local time
Today, 02:06
Joined
Jun 7, 2017
Messages
81
@LarryE its not solved mate i need to find a way to to filter the First and last report...
extrapolating the Poision First and Last of this report would some how solve my issue.

ie report1 = filter to the Accessory's 1st position value.
report 3 = filter from Accessory's position max value
 

bastanu

AWF VIP
Local time
Today, 02:06
Joined
Apr 13, 2010
Messages
1,401
Create a totals query (qryLast_AC) where you Group by Left([Part_No]),2) ="AC" and get Last_AC:Max([Position]) then in the report 3 record source you filter the Position field to be greater then Last_AC:
>dlookup("Last_AC","qryLast_AC")

Cheers,
 

LarryE

Active member
Local time
Today, 02:06
Joined
Aug 18, 2021
Messages
562
Open the queries for the subreports in Design View and use the Criteria boxes to filter the subreports. You can use the DMax and DMin functions in the criteria boxes to filter for the Maximum Position and Minimum Position in each subreport or you can do what bastanu suggested as well. Either method should work.
 

Sodslaw

Registered User.
Local time
Today, 02:06
Joined
Jun 7, 2017
Messages
81
Thanks Guys seems i am much closer now thanks to you both.
This has now presented a new issue, now that i am filtering reports, in that if no accessories have been selected then none of the sub reports print. (Im expect its because it cant find results to the filter crirtera)

this is the filter in the reports
Report1..
Code:
POSITION < DMin("POSITION","QSalesQuotationItemAcc","OrderID = " &OrderID)
Report3..
Code:
POSITION > DMax("POSITION","QSalesQuotationItemAcc","OrderID = " &OrderID)
1652962887611.png

is there anyway to solve this new issue ?
should i add and if statement to this? and if so what should it be? Please help :)
 

LarryE

Active member
Local time
Today, 02:06
Joined
Aug 18, 2021
Messages
562
You have your > and < reversed. It's <=DMax and >=DMin. Greater than or = to DMin and Less than or = to DMax.
 

Sodslaw

Registered User.
Local time
Today, 02:06
Joined
Jun 7, 2017
Messages
81
It seems to work fine. in the example data in post #6... there are 2 records for the accessories position = 4 and 5

i manually wrote in report1 "Position < 4 " as the 1st accesory, the position value is 4. And filter only those with position less than 4, in report 1.
Then in the 3rd sub report "Position > 5" to display all records after the accessory ie the rest. that worked fine.

so now in the 1st sub report in above table I replaced the "4" with... what is the 1st Position for accessories...
Answer = "DMin("POSITION","QSalesQuotationItemAcc","OrderID = " &OrderID)" code =

Code:
POSITION < DMin("POSITION","QSalesQuotationItemAcc","OrderID = " &OrderID)

2nd subreport is based on the "QSalesQuotationItemAcc" Query this displays only the accessores. this is placed after 1st sub report in the main report.

and with the 3rd subreport, similar to the 1st, but now find the highest position value and display everything after those..
Answer = "POSITION > DMax("POSITION","QSalesQuotationItemAcc","OrderID = " &OrderID)"

Code:
POSITION > DMax("POSITION","QSalesQuotationItemAcc","OrderID = " &OrderID)

my only issue is when these 2 bits of code cant find a result ie there is no "OrderID = " &OrderID" in the "QSalesQuotationItemAcc" Query
 

bastanu

AWF VIP
Local time
Today, 02:06
Joined
Apr 13, 2010
Messages
1,401
Not sure what do you mean by that, what should happen if there is no OrderID? Can you show a sample?

Cheers,
 

LarryE

Active member
Local time
Today, 02:06
Joined
Aug 18, 2021
Messages
562
That's because you have not referenced the Report that the OrderID is on. So:
DMax("POSITION","QSalesQuotationItemAcc","[OrderID] = Reports![The Report Name Here]![OrderID]")
Do the same for the DMin
 

Sodslaw

Registered User.
Local time
Today, 02:06
Joined
Jun 7, 2017
Messages
81
Example not all Order IDs have Accessories.
Accesssories are cought in query "QSalesQuotationItemAcc"
So if order id 128 has no accessories then OrderID 128 wont exist in query "QSalesQuotationItemAcc"
When this happens the report fails to generate

if order id exists in "QSalesQuotationItemAcc" all is working well so i kinda need anf if statement here..
Code:
POSITION < DMin("POSITION","QSalesQuotationItemAcc","OrderID = " &OrderID)
if me.order ID exists in "QSalesQuotationItemAcc" then apply the filter, if not then dont apply the filter.
 
Last edited:

Sodslaw

Registered User.
Local time
Today, 02:06
Joined
Jun 7, 2017
Messages
81
That's because you have not referenced the Report that the OrderID is on. So:
DMax("POSITION","QSalesQuotationItemAcc","[OrderID] = Reports![The Report Name Here]![OrderID]")
Do the same for the DMin
I get the same results as before. If QSalesQuotationItemAcc doesn’t have matching orderID record due to no accessories for that order ID then the sub reports won’t generate in the main
 

activemars

New member
Local time
Today, 05:06
Joined
May 19, 2022
Messages
2
In your report query add field with name something like
put iif([PART_NO] Like "AC*"= True,[PART_NO],null) as AC_example (AC_example can be whatever name)
in your report point field that you want shrink to source AC_example
Your report Field should auto shrink if there is null value AC_example record.
 

Sodslaw

Registered User.
Local time
Today, 02:06
Joined
Jun 7, 2017
Messages
81
Not sure what do you mean by that, what should happen if there is no OrderID? Can you show a sample?

Cheers,
If no order ID don’t apply filter. This will fix the issue
 

Users who are viewing this thread

Top Bottom