Results 1 to 14 of 14
  1. #1
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    92

    Programmatically rearrange a report before printing it

    Well, here's what I'm trying to do and miserably failing at it. Any tip on how to do it or the correct approach will be welcome.



    I have a different number of reports to print resumed data of components or workstations based on search criteria. All of them work great but share the same basic trait. They're reports with their design set beforehand.

    Now I have this last report that will contain most if not all the possible data of each component on a workstation. The report is based on a form showing this same data through a number of subforms, only with the design tweaked so that it has a better layout for printing. Thing is I'd like to programmatically hide or resize the subforms and fields on the report depending on if they have any record or not, so that it takes up as few pages as possible (hopefully just one). I've tried this on the report.load event, but if I open the form in normal view, it just prints without launching the code to readjust the report. Opening the report in Layout view works, but I can't get it to open in an emergent window in that mode, which I really need (yes, the popup property is set to true but it seems to cancel when opening in layout). And in preview mode it opens right the way I need it to but the code fails because preview mode doesn't allow any change to the properties of the fields.

    What's the correct way to tackle what I'm trying? Ideally I'd want the report to open, rearrange fields through the code and still show a "preview" before letting the user choose to print it or not.

    Thank you very much!

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think you want the OnFormat event to make changes (Hiding, resizing etc.) to how it actually appears.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    92
    Ok, just learned that the Open event is run before the form is previewed or printed, so it would work on the report if opened on preview mode, but now the following code line that worked on the Load event throws a "runtime error 2455: You entered an expression that has an invalid reference to the property Form/Report". The line in question is:

    Code:
            If TypeOf ctrol Is SubForm Then
                If ctrol.Form.Recordset.RecordCount = 0 Then     '<-- this is the line throwing the error
    So it basically cycles the controls, detects is a subform but then the following line which worked well before when used on the report Load event is giving that error when used on the report Open event. Maybe the control is not yet fully initialized so it doesn't have a Recordset?

    Edit: Hi Minty, seems you posted while I was writing this response. I don't see a OnFormat event on the Report documentation from Microsoft, but I'll give that a go and keep you updated. Thanks!

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Most objects aren't "Set" (established?) during Form Open. Recordsets aren't.

    You should be aware that Subforms load before the Main form.
    I'm not so certain this applies for reports and sub reports, I nearly always use the format event in a report.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    92
    A little update with the Detail_Format event.

    -With the report opened in 'preview' view (my ideal approach for this situation) it works as the report_load event, says the fields cannot be altered after displaying/printing them.
    -With the report opened in 'layout' view works exactly as the report_load event too, it resizes the fields and makes most of the adjustments I need, but the report doesn't open as a popup, nor would I really want to present the form to the user in that view.
    -And now for the fun part, if opened in 'normal' view, it doesn't display the report but tries to print it directly, as expected. But rather than not running the code (as happened with the report_load event) it runs the code but stops on the first control, as if it couldn't resize the columns in the subform and thus couldn't continue, but it doesn't throw any error. The printer icon pop ups in the task bar showing 0 documents for the printer, and after some seconds it goes away without printing the report, resized or otherwise.

    I'll give it another twist, see if I can make any progress. Any suggestions are still kindly welcome

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm not so certain this applies for reports and sub reports
    I checked that out about 2 weeks ago for somebody else. Pretty sure it does not mirror the form situation but it's easy enough to check using msgbox function for each object to see which you get first. I would have thought that DetailFormat event would be the right one and have seen code that resizes controls based on empty fields. However, it didn't state which view was being used. You might have to settle for normal view but of course, the error has to be figured out first. If you can copy the db, compact, zip and post here it would certainly help. I find that many requests for report design help are complicated, and unless you do this sort of thing all the time, figuring out a solution can be difficult. Having something established to work with is a big bonus.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    92
    Sorry for the late response, I'm actually doing this just as a side project to turn our various excel data sheets into a relational DB and learn some Access and VB in the process, but some days I barely have any time to invest on it.

    Believe me, I know it's a fool's errand to try and solve this without access to the actual DB, but as I said in another thread, it would take too much effort to remove all the sensible information (telephones, addresses, emails, serial numbers, license keys, etc), and while not ideal I still have the option to go back to letting the user resize or hide fields on the form and print it with whatever is left by the user, just wanted to automatize it and make it cleaner hoping it would be easier, but it's proven to be quite of a challenge actually. So yeah, I'm sorry but I can't (maybe should say won't) post the DB, just posting this to see if anyone on the forum experienced something similar and can guide me a little (which you all already have )

    Anyway back to the matter at hand, I completely cleaned the report code (because it was sometimes throwing a "Report open cancelled") and reduced the resizing repositioning code just to start at the easiest and if it works slowly start ramping things up. Gave up on the preview view because it's constantly giving me the "can't modify properties after the object has been displayed or printed" or whatever error, so I'm back to the normal view with the detail_format event. The current, simplified code of the event (which is the only code in the report right now) is the following one:

    Code:
    Private Sub Detalle_Format(cancel As Integer, FormatCount As Integer)
        Dim ctrol As Control
        Dim subCtrol As Control
        MsgBox "Entering Format event"
        For Each ctrol In Me.Controls
            If TypeOf ctrol Is SubForm Then
                If ctrol.Form.Recordset.RecordCount = 0 Then
                    MsgBox "Hiding " & ctrol.Name
                    ctrol.Form.Visible = False
                Else
                    MsgBox "Resizing " & ctrol.Name
                    For Each subCtrol In ctrol.Form.Controls
                        If TypeOf subCtrol Is ComboBox Or TypeOf subCtrol Is TextBox Then
                            MsgBox "Resizing column " & subCtrol.Name
                            subCtrol.ColumnWidth = -2
                        End If
                    Next
                End If
            End If
        Next
    End Sub
    I threw in a couple msgboxes because setting interruptions in either the openReport call or the format function declaration wouldn't halt the execution for some reason. It shows the "entering function" and the "resizing" messages (because the first subform always has data), then the first "resizing column" message then it stops with no error message. The printing icon displays with 0 pending documents, disappears, nothing gets printed. Obviously the function not working properly is the cause, but since no error is thrown I have no clue why it struggles with the subCtrol.ColumWidth = -2 instruction.

    Btw, I created a new fresh DB, imported everything, set up the table relations, same behaviour. So it's not a corrupted DB issue either apparently (I had already repaired and compacted it anyway)

    Edit: I took out the column resizing part and the forms are hiding correctly and the report is getting printed, so it's definitely the columnWidth instruction halting the execution. As to why, I have no clue, it's working great in layout mode.

  8. #8
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'm going to stab a guess here, but I don't think a textbox has a column width property unless this is a datasheet view, but I thought it was a report?
    So trying to set it will probably give you an error.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    92
    The instruction was working good when the report was opened in layout mode and in preview mode it only complained that it couldn't be altered in preview mode, not that it dind't exist. The report has been created from a form so it has a number of Subform/Subreport which are in datasheet view.

    Anyway, right now I left the column resizing aside for now and was trying to settle with hiding the subforms that don't have any data and move the rest together so they take up less space (hopefully taking no more than 1 page in landscape). The hide part is working great both in preview and normal views, the moving part not so much. So here is my train of thought. I could start by the y axis (top property) position of the first form, and for every subform with data (which are set to 'can shrink' and do so perfectly depending in the number of records), move it to that y position, and increase the position by the inner height of the form and a bit of extra space to move the next subform with data to that position and so on. But if I try the following:

    Code:
    ctrol.Move 113, posY, ctrol.Form.InsideWidth, ctrol.Form.InsideHeight
    with 113 being the fixed x axis position (left property) for each form, I get a 2113 "The value you entered isn't valid for this field" in preview mode (yes, the y position is calculated in twips too), and in normal mode it just terminates without error and without printing anything, as with the column resizing code. If i move the form instead of the control though

    Code:
    ctrol.Form.Move 113, posY, ctrol.Form.InsideWidth, ctrol.Form.InsideHeight
    no error is thrown but the forms just don't move, they preview or print as if the code wasn't there (even though the code is executing and adjusting the posY value properly, as checked through msgboxes). I've had no luck finding a similar problem to mine. Any ideas?

  10. #10
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    To hide the sub reports I would set their container control height to .0001 then set them to grow = true.
    If there is no data they won't appear. If there is data other controls below them will be shifted down to accommodate the size accordingly.

    Unless I'm being very confused Me.Form (anything .Form?) isn't going to work in a report?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    92
    Quote Originally Posted by Minty View Post
    To hide the sub reports I would set their container control height to .0001 then set them to grow = true.
    If there is no data they won't appear. If there is data other controls below them will be shifted down to accommodate the size accordingly.
    What a nice little workaround! It still leaves a little gap for the subform label (which lies on top of the form) when hidden but that is totally acceptable. Thank you so much Minty! I will give it another go at the column resizing matter and I'll call it a day.

    Quote Originally Posted by Minty View Post
    Unless I'm being very confused Me.Form (anything .Form?) isn't going to work in a report?
    Dunno about that, maybe. But it's identifying the control type as subform so that might not be it.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If it works it ought to be a fluke, if in fact it is a report in the sub control - maybe even a bug. With a test report open, in the immediate window if I type
    ?reports!rptsubtest.child0.report.name I get a valid response (the name of the sub report)

    If I type
    ?reports!rptsubtest.child0.form.name I get an error (object is closed or doesn't exist)

    The only way I can see that you don't raise an error is that the sub control doesn't contain a report, it contains a form. I cannot recall ever putting a form on a report except to test someone else's issue. AFAIC it's a no-no.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    92
    Well, then there's definitely something wrong with my Access or my DB. I gave it a go and created reports from each subform. Then removed the subforms from the main report, replaced them with the reports i created. When I opened the main report with the code
    Code:
    If typeof ctrol is SubReport
    the else code runs, if i use
    Code:
    If typeof ctrol is SubForm
    then the if code runs even if the subreports are freshly created and there's actually a report in the container. In fact ctrol.Form will now throw an error while ctrol.Report will work, but still identifies the control type as a subform.

    Anyway, I played around a bit and it seems with the subreports I might be able to resize the fields the way I want to (at least know it's letting me resize them, now it's just a matter of making it all fit). But I'm having the following problem. To hide the subreports that don't have data, I'm replacing the ctrol.Form.Recordset.Recordcount with ctrol.Report.Recordset.Recordcount, and that throws a Error 32585: This feature is only available in an ADP project. Ctrol.Record.count is returning values above 0 for every report regardless If it has records or not, so it's still showing the label and an empty report for the reports without records. So how do I get the record count for each subreport?

    Edit: Never mind, got it working with the HasData function
    Last edited by Lhoj; 07-07-2021 at 06:03 AM.

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    still identifies the control type as a subform.
    Because that's what it really is. I thought the first comment was about .Form, not what precedes it.
    In other words, the subform control is the container that contains the form OR report. If you want to refer to the object that the container/subform control holds, you have to use .Form if it is a form, and .Report if it is a report. The comments don't pertain to the container object, they pertain to referencing the object that it holds.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 08-10-2018, 07:22 AM
  2. Excel macro to rearrange cell information
    By Charles Notley in forum Macros
    Replies: 1
    Last Post: 04-11-2017, 01:15 PM
  3. How to change report to page 2 programmatically?
    By naeemahmad in forum Reports
    Replies: 23
    Last Post: 12-04-2013, 12:27 AM
  4. Replies: 2
    Last Post: 08-29-2010, 01:17 AM
  5. Rearrange name in Table
    By Brian62 in forum Access
    Replies: 5
    Last Post: 09-15-2009, 03:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums