Fixed Report Detail Height (1 Viewer)

ChrisMore

Member
Local time
Today, 17:54
Joined
Jan 28, 2020
Messages
137
i change the code, just check if the filter field is correct.
Code:
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
After updating the code, I am now getting a syntax error (image attached). Combo0 contains '16710A'.

Is this referring to the syntax in the code because the report's record source query is working? I can't see any issues with the code though. The field names you entered for the filter look to be correct...
 

Attachments

  • 1618919196251.png
    1618919196251.png
    22.8 KB · Views: 45

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:54
Joined
May 7, 2009
Messages
14,626
what is the datatype of [Order_Shipment_Number]? text or numeric?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:54
Joined
May 7, 2009
Messages
14,626
try this:
Code:
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
 

ChrisMore

Member
Local time
Today, 17:54
Joined
Jan 28, 2020
Messages
137
Hi @arnelgp

I have returned to working on these pallet labels after spending some time on other areas of my database and I'm still not getting the results I would like. I have attached a screenshot of what's happening. Basically, it's duplicating the results by the amount of items that are on the pallet. So in the screenshot there are two items on the pallet and the results show twice on the label.

Do you (or anyone else who might be reading this) have any idea why this is happening?

Thanks.
 

Attachments

  • Screenshot 11.png
    Screenshot 11.png
    63.9 KB · Views: 30

ChrisMore

Member
Local time
Today, 17:54
Joined
Jan 28, 2020
Messages
137
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 found that the problem I have above is resolved when I make the main report and subreport link by Pallet_Number (this field is called 'pallet' in your example). However, when I change this your code doesn't seem to do anything unless I link the reports by Order_Shipment_Number (this field is called 'masterid' in your example). Is there a way to get your code working when I link the reports by Pallet_Number?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:54
Joined
May 7, 2009
Messages
14,626
if you can post a small sample db, i'll have a look at the code.
if you are unable to share it here, maybe pm me and attached
the sample db there.
 

ChrisMore

Member
Local time
Today, 17:54
Joined
Jan 28, 2020
Messages
137
if you can post a small sample db, i'll have a look at the code.
if you are unable to share it here, maybe pm me and attached
the sample db there.
I have attached the sample database. The shipment number to use is 16172A. I have left the reports set up so they are linked using the Order_Shipment_Number but it does look like they need to be linked using Pallet_Number to get my desired result.

Something else which I was unable to get working is where it says 66 and 67 on the report. These are the IDs from the Works_Order_Items table and I would like the Manufactured_Product_ID and Manufactured_Product_Name fields to show here instead. It would be great if you could get this working also please.

Thank you for your continued help with this @arnelgp.
 

Attachments

  • Database.accdb
    3.1 MB · Views: 29

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:54
Joined
May 7, 2009
Messages
14,626
check and test
 

Attachments

  • Database (3).accdb
    2.9 MB · Views: 28

ChrisMore

Member
Local time
Today, 17:54
Joined
Jan 28, 2020
Messages
137
check and test
It is much appreciated that you have taken your time to look at this but I should have been a bit clearer with what the results of the report should be for this example. There are two pallets for this shipment; Ordered_Item_ID 66 is on pallet 1 and Ordered_Item_ID 67 is on pallet 2. Therefore, it's still not working 100% because the two items should be split over the two labels. I have attached a screenshot with how it should look when split but the code then doesn't do what it is supposed to do when I change the report master/child link.

I hope this makes sense. Thanks.
 

Attachments

  • Screenshot 12.png
    Screenshot 12.png
    87.5 KB · Views: 33

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:54
Joined
May 7, 2009
Messages
14,626
test again
 

Attachments

  • Database (3).accdb
    3 MB · Views: 24

ChrisMore

Member
Local time
Today, 17:54
Joined
Jan 28, 2020
Messages
137
test again
When I run the new report you made it is splitting the items across two pallet labels correctly but the code doesn't seem to be adding the blank spaces to create the fixed label height. As there is one item per label in this example there should be two blank spaces added for each label. Was this working for you?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:54
Joined
May 7, 2009
Messages
14,626
as far as i can remember, the request is to make 3 labels (pallet) fit in a single page.
whether there is only 1 pallet or 2 to print, the output must always print in 3 (uniform).
the "3rd" pallet, on the sample, is printing in "white" foreground, therefore will not be shown.

the report name in "macro" of Open Report should be (New_AIM_Pallet_Labels_Report).

if you have more pallet (more that 3), it will print each page in 3.
 

ChrisMore

Member
Local time
Today, 17:54
Joined
Jan 28, 2020
Messages
137
as far as i can remember, the request is to make 3 labels (pallet) fit in a single page.
whether there is only 1 pallet or 2 to print, the output must always print in 3 (uniform).
the "3rd" pallet, on the sample, is printing in "white" foreground, therefore will not be shown.

the report name in "macro" of Open Report should be (New_AIM_Pallet_Labels_Report).

if you have more pallet (more that 3), it will print each page in 3.
It's not to make 3 labels per page but to make 3 items per label. This was working using the code you first created but it didn't respond when I tried to change the master/child links on the report to split the items based on the Pallet_Number field.

I have attached a file showing two versions of the labels I have been able to create so far. The first one shows how the code is working but as the master/child link is using the Order_Shipment_Number field the items are grouped together on each label. The other one shows how the labels looks when the master/child link using Pallet_Label. I require a combination of both here, so the items group by Pallet_Number but also have a uniform 3 items per label.
 

Attachments

  • SINEO 221052617060.pdf
    70.5 KB · Views: 29

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:54
Joined
May 7, 2009
Messages
14,626
check again.
 

Attachments

  • Database (3).accdb
    5 MB · Views: 24

ChrisMore

Member
Local time
Today, 17:54
Joined
Jan 28, 2020
Messages
137
check again.
Thanks so much, it is so close to working! The problem I can see is when there are multiple order shipment numbers for the order. I have added 16172B to the data and I'm getting the results as shown in the example database attached. It's working perfectly when there is just 1 shipment number involved.
 

Attachments

  • Database (3) (2).accdb
    5 MB · Views: 29

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:54
Joined
May 7, 2009
Messages
14,626
check again. the report is based on the combo (shipment_number).
 

Attachments

  • Database (3) (2) (1).accdb
    3 MB · Views: 25

ChrisMore

Member
Local time
Today, 17:54
Joined
Jan 28, 2020
Messages
137
check again. the report is based on the combo (shipment_number).
That looks to have done it! It will be thoroughly tested over the coming weeks but I think it is working perfectly. That has made my day, thank you so much for your help :)
 

Users who are viewing this thread

Top Bottom