Fixed Report Detail Height (1 Viewer)

[ChrisMore]

Hi all,

I am using a report to create a custom layout for printing onto labels which stick onto pallets. Each label can have up to three product details on it and the report is grouped by pallet number. The report detail height is fine when a specific pallet number has three items but if the report outputs one or two items per pallet number the report is not tall enough for the label size, meaning the other pallet number grouped items concertina up over the 2 per page labels.

Is there a way to fix the detail section height so there is blank space as a place holder for one or two items? I am currently using a subreport for the item information as I thought this might be the way to go but it hasn't worked with my current set up. I have attached a screenshot of the relationships for the items and pallet labels tables. Maybe my tables are not set up correctly to achieve what I want?

My Report and Subreport record source are queries, SQL for both queries if helpful for you:
Subreport

SELECT Customer_Orders.Order_Number, Shipments.Order_Shipment_Number, Works_Order_Items.Ordered_Item_ID, Manufactured_Products.Product_Name, Works_Order_Items.Manufactured_Product_ID, Manufactured_Products.DPC_Dimensions, Manufactured_Products.Void_Depth, Pallet_Label_Items.Pallet_Item_Quantity, Pallets.Pallet_Number, Pallet_Label_Items.Pallet_ID
FROM (Manufactured_Products INNER JOIN ((Customer_Orders INNER JOIN Shipments ON Customer_Orders.Order_Number = Shipments.Order_Number) INNER JOIN Works_Order_Items ON (Shipments.Order_Shipment_Number = Works_Order_Items.Order_Shipment_Number) AND (Customer_Orders.Order_Number = Works_Order_Items.Order_Number)) ON Manufactured_Products.Manufactured_Product_ID = Works_Order_Items.Manufactured_Product_ID) INNER JOIN (Pallets INNER JOIN Pallet_Label_Items ON Pallets.Pallet_ID = Pallet_Label_Items.Pallet_ID) ON Works_Order_Items.Ordered_Item_ID = Pallets.Ordered_Item_ID
WHERE (((Customer_Orders.Order_Number)=[Reports]![AIM_Pallet_Labels_Report_and_Subreport]![Order_Number]) AND ((Shipments.Order_Shipment_Number)=[Reports]![AIM_Pallet_Labels_Report_and_Subreport]![Order_Shipment_Number]));

Report

SELECT Shipments.Order_Number, Shipments.Order_Shipment_Number, Shipments.Transport_Mode, Shipments.Number_Of_Pallets, Shipments.Clips_Pallet_Number, Works_Order_Items.Ordered_Item_ID, Pallets.Pallet_ID, Pallets.Pallet_Number, Pallet_Label_Items.Pallet_Item_Quantity
FROM (Shipments INNER JOIN Works_Order_Items ON Shipments.Order_Shipment_Number = Works_Order_Items.Order_Shipment_Number) INNER JOIN (Pallets INNER JOIN Pallet_Label_Items ON Pallets.Pallet_ID = Pallet_Label_Items.Pallet_ID) ON Works_Order_Items.Ordered_Item_ID = Pallets.Ordered_Item_ID
WHERE (((Shipments.Order_Shipment_Number)=[Forms]![Pallet_Labels_Shipment_Number_Pop_Up_Form]![Combo0]));

Thanks for your help in advance.
Chris

 

[arnelgp]

you can have a Fixed detail textbox (3 textbox, unbound, can shrink = No).
on the Detail's Format event, these textbox are filled.
using recordset from your query, you fill those textboxes.

 

[ChrisMore]

you can have a Fixed detail textbox (3 textbox, unbound, can shrink = No).
on the Detail's Format event, these textbox are filled.
using recordset from your query, you fill those textboxes.

Hi arnelgp, am I correct in assuming I wouldn't need a subreport for this to work?

Would you be able to expand on your explanation of your idea, as I am unsure what to do on the Detail's Format event using the recordset from the query?

Thanks.

 

[theDBguy]

The report detail height is fine when a specific pallet number has three items but if the report outputs one or two items per pallet number the report is not tall enough for the label size

Hi Chris. Just thinking out loud, but I wonder if a Cartesian query approach would work here.

 

[ChrisMore]

Hi Chris. Just thinking out loud, but I wonder if a Cartesian query approach would work here.

Hi theDBguy, please can you explain how you think this might work as from my understanding wouldn't it group all items on each pallet number?

 

[theDBguy]

