Solved Report not Opening (1 Viewer)

[mloucel]

Hello gurus:

I have been working on a program for my office for the last year, many of you have helped me to accomplish many of the tasks, and I am grateful.

The application contains a few reports that are working perfectly fine, except for this one:
"ReportForAuthorizationsR"

Background:
I use some really cool techniques by @isladogs [Colin] that he has kindly provided in his website: [https://www.isladogs.co.uk]
This allows me to hide application window for access leaving a really clean environment.
Everything works fine, except for 1 report, that happens to be the most important report of all, since is the one that the administration will use the most.

The Problem as far as i figured out is with this module:
HideAppWindow Me
which is loaded in the MAIN program at the splash screen, but in this special TEST program I created I load this module in the form: ReportsMenuAllF
There is another module as well: HideRibbon which simply works fine and gives me no issues whatsoever.

If I disable the HideAppWindow Me from loading the report works fine, but as soon as I enable that module, all goes to the trash and access closes or stays hanging in the background and i have to use TASK MANAGER to close.

The Report is created first by selecting a range of dates, then opening this form: ReportFormForAuthorizationsF
This form allows the end user to sort and filter any of the presented data on screen, once the EU is happy, there is a Print button with the following code:

Private Sub PrintBtn_Click()
' Hides the Form NOT CLOSE so that the report can be created
' using the data sort and filter from the form
    Me.Visible = False

    DoCmd.OpenReport "ReportForAuthorizationsR", acViewPreview, windowmode:=acDialog
    'DoCmd.OpenReport "ReportForAuthorizationsR", acViewReport, windowmode:=acDialog
    
    Me.Visible = True
End Sub

Once the report is opening the following code must be run from the Report itself, to pass the filters and sorts to the report:

Private Sub Report_Open(Cancel As Integer)
    
    Me.Filter = Forms!ReportFormForAuthorizationsF.Filter
    Me.FilterOn = Forms!ReportFormForAuthorizationsF.FilterOn

    Me.OrderBy = Forms!ReportFormForAuthorizationsF.OrderBy
    Me.OrderByOn = Forms!ReportFormForAuthorizationsF.OrderByOn
    
End Sub

I have tried many different things but I cannot figure out why all the other reports work fine and only this one does not.

I was going to attach the program here in the forum but the small test version is about 16 mb zipped file and is too big, so the only way I can do that is using my google drive, so I uploaded the small test app version there if anyone wants to see what I have and hopefully figure out what is going on.

Here's the link:

Test - Google Drive

drive.google.com

THE PROGRAM CONTAINS NO IDENTIFIABLE DATA, ALL DATA HAS BEEN SCRAMBLED AND ALL NAMES ARE FAKE.

Thank you so much in advance for any help.

Maurice

 

[June7]

File does not close properly at all when windows are hidden, not even when reports are not run.

Why is file size so large for so little data and few objects (download was 18MB)? Zip file is still over 15MB. C&R didn't help.

I've never seen any real benefit to hiding app window. Implementing customized ribbon yes.

 

[xavier.batlle]

mloucel​

Don't ask me why, but if you add Docmd.Restore when opening the report, it works!

Private Sub Report_Open(Cancel As Integer)
 
    DoCmd.Restore  ' Line to add
 
    Me.Filter = Forms!ReportFormForAuthorizationsF.Filter
    Me.FilterOn = Forms!ReportFormForAuthorizationsF.FilterOn

    Me.OrderBy = Forms!ReportFormForAuthorizationsF.OrderBy
    Me.OrderByOn = Forms!ReportFormForAuthorizationsF.OrderByOn
 
End Sub

 

[mloucel]

mloucel​

Don't ask me why, but if you add Docmd.Restore when opening the report, it works!

Private Sub Report_Open(Cancel As Integer)
 
    DoCmd.Restore  ' Line to add
 
    Me.Filter = Forms!ReportFormForAuthorizationsF.Filter
    Me.FilterOn = Forms!ReportFormForAuthorizationsF.FilterOn

    Me.OrderBy = Forms!ReportFormForAuthorizationsF.OrderBy
    Me.OrderByOn = Forms!ReportFormForAuthorizationsF.OrderByOn
 
End Sub

DARN YES. it does... I never in my wildest dreams would have think about that. Thanks mate.

Also, can you please share with me, how were you able to reduce the size of the file from 15 - 20 mb to barely 2mb ?
Now that is awesome, and could help me make my original slimmer, which is already about 40mb just the FE.

Thank you so much for your advise.
Maurice.

 

[xavier.batlle]

DARN YES. it does... I never in my wildest dreams would have think about that. Thanks mate.

Also, can you please share with me, how were you able to reduce the size of the file from 15 - 20 mb to barely 2mb ?
Now that is awesome, and could help me make my original slimmer, which is already about 40mb just the FE.

Thank you so much for your advise.
Maurice.

Easy, I created a new database and imported all the objects.

 

[mloucel]

Easy, I created a new database and imported all the objects.

Well BLOW ME DOWN!!!!
Thanks mate.

 

[The_Doc_Man]

For what it is worth, if you wanted to reduce the size of a native Access DB, front-end OR back-end, you just open the file and select the "Compact and Repair" option. Though if the DB has a corruption problem, that might be risky. If the C&R worked, it did so by creating a new database (probably called DB1), copying over all of the valid objects, and then renaming the old DB and giving the original name to the new DB, after which the old DB is deleted UNLESS there was an error during the operation. @xavier.batlle did the same sequence, except it sounds like he did it by hand.

 

[June7]

That command was already in code for UsersReport. I added to other report. No change, still fails.
Also, as I said, I tried C&R and that did not help.
Something wrong with posted file.

 

[mloucel]

That command was already in code for UsersReport. I added to other report. No change, still fails.
Also, as I said, I tried C&R and that did not help.
Something wrong with posted file.

Thanks mate but @xavier.batlle fixed it, it was missing 1 instruction at the Open Report level, but thanks.

 

[mloucel]

For what it is worth, if you wanted to reduce the size of a native Access DB, front-end OR back-end, you just open the file and select the "Compact and Repair" option. Though if the DB has a corruption problem, that might be risky. If the C&R worked, it did so by creating a new database (probably called DB1), copying over all of the valid objects, and then renaming the old DB and giving the original name to the new DB, after which the old DB is deleted UNLESS there was an error during the operation. @xavier.batlle did the same sequence, except it sounds like he did it by hand.

Thanks @The_Doc_Man .
I Did the C&R, file remained at 60MB [The Original FE with all the bells and whistles], The backend it did went down, but it was expected, is not really a big deal, since I am going to compile anyway, it was just mere curiosity.
I just create a brand new DB then I Import all to the new one and did a C&R and it went down significantly to 40MB, which to me is ok.
Thanks for your wisdom.

 

[June7]

Thanks mate but @xavier.batlle fixed it, it was missing 1 instruction at the Open Report level, but thanks.

Which doesn't explain why posted TEST db, even with code adjustment, still fails. However, as long as you have a working file, all is good.

 

[isladogs]

I'm a bit late to the party and am aware that @xavier.batlle found the solution to your main problem.
The DoCmd.Restore line is necessary due to the API arguments used in the HideAppWindow function (BTW - its a function - not a module)

If you want to hide the application interface, you need to first ensure there are no code issues or you will get unexpected results (as in this case).

I looked at your example app this morning and found several things wrong (apart from the issue you raised). For example:

• You have attached a lot of large shared images to the Image Gallery which causes your file to be unnecessarily large at 20 MB. Compacting won't help here. I removed all of those attached images and then compacted. The file size dropped to 1.2 MB. The same result is obtained by importing all objects (except for system tables) to a blank database. Recommend you only place small images in the image gallery
• You have a startup form and an autoexec macro which opens the same form. Use one or other but not both
• Your forms and reports are modal and also opened using acDialog. Again, use one or other (depending on the need) but not both
• You don't need any of the filter / order code in the Report_Open event as that is already set in the record source & property sheet
• The app doesn’t close from the main form leaving a hanging instance in Task Manager. Changing the code on the Close button to Application Quit and removing the autoexec macro fixes that.
• Personally I would use Exit (or Quit) as the button caption on the startup form and change the other form Exit captions to Close as they just close the forms
• There was no Close event on the report ReportForAuthorizationsR

I fixed most of those items in the attached db. Might be worth having a look.

Also:
What is the point of first opening a read-only form (ReportFormForAuthorizationsF) before opening the report?
As the report is opened in preview mode without the ribbon, you are making it unnecessarily hard to print the report.
Suggest using report mode with a print button.

 

[mloucel]

I'm a bit late to the party and am aware that @xavier.batlle found the solution to your main problem.
The DoCmd.Restore line is necessary due to the API arguments used in the HideAppWindow function (BTW - its a function - not a module)

If you want to hide the application interface, you need to first ensure there are no code issues or you will get unexpected results (as in this case).

I looked at your example app this morning and found several things wrong (apart from the issue you raised). For example:

• You have attached a lot of large shared images to the Image Gallery which causes your file to be unnecessarily large at 20 MB. Compacting won't help here. I removed all of those attached images and then compacted. The file size dropped to 1.2 MB. The same result is obtained by importing all objects (except for system tables) to a blank database. Recommend you only place small images in the image gallery
• You have a startup form and an autoexec macro which opens the same form. Use one or other but not both
• Your forms and reports are modal and also opened using acDialog. Again, use one or other (depending on the need) but not both
• You don't need any of the filter / order code in the Report_Open event as that is already set in the record source & property sheet
• The app doesn’t close from the main form leaving a hanging instance in Task Manager. Changing the code on the Close button to Application Quit and removing the autoexec macro fixes that.
• Personally I would use Exit (or Quit) as the button caption on the startup form and change the other form Exit captions to Close as they just close the forms
• There was no Close event on the report ReportForAuthorizationsR

I fixed most of those items in the attached db. Might be worth having a look.

Also:
What is the point of first opening a read-only form (ReportFormForAuthorizationsF) before opening the report?
As the report is opened in preview mode without the ribbon, you are making it unnecessarily hard to print the report.
Suggest using report mode with a print button.

Colin I am simply blown away..

Every time I learn something new, and you simply are a great teacher.
About the issues:
-Yes, I am sorry I am still confusing the words and keep saying module instead of function, point noted and taken.
-Thanks for the warning, I am trying to be more careful, but it happens.
-On the images I am working on a technique I found also with Richard Rost, instead of having the image file embedded use a link, I control the folders where the BE is so I will leave those files there and use his technique to show the files without the need to have them embedded.
-I forgot about the startup was there already.
-I always thought I needed acDialog, I read somewhere, but I will start changing the code in all of the forms.
-This about the report really got me by surprise, in the code Mr. Richard Rost says it is a must to have those lines on the Report Open, when I remove the lines on my file, the report did not work, but it does in the one you sent me so I have to check your file and the file I have to see where the difference is.
-The suggestion of Close button is applied and is working, I love the colors you choose, they are impressive.

-- The ReportFormForAuthorizationsF, is the form where the user can Sort/Filter whatever fields they need to make the report based on their own needs, once they have their data their own way, the report prints.
On the Video From Richard Rost it is necessary to open the report as preview mode, I tried using acViewReport instead so I can place a PRINT button on the form but it didn't work, I'm going to try with the file you sent and let's see if I can figure it out, as well the End Users are used to right Click and choose Print anyhow, never the less, the idea of the print button has been in my mind forever.

Again, Thanks for your time and valuable input, wishing you and your family a wonderful new year, full of awesome new things.

 

[mloucel]

@isladogs

I tested the following:

Once I changed the report to acViewReport , all hell is loose, the report does not print only the data I previously sorted/filtered using ReportFormForAuthorizationsF, and no matter what I lose control, I remove the Remarks of the code at the Open Report Level and now the Report is opening perfectly, with only the number of records that ReportFormForAuthorizationsF is actually showing me.
I remember now Richard said that it was the only way for the report to work.

 

[isladogs]

@isladogs

I tested the following:

Once I changed the report to acViewReport , all hell is loose, the report does not print only the data I previously sorted/filtered using ReportFormForAuthorizationsF, and no matter what I lose control, I remove the Remarks of the code at the Open Report Level and now the Report is opening perfectly, with only the number of records that ReportFormForAuthorizationsF is actually showing me.
I remember now Richard said that it was the only way for the report to work.

Sorry but I've no idea what 'all hell is loose' and 'no matter what I lose control' actually mean.

However, if your users are going to sort / filter on the form, then I agree that you do need the code in the Report_Open event
In that case, I would suggest removing it in the property sheet.

I can't comment on what Richard Rost has said as I haven't seen his comments.
However, the report should work correctly in all of the following scenarios:

• report view with a print button
• print preview with the print preview ribbon restored to allow printing
• sent directly to the printer

I always thought I needed acDialog, I read somewhere, but I will start changing the code in all of the forms.

FWIW, I only rarely use acDialog or Modal. Each have their uses but should definitely not be treated as the default mode

 

[mloucel]

Sorry but I've no idea what 'all hell is loose' and 'no matter what I lose control' actually mean.

However, if your users are going to sort / filter on the form, then I agree that you do need the code in the Report_Open event
In that case, I would suggest removing it in the property sheet.

I can't comment on what Richard Rost has said as I haven't seen his comments.
However, the report should work correctly in all of the following scenarios:

• report view with a print button
• print preview with the print preview ribbon restored to allow printing
• sent directly to the printer

FWIW, I only rarely use acDialog or Modal. Each have their uses but should definitely not be treated as the default mode

No I am sorry for my stupid explanation.
Yes they do sort and filter and based on those filters/sorts, the report is generated, so when I tried the report option it doesn't work because the whole Table is dumped into the report, and it doesn't matter if I use the code on Open Report, it only works on Preview.

I am using acDialog or Modal now I will use modal only, forcing the user to 1 screen at a time, I tried not to but they were able to make a mess and somehow corrupt the DB, I know I am at fault in many ways but once I went modal all problems were solved.
So: IF is working, don't fix it.

If you have any suggestion to do the report in a different way so that I can use report view to add a print button it will be appreciated.

Thanks for your wisdom, Colin.

 

[isladogs]

Yes they do sort and filter and based on those filters/sorts, the report is generated, so when I tried the report option it doesn't work because the whole Table is dumped into the report, and it doesn't matter if I use the code on Open Report, it only works on Preview.

I am using acDialog or Modal now I will use modal only, forcing the user to 1 screen at a time, I tried not to but they were able to make a mess and somehow corrupt the DB, I know I am at fault in many ways but once I went modal all problems were solved.
So: IF is working, don't fix it.

If you have any suggestion to do the report in a different way so that I can use report view to add a print button it will be appreciated.

Done!

I've updated it as follows:
a) Added Remove Filters & Remove Sorts buttons to your form - both are hidden when the form is first opened and become visible when the end user adds a sort or a filter:

