Solved Open a report or reports does not open COMPILED

[mloucel]

Here is one for the books, I would much appreciate your help understanding the WHY..

Disclaimer:
- Some fancy code was copied from @arnelgp , @Pat Hartman @isladogs and some other wonderful programmers I appreciate.
- ALL data contained in the DB is 100% FAKE is test data.

I created a very simple report that SHOULD open from the main menu, it does open when I test, but it simply does not open when I tried with the program compiled, I have tried already a few things to no avail, I hope anyone can help me solve my issue, I am not an expert in Access but I sort of move my way around.

I attached a compiled version in the link but you can compile as well.

to RUN the program:
The User is SU and the Password is 1234 [Super User]
Regular User: Sarahm Password Menchaca. [Theres is a period]
VBA has no password.

If I use the SU account, the report(s) open without any issue [Using the report on the Menu or the TEMP button I create to test ]

now login with the Regular User account, and you will see that the report does not open, from either the Menu report or the temp button

There are NO restrictions whatsoever for ANY user to use the reports, the User Name and Level is simply used to stop the REGULAR users to change or add users to the DB, so it makes no difference if it is level 1 [Admin] or Level 3 Simple user, literally, we ALL know how those permissions work, we control what to do with them, but in this case it has NO BEARING whatsoever it is simply ignored.

Still the report does not open at ALL.

The program is too big now so I had placed the TEST DB in my google drive:
Test DB Link

Any Help will be appreciated..

Thanks

Maurice.

 

[theDBguy]

Hi. I tried to take a look, and I can see what you're saying. I couldn't run your ACCDE, because I'm not using 64-bit Access. So, I only looked at your ACCDB file. When I log in as SU, I can open the report. When I log in as Sarah, the report won't open. I added some MsgBox to the Click event of the button and in the Open and Close event of the report to see if the code to open the report was executing and if the report was opening or not (maybe just not visible). From that, I can tell that the report was opening but not showing up on the screen. I thought maybe it was hiding behind one of the other windows, but I couldn't find it. As a test, I disabled your fancy APIs for manipulating the screens and when I log in as Sarah again, the report opened. To see what I mean, I have uploaded the file I modified to your Drive folder. Hope that helps...

 

[mloucel]

Hi. I tried to take a look, and I can see what you're saying. I couldn't run your ACCDE, because I'm not using 64-bit Access. So, I only looked at your ACCDB file. When I log in as SU, I can open the report. When I log in as Sarah, the report won't open. I added some MsgBox to the Click event of the button and in the Open and Close event of the report to see if the code to open the report was executing and if the report was opening or not (maybe just not visible). From that, I can tell that the report was opening but not showing up on the screen. I thought maybe it was hiding behind one of the other windows, but I couldn't find it. As a test, I disabled your fancy APIs for manipulating the screens and when I log in as Sarah again, the report opened. To see what I mean, I have uploaded the file I modified to your Drive folder. Hope that helps...

Krap... I can see clearly now... the rain is gone....

I went back to the post where @isladogs talks about it, and you are right

Colin's 2 Cents

So to be honest the idea of having a clean version to work is simply amazing and I love it, but creates a struggle at print time, since the printing window disappears, for some weird reason and I can't figure it out, I hope Colin can see this and give us his knowledge but is just a hope.

for now I guess I will have to lock and hide as many objects so that the End User does not paly around with the tables, queries and all that stuff, yes is sort of lock while compiled but still a risk, so I guess I will have to re-align my approach to save or salvage as much as I can this project.

Thank you sir.

 

[theDBguy]

Krap... I can see clearly now... the rain is gone....

I went back to the post where @isladogs talks about it, and you are right

Colin's 2 Cents

So to be honest the idea of having a clean version to work is simply amazing and I love it, but creates a struggle at print time, since the printing window disappears, for some weird reason and I can't figure it out, I hope Colin can see this and give us his knowledge but is just a hope.

for now I guess I will have to lock and hide as many objects so that the End User does not paly around with the tables, queries and all that stuff, yes is sort of lock while compiled but still a risk, so I guess I will have to re-align my approach to save or salvage as much as I can this project.

Thank you sir.

You're welcome. Good luck!

 

[isladogs]

Sorry but I don't have time to look at your app at the moment.

You didn't mention it in post #1 but it seems you are hiding the application interface
There are several ways of handling reports successfully with the application window hidden.
In each case, you do have to make all reports popups as well as all forms. Did you do that?

See the latest version of my example app:

Control Application Interface

This article discusses how the Access application interface can be controlled including hiding the navigation pane, ribbon or the entire application interface. Some methods can help make a database more secure. The example app opens with a form 'floating independently' on the desktop. Various...

www.isladogs.co.uk

 

[mloucel]

Sorry but I don't have time to look at your app at the moment.

You didn't mention it in post #1 but it seems you are hiding the application interface
There are several ways of handling reports successfully with the application window hidden.
In each case, you do have to make all reports popups as well as all forms. Did you do that?

See the latest version of my example app:

Control Application Interface

This article discusses how the Access application interface can be controlled including hiding the navigation pane, ribbon or the entire application interface. Some methods can help make a database more secure. The example app opens with a form 'floating independently' on the desktop. Various...

www.isladogs.co.uk

Yes Colin I have all forms and report as well as pop-up, I just followed what @theDBguy found and is working, but for the life of my body I can't figure out how to fix it.