Hi theDBguy, please can you explain how you think this might work as from my understanding wouldn't it group all items on each pallet number?

Hi. I'll try to explain, but it would be easier if I had a file to demo it.

Based on your description, the labels (subreport) are fine as long as you have three items in them. So, I imagine you might have something like this situation.

Pallet1
Item1
Item2
Item3

Pallet2
Item1
Item2

In which case, Pallet2 would mess up the placement of the next labels. If so, I was thinking, by using a Cartesian Product, we could try to force each pallet to have exactly three items each. For instance.

Pallet1
Item1
Item2
Item3

Pallet2
Item1
Item2
BlankItem3

So, that was the idea, but I can't say for sure it will work without testing it.

 

[ChrisMore]

Hi. I'll try to explain, but it would be easier if I had a file to demo it.

Based on your description, the labels (subreport) are fine as long as you have three items in them. So, I imagine you might have something like this situation.

Pallet1
Item1
Item2
Item3

Pallet2
Item1
Item2

In which case, Pallet2 would mess up the placement of the next labels. If so, I was thinking, by using a Cartesian Product, we could try to force each pallet to have exactly three items each. For instance.

Pallet1
Item1
Item2
Item3

Pallet2
Item1
Item2
BlankItem3

So, that was the idea, but I can't say for sure it will work without testing it.

Thanks for explaining, I see what you're saying. Where would the blank item come from in your example for pallet 2 item 3?

 

[theDBguy]

Thanks for explaining, I see what you're saying. Where would the blank item come from in your example for pallet 2 item 3?

It would come from the Cartesian Product.

 

[ChrisMore]

It would come from the Cartesian Product.

Haha, ok, I'm misunderstanding what the Cartesian Product can do.

It's difficult for me to attach the database for you to test because it is live as a split database for the company I work for. Do you have any pointers for me to go in the right direction on how to use a Cartesian Product as in your example?

 

[theDBguy]

Haha, ok, I'm misunderstanding what the Cartesian Product can do.

It's difficult for me to attach the database for you to test because it is live as a split database for the company I work for. Do you have any pointers for me to go in the right direction on how to use a Cartesian Product as in your example?

Maybe this will give you the idea.

A practical use of a Cartesian Query

As promised in my previous blog on Cartesian Query , I will now try to describe in detail one example of a practical use of a Cartesian Quer...

thedbguy.blogspot.com

But, just as I indicated in my original reply to your post, I was merely thinking out loud. It would probably take a few experimentation to see how we can apply the idea and make it work in your situation. Cheers!

 

[ChrisMore]

Maybe this will give you the idea.

A practical use of a Cartesian Query

As promised in my previous blog on Cartesian Query , I will now try to describe in detail one example of a practical use of a Cartesian Quer...

thedbguy.blogspot.com

But, just as I indicated in my original reply to your post, I was merely thinking out loud. It would probably take a few experimentation to see how we can apply the idea and make it work in your situation. Cheers!

Thanks for your help theDBguy, but I can't make this work with my set up. It's a lot more complicated than the example in your blog and although I'm getting the expected results in the query, when running the report it just looks like duplicated records for each pallet number. I'm struggling to think how to use a cartesian query to get the correct items for each pallet number with blank item/s to make the items per pallet to 3.

 

[ChrisMore]

Hi arnelgp, am I correct in assuming I wouldn't need a subreport for this to work?

Would you be able to expand on your explanation of your idea, as I am unsure what to do on the Detail's Format event using the recordset from the query?

Thanks.

@arnelgp are you able to provide the above advice on your idea please?
Cheers.

 

[arnelgp]

can you post a snapshot of what the report look like now?
it is a great help if you can share a dummy db (somewhat similar to what you have).

 

[ChrisMore]

can you post a snapshot of what the report look like now?
it is a great help if you can share a dummy db (somewhat similar to what you have).

I have attached a screenshot of the report (the one outlined in red is a separate output of the report to the one in blue). The one outlined in red shows what happens when only 1 item is on a pallet and I would like the height to be fixed to what happens when 3 items are on a pallet (as shown with the report outlined in blue).

If this isn't enough of a help, I'll see what I can do to create a dummy version of my database.

Thanks.

 

[arnelgp]

this might not be close to your report.
on the report table1 as main report and table2 is linked by masterid

as you can see in table2 table, there is only 1 record for masterid 1, while there are 3 for masterid 3 (complete).
we change the recordsource of table2 subreport on it's Open event.