b) The report now opens in report view with the same record source as on the form including sorts & filters. This just needed one line of code added

NOTE:
1. When the report is closed, the date form reopens. The authorization form remains open hidden so any previous filters / sorts are still in place when it reopens normally

2. With the Access interface hidden, major problems occur if users right click and select Design view.
Normally I prevent this by disabling shortcut menus in Access Options.
I can't do that here as you want users to be able to sort / filter the authorization form.
However I have disabled shortcut menus on the startup & date forms to prevent users right clicking on those

 

[mloucel]

Done!

I've updated it as follows:
a) Added Remove Filters & Remove Sorts buttons to your form - both are hidden when the form is first opened and become visible when the end user adds a sort or a filter:

View attachment 122761

b) The report now opens in report view with the same record source as on the form including sorts & filters. This just needed one line of code added
View attachment 122762

NOTE:
1. When the report is closed, the date form reopens. The authorization form remains open hidden so any previous filters / sorts are still in place when it reopens normally

2. With the Access interface hidden, major problems occur if users right click and select Design view.
Normally I prevent this by disabling shortcut menus in Access Options.
I can't do that here as you want users to be able to sort / filter the authorization form.
However I have disabled shortcut menus on the startup & date forms to prevent users right clicking on those

Incredible ..
I have questions since I am still learning, but I believe is time for me to dig deeper on your code and go back to your CAI page.