IF you happen to have a minute or 2 maybe you can, in the meantime I will just hide some essentials nav pane/ribbon I guess, so I'll go from there, one of them gotta be the culprit, once I know I'll post the issue and hopefully you can help us from there.

I'll leave this post open so I can Update.

 

[isladogs]

Just downloaded your files logged in as the regular user and the report opened for me on my first test run without changing anything in your code. I noticed the title bar was visible though the nav pane & ribbon were hidden

I closed & reopened & this time, no title bar & the report didn't appear.
A quick check indicated error 2501 on opening the report - something in your code was cancelling the report as it opens

I repeated a third time logging on first as the super user then as the regular user. That time it worked for both users!

You will need to step through your code to find out why its not working correctly. There is a logic error somewhere (or possibly a timimg issue?)

As a starting point, remove the line keeping the form visible when the report is opened then add code to the report close event to reopen the form (or make it visible again)

Private Sub Report_Close()
    Forms!MenuF.Visible = True
End Sub

You could also try opening the report in print preview. If so, restore the ribbon while the report is open and close it again when the report closes.

By the way, it took 7 seconds before the main form opens on my machine. Try to reduce that as well if you can

 

[isladogs]

It now seems to work reliably for me so I've uploaded my version to your GoogleDrive folder
The only changes made were to hide the main form on opening the report in report view and then make the form visible on closing the report

 

[mloucel]

It now seems to work reliably for me so I've uploaded my version to your GoogleDrive folder
The only changes made were to hide the main form on opening the report in report view and then make the form visible on closing the report

I'm going to check your version against mine here, for the love of my mother I cannot figure out, BEFORE your post I did change the code at the Menu level

        MsgBox "Opening the Access Window"
        SetAccessWindow (SW_SHOW)
        MsgBox "Open the Report"
        DoCmd.OpenReport "AuthByUserR", acViewReport
        MsgBox "Your report has finished.."
        HideAppWindowIcon Me

Thinking I should be able to control the access window, let the report print and at closing the report when the control is back to the menu, hide access window again, but I found out the Report does not stay open as Dialog the control goes back to the menu immediately, my msgbox is displayed with the report on the back of the msgbox but as soon as close the msgbox the accesswindow closes and the whole thing close again.

I'm going to compare your code line by line with mine, there's gotta be a bug either in the code [MINE] or at the properties level that I am missing, I downloaded your latest version of the CAI, and yours worked like charm, in my mind I am not doing anything different than what you did in CAI, so gotta be something in the properties or a bad misplaced line in my code.

Thanks for taking the time out of your busy schedule Colin, I should learn the hard way [which is the best way] going line by line.

Regards.

Maurice.

 

[mloucel]

@isladogs

I did not have to go that deep Colin, I see the changes you made literally GENIUS
Simple yet work like a charm,

At the MENU level at click:

Me.Visible = False

then when opening the report:

DoCmd.OpenReport "AuthByUserR", acViewReport, , , acDialog

then at the Report level, On Open and On Close procedures:

Private Sub CloseBtn_Click()
    
    DoCmd.Close
    
End Sub

Private Sub Report_Close()
    'restore the main form
    Forms!MenuF.Visible = True
End Sub

Private Sub Report_Open(Cancel As Integer)
    DoCmd.Restore
End Sub

Unless I am mistaken that's about it, and literally I am missing some key points:
1) Make the menu invisible so that the Report is displayed only
2) Make sure the Report is open in DIALOG mode
3) use Access to Report Close to make the menu visible again [last command in the chain]

the only command is obscure to me even after googling for about 10 minutes is the " DoCmd.Restore "
Seems it has to do with restoring the form or forms, for something I did not understand, really seems a command very useful but I haven't find a clear explain on why is there.
I'll keep digging.

I did only those changes to my code and it works like a charm,

Colin @isladogs no wonder you are excellent at what you do, thanks for the helping hand, my hat's off to you sir.

Maurice.

 

[isladogs]

Hi
Yes those were all the changes. Glad it’s now working for you.

The acDialog argument can probably be removed. Try disabling that part

There are 3 DoCmd options for controlling the size of an object. Maximize/Minimize and Restore. That line seemed to be essential to get this to work.

Thanks for the kind words. Ironically I’ve been battling with a similar issue today where using a splash form is preventing the print preview menu appearing in my reports. After several hours I still haven’t solved it.

 

[mloucel]

Hi
Yes those were all the changes. Glad it’s now working for you.

The acDialog argument can probably be removed. Try disabling that part

There are 3 DoCmd options for controlling the size of an object. Maximize/Minimize and Restore. That line seemed to be essential to get this to work.

Thanks for the kind words. Ironically I’ve been battling with a similar issue today where using a splash form is preventing the print preview menu appearing in my reports. After several hours I still haven’t solved it.

You make me feel I am not alone in this world.

A splash form... Incredible, like Popeye said.. Well blow me down!!!

Tried removing the dialog argument, didn't go well, so is back again, hey.. "IF IT'S WORKING DON'T FIX IT"

I even found another glitch with 2 errors 2501 and another I can't explain, so I had to sort to re-open [Docmd.OpenForm] the main menu, or otherwise gets stuck hidden, is solved thou.

Thanks for your understanding and mentorship, same as to many other gus and gals here that donate their time out of their busy schedules to help another pal to get over the "idiosyncrasies" of Access when its behavior is simply off.

My utmost respect to all.

Maurice.