run table1 report in print preview.

 

[ChrisMore]

this might not be close to your report.
on the report table1 as main report and table2 is linked by masterid

as you can see in table2 table, there is only 1 record for masterid 1, while there are 3 for masterid 3 (complete).
we change the recordsource of table2 subreport on it's Open event.

run table1 report in print preview.

Hi arnelgp,

I have been away from this project for the past couple of weeks but thank you for sending the example database and it's great that you have been able to get it working. I have tried to amend the code to fit my database but I haven't been able to get the code working. My 'MasterID' field is 'Order_Shipment_Number' which is actually a text field and have changed the code as below. The error appears when running 'fncQuerySource' on the report's open event, saying "The setting for this property is too long". Do you know why this is happening?

Public Function fncQuerySource() As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String, i As Integer
Dim MasterID As String
Set db = CurrentDb
Set rs = db.OpenRecordset("select * from Shipments order by [Order_Shipment_Number];")
With rs
    If Not (.BOF And .EOF) Then
        .MoveFirst
    End If
    Do Until .EOF
        MasterID = ![Order_Shipment_Number]
        If Len(sql) < 1 Then
            sql = "select [Order_Shipment_Number],[Pallet_Number],[Ordered_Item_ID], [Pallet_Item_Quantity] from Pallets where Order_Shipment_Number = " & MasterID
        Else
            sql = sql & " union select [Order_Shipment_Number],[Pallet_Number],[Ordered_Item_ID], [Pallet_Item_Quantity] from Pallets where Order_Shipment_Number = " & MasterID
        End If
        i = DCount("1", "Pallets", "Order_Shipment_Number = '" & MasterID & "'")
        If i < 3 Then
            sql = sql & _
                " union ALL " & _
                "select top " & (3 - i) & " " & MasterID & ",1000+[Pallet_ID],[Ordered_Item_ID],[Pallet_Item_Quantity] from zzTable2"
        End If
        .MoveNext
    Loop
    .Close
End With
Set rs = Nothing
Set db = Nothing
fncQuerySource = sql
End Function

 

[ChrisMore]

@arnelgp your assistance with the above would be much appreciated, thank you.

 

[arnelgp]

hello.
you need to Limit the number of records returned from the shipment table,
otherwise it will pull All records from that table and will make the Query string long.

 

[ChrisMore]

hello.
you need to Limit the number of records returned from the shipment table,
otherwise it will pull All records from that table and will make the Query string long.

Would that be done by changing the code? I have the following as criteria on the query which is the report's record source to limit the number of records returned:

[Forms]![Pallet_Labels_Shipment_Number_Pop_Up_Form]![Combo0]

 

[arnelgp]

i change the code, just check if the filter field is correct.

Public Function fncQuerySource() As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String, i As Integer
Dim MasterID As String
Dim sFilter As String
Set db = CurrentDb
If SysCmd(acSysCmdGetObjectState, acForm, "Pallet_Labels_Shipment_Number_Pop_Up_Form") <> 0 Then
    sFilter = " where [Order_Shipment_Number] = '" & [Forms]![Pallet_Labels_Shipment_Number_Pop_Up_Form]![Combo0] & "'"
End If
Set rs = db.OpenRecordset("select * from Shipments " & sFilter & " order by [Order_Shipment_Number];")
With rs
    If Not (.BOF And .EOF) Then
        .MoveFirst
    End If
    Do Until .EOF
        MasterID = ![Order_Shipment_Number]
        If Len(sql) < 1 Then
            sql = "select [Order_Shipment_Number],[Pallet_Number],[Ordered_Item_ID], [Pallet_Item_Quantity] from Pallets where Order_Shipment_Number = " & MasterID
        Else
            sql = sql & " union select [Order_Shipment_Number],[Pallet_Number],[Ordered_Item_ID], [Pallet_Item_Quantity] from Pallets where Order_Shipment_Number = " & MasterID
        End If
        i = DCount("1", "Pallets", "Order_Shipment_Number = '" & MasterID & "'")
        If i < 3 Then
            sql = sql & _
                " union ALL " & _
                "select top " & (3 - i) & " " & MasterID & ",1000+[Pallet_ID],[Ordered_Item_ID],[Pallet_Item_Quantity] from zzTable2"
        End If
        .MoveNext
    Loop
    .Close
End With
Set rs = Nothing
Set db = Nothing
fncQuerySource = sql
End Function