Adding blank rows to a report

[arnelgp]

i only noticed now your PrepReport form.
you can now use it and your report will be shown filtered/un filtered (if nothing is
selected from the 2 combos)

 

[T. McConnell]

here also with Filter.
you can run form1 for this demo that will filter the report on
DeliveryDate = Oct 13, 2022.

you can create any filter on it.

Once again, thank you for the help. I think what I am going to end up doing is just run with opening the Report up with all showing. However I tested your one sample I think you posted in Post #12. What is weird this is doing what I need, however I uploaded our actual file that I would be using and it has a total of 32 trucks on it (sometimes this can go up to as high as 35. It has a total record count in the tblMain of 486 which isn't a lot. When I try to open the report, it gives an error saying Query is too complex. When I delete the last truck (truck 32 and those records tied to it) it seems to open up just fine. I know before I just had about 6 trucks or so, but our truck list is like below. I initially had the wrong TruckID on there, which I figured out that part of it all. Now just need to see why the query is too complex with this much of an actualy query maybe?
XDTLOUV01, XDTLOUV06, XDTLOUV07, XDTLOUV08, XDTLOUV09, XDTLOUV10, XDTLOUV11, XDTLOUV13, XDTLOUV14, XDTLOUV15, XDTLOUV16, XDTLOUV17, XDTLOUV18, XDTLOUV19, XDTLOUV20, XDTLOUV21, XDTLOUV22, XDTLOUV23, XDTLOUV24, XDTLOUV26, XDTLOUV27, XDTLOUV28, XDTLOUV29, XDTLOUV30, XDTLOUV31, XDTLOUV32, XDTLOUV33, XDTLOUV34, XDTLOUV35
I apologize for being dumb when it comes to this, I really wish I used access on such a consistent basis for my day to day that I actually remember and can learn more on it. I unfortunately can't post the new copy with all the data as it does actually contain real customer information.

 

[Gasman]

You can use the function here. https://www.accessforums.net/showthread.php?t=82910&page=2&highlight=randomize to hide confidential data.

 

[arnelgp]

I unfortunately can't post the new copy with all the data as it does actually contain real customer information

that is ok.
what you need to copy on the demo:

1. form PrepReport
2. table dummy and table zzTruckLog
3. report TruckLog2

as suggested earlier, you would need a temp table for this (zzTruckLog).

 

[T. McConnell]

Ok, so I think I have a pretty good thing going, one snag I've ran into is sometimes the fields will contain text that will cut off in the text boxes, I know there is a way to shrink the text based on length of the textbox and make it fit. This affects one field the most which is the order number field as sometimes the order numbers can be an 18 digit number and the others will always be 9 digits. Just curious to see if this is a doable option.
Thanks again for everything

 

[MajP]

Some solutions here

shrink to fit

I know the property exists in Excel, but can I shrink the font of a control depending on the size of the text, while keeping the control the same size?? TIA, Sparx

www.access-programmers.co.uk

 

[T. McConnell]

Some solutions here

shrink to fit

I know the property exists in Excel, but can I shrink the font of a control depending on the size of the text, while keeping the control the same size?? TIA, Sparx

www.access-programmers.co.uk

I actually had tried this, when this code is in place or not and if I have a 10 digit or more number in the field, that whole row is blank on my report. I have it set to allow to grown and shrink to yes and as well as to no, so not sure why it's being dumb. I don't think I have any validation in place to meet length requirements either. I'm lost haha

 

[MajP]

Grow and shrink deals with the height, so you want that set to false. If not it will throw off the number of blank rows required. Can you post an example so we can see the issue?

 

[T. McConnell]

Grow and shrink deals with the height, so you want that set to false. If not it will throw off the number of blank rows required. Can you post an example so we can see the issue?

I can try and get a screenshot of it and upload it. What's weird is I can get that code to work for a different field, but for some reason that field won't adjust the font size when equal to 18 or greater than 9 etc..

 

[T. McConnell]

This issue pertains an earlier reply above with getting the error "Query is too complex" I looked at the Query tied to the report and removed the Criteria in both the item number and model number fields as maybe it was trying to filter out too many different items not needed in the report. However I still get the error when I have extra trucks I need to add. Or if I delete a small chunk of rows from the main table.
This is the code provided by @arnelgp from one of the samples. I am not sure if somewhere in here is causing the error or not.

Private Sub Report_Open(Cancel As Integer)
    Dim sql As String
    Dim record_count As Long, record_fill As Long
    With CurrentDb.OpenRecordset("TruckCount", dbOpenSnapshot, dbReadOnly)
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do While Not .EOF
            record_count = 0: record_fill = 0
            
            sql = sql & _
            IIf(Len(sql) <> 0, " UNION ", "") & _
            "select * from TruckLogQry where TruckID = '" & !TruckID & "'"
            
            record_count = !Kount
            If record_count < MAX_LINES Then
                record_fill = MAX_LINES - record_count
            Else
                If record_count > MAX_LINES Then
                    record_fill = record_count Mod MAX_LINES
                End If
            End If
            If (record_fill <> 0) Then
                sql = sql & " UNION " & _
                "select top " & record_fill & " " & _
                "id," & _
                "'" & !TruckID & "'," & _
                99999 & "," & _
                "Null," & _
                "Null," & _
                "Null," & _
                "Null," & _
                "Null," & _
                "Null," & _
                "Null from dummy"
            End If
            .MoveNext
        Loop
        .Close
    End With
    'Debug.Print sql
    Me.RecordSource = sql
End Sub

The "dummy" table has entries in there currently from 1000000001 to 1000000102, so not sure what I am missing here as no matter I do it doesn't seem to make the error go away.
I did notice if I move it to the On Load event it works, but won't add the extra lines to it and also gives an error on the final piece of code where it says Me.RecordSource = sql.

 

[arnelgp]

if you have many trucks, the Union query will fail.
see post #24 for Temp table solution.

 

[T. McConnell]

if you have many trucks, the Union query will fail.
see post #24 for Temp table solution.

Ok, I am an idiot sorry, I saw the correct info. One thing I am getting an error now trying to open the report Truck Log it gives a parameter error asking for tblMain.TruckID which is a valid field.

 

[arnelgp]

use TruckLog2 report.
also i added another module modFitTextInDetailOfReport.
there is a function here, fncFitText() that is being called in the Detail Format event of the report (report must be in Print Preview).
this will reduce the Font size when the Text on your textbox will not fit (therefore will eliminate the "####" on the report).

 

[T. McConnell]

You guys rock!!!! I love this forum as it has helped me so much and lots of the users here who keep this forum going. Thanks again for all the help on this matter. I'm going to button up a couple of things and if everything goes good I'll go ahead and close this as solved.