I really hope someone else can use your code as well, it is really good specially in my report situation.

Appreciate your mentorship Colin.

Have a wonderful 2026.

 

[isladogs]

You're welcome.
I tried to comment the main changes I made but ask if anything is unclear.

I recommend one further change:
Your form has so many columns that it could be hard for users to keep track of sorts & filters.

I've made a further tweak so users can see the details about any filters or sorts used on the form / report
The same idea is used in my example app:

Highlight Filtered Columns

This example app demonstrates how to highlight filtered columns in a continuous form. Very little code is required.

www.isladogs.co.uk

The form info is updated in real time as changes are made:

Here is a different example from the report:

I've added the sort/filter info in the form footer area and the report header area (but you can move / remove them if you wish).

I’ve also set Can Shrink = Yes on those report controls to avoid having empty space if there are no sorts or filters.
Unfortunately those properties have no effect when used in forms.

Code changes made are marked as v3 in my comments on both form & report

 

[mloucel]

You're welcome.
I tried to comment the main changes I made but ask if anything is unclear.

I recommend one further change:
Your form has so many columns that it could be hard for users to keep track of sorts & filters.

I've made a further tweak so users can see the details about any filters or sorts used on the form / report
The same idea is used in my example app:

Highlight Filtered Columns

This example app demonstrates how to highlight filtered columns in a continuous form. Very little code is required.

www.isladogs.co.uk

The form info is updated in real time as changes are made:
View attachment 122776

Here is a different example from the report:
View attachment 122777

I've added the sort/filter info in the form footer area and the report header area (but you can move / remove them if you wish).

I’ve also set Can Shrink = Yes on those report controls to avoid having empty space if there are no sorts or filters.
Unfortunately those properties have no effect when used in forms.

Code changes made are marked as v3 in my comments on both form & report

Thanks Colin @isladogs
The use of tempvars in this version is simply GENIOUS, I had no Idea you could do that also all the additions in V3 are so simple [When you know], but I didn't know they existed, I am learning so much.
That simple line of code is really good for me as a newbie to see what is going on with the sort and filters and how Access sees it, that really helps a LOT, when you can see what ACCESS sees and how my line of code works, that is simply fascinating.

Addendum:
I just noticed something:
The "ReportForAuthorizationsR" when is opened does not have a sort by the AuthorizationID, which is what I want, I want the data sorted by AuthorizationDate, [All is fine so far], now let me give you an example:

I open ReportFormForAuthorizationsF and over ApprovedDate I filter for "ApprovedDate is not Null", gives me 33 records, PK is NOT sorted [That is by default] and AuthorizationDate is sorted [is what I want]
now I click Preview Report:

The Report shows Sorted by AuthorizationID [The PK] not by AuthorizationDate as it should, I've checked all the code that goes from the form to the report, and as well the Query "qryAuthorizationAllForReport" and I don't see where the sort is changed to the PK instead of the AuthorizationDate, I'm baffled.