Reports and hidden interface

[Lhoj]

Hi all! Back again with another of my weird questions. It's a bit of a mix of VBA code, forms, reports and access in general so I'll put it in here. Any admin feel free to move it if it suits another section better.

Thing is, I have developed an inventory database in Access with the Access interface visible for simple commodity while programming and designing it, but with the idea of hiding it afterwards and making the user work through the forms and not let them get their hands on the tables directly. Unfortunately I have recently discovered that report previews won't show with the access interface hidden. I need to show a preview because depending on the records fitting a search criteria, the report might span several pages and I want to let the users check the report before deciding they want to go ahead and print it. All forms and reports are modal pop-ups, meaning the report appears hidden (because of the hidden interface) but blocks the access to the rest of the elements, forcing to shut the application by the task manager.

This is the code I'm using to hide the UI:

Option Compare Database

'Guarda Valor de Estados de Ventana
Dim dwReturn As Long

'Constantes de Estado de Ventana
Const SW_HIDE = 0
Const SW_SHOWNORMAL = 1
Const SW_SHOWMINIMIZED = 2
Const SW_SHOWMAXIMIZED = 3

'  Se identifica Plataforma 32  o  64  bits'
#If Win64 Then
    Private Declare PtrSafe Function IsWindowVisible Lib "User32" (ByVal hwnd As Long) As Long
    Private Declare PtrSafe Function ShowWindow Lib "User32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
#Else
    Private Declare Function IsWindowVisible Lib "User32" (ByVal hwnd As Long) As Long
    Private Declare Function ShowWindow Lib "User32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
#End If

'Llamada de funcion  para  ocultar Ventana  de  Access
Public Function fAccessWindow(Optional Procedure As String, Optional SwitchStatus As Boolean, Optional StatusCheck As Boolean) As Boolean

'Tres  Modos de llamada de Ventana:  Ocultada,  Visible,  Minimizada
If Procedure = "Hide" Then
    dwReturn = ShowWindow(Application.hWndAccessApp, SW_HIDE)
End If

If Procedure = "Show" Then
    dwReturn = ShowWindow(Application.hWndAccessApp, SW_SHOWMAXIMIZED)
End If

If Procedure = "Minimize" Then
    dwReturn = ShowWindow(Application.hWndAccessApp, SW_SHOWMINIMIZED)
End If

If SwitchStatus = True Then
    If IsWindowVisible(hWndAccessApp) = 1 Then
        dwReturn = ShowWindow(Application.hWndAccessApp, SW_HIDE)
    Else
        dwReturn = ShowWindow(Application.hWndAccessApp, SW_SHOWMAXIMIZED)
    End If
End If

If StatusCheck = True Then
    If IsWindowVisible(hWndAccessApp) = 0 Then
        fAccessWindow = False
    End If
    If IsWindowVisible(hWndAccessApp) = 1 Then
        fAccessWindow = True
    End If
End If

End Function

The autoexec form has the following on its onLoad event

fAccessWindow "Hide", False, False

Then I open the report with a simple OpenReport, as an example

DoCmd.OpenReport "Oficina_Movil", acViewPreview, , "[Id_oficina]='" & Me.Id_Oficina & "'"

The modal pop-up behaviour must remain (it's not specified on the OpenReport instruction because it's set up in the report properties).


Want to try to solve that without any external program or plugin like snapshot viewer, and the only workaround I've found is showing the Access interface before opening the report, then hiding it again when closing the report. So now the OperReport looks like:

fAccessWindow "Show", False, False
DoCmd.OpenReport "Oficina_Movil", acViewPreview, , "[Id_oficina]='" & Me.Id_Oficina & "'"

And the Close event of the report has the instruction to hide the interface again. This works as intended, the report shows and the interface hides again when closing it, but the form from which the report was invoked becomes irresponsive.

Ive tried:

Forms(Forms.Count-1).setFocus

and

Forms(Forms.Count-1).visible=true

Both seem to get the focus on the form, but it remains irresponsive, so I can't click anything and once again, since the forms are popup and modal too, I have to kill the app.

Any insight on how to return the control to the calling form after hiding the interface again?

[ranman256]

There’s no need for modal. If The form is open,user can enter data.
then either close it or make it invisible.
nothing to lock up your program.

the complicated api calls are not needed.

[Lhoj]

Sometimes I have more than one form open at the same time and having them not modal would let the user navigate through them, wouldn't it? I definitely need to avoid that, thus the modal property for them.

[Minty]

I would have a look at Colin's excellent demo here https://www.access-programmers.co.uk...askbar.293584/

I think he includes a method to handle the report previewing.

[Lhoj]

Thank you very much Minty, will check his demo and see if I can find a solution, and hopefully some other useful ideas to tweak my DB. That demo looks pretty polished and promising!

[Lhoj]

Ok, I've looked a bit through it and it's not exactly what I was looking for, he's minimizing the interface window instead of hiding it. But actually it could have its benefits. I wanted to hide it because otherwise clicking in the taskbar or alt-tab'ing in and out of it brings the interface back and it's gonna be a pain to handle it to minimize again in each form's onFocus or current or whatever events. Anyways, hiding the interface is making it imposible to alt-tab back to it or get back to it through the taskbar so it's really tricky to get back to access if you move to something else.

So now I'm thinking about the pros and cons of switching to a minimize/maximize interface approach or alternatively make my forms maximized so that the interface is not visible (would really mess all the appearance of all my forms so I would rather not).

All in all, I still haven't discarded the idea of hiding the interface completely and showing it just for the reports, so if anyone knows of a solution for the opening post issue I'm still open to suggestions.

[Minty]

As an aside - you can't set focus to a Form unless it has no controls on it.
Focus will always land on a control on the form, even if the all the tab stops are set to no.

[Micron]

making the user work through the forms and not let them get their hands on the tables directly.

Why not just hide the nav pane, disable shift bypass, distribute accde files only, etc. That's not the complete list but how much you have to do probably depends on your users and their level of expertise and intent.

[isladogs]

Ok, I've looked a bit through it and it's not exactly what I was looking for, he's minimizing the interface window instead of hiding it. But actually it could have its benefits. I wanted to hide it because otherwise clicking in the taskbar or alt-tab'ing in and out of it brings the interface back and it's gonna be a pain to handle it to minimize again in each form's onFocus or current or whatever events. Anyways, hiding the interface is making it imposible to alt-tab back to it or get back to it through the taskbar so it's really tricky to get back to access if you move to something else.

So now I'm thinking about the pros and cons of switching to a minimize/maximize interface approach or alternatively make my forms maximized so that the interface is not visible (would really mess all the appearance of all my forms so I would rather not).

All in all, I still haven't discarded the idea of hiding the interface completely and showing it just for the reports, so if anyone knows of a solution for the opening post issue I'm still open to suggestions.

No. I'm hiding the application interface but retaining a taskbar icon. That's why I use the code shown in my example app
If you hide the taskbar icon as well, it is difficult to swop between Access & other applications.

I'm not sure which version of my app you looked at.
Since first publishing that thread at AWF, there have been many further updates to the code.
For example I have solved the issue of the interface reappearing after users click on the taskbar icon
The latest version is 3.55 which you can find at post #23 of the thread at AWF. It includes code to open reports in various ways whilst the application interface is hidden
If you use print preview, the ribbon can be displayed temporarily whilst the report is visible & removed again when the report is closed

For further information, see my article on the topic at Control Application Interface - Mendip Data Systems

Please look at my latest version and come back with any specific questions

[Lhoj]

Sorry, Colin, my bad. For some silly reason I assumed it was a one time thing and I grabbed the one in the opening post. Latest version is exactly what I need.

Thank you so much!

[isladogs]

You're welcome.

For info, I've only just properly read all the code in your first post. Always interesting to see different approaches.
Not sure exactly why your code caused everything to lock up but personally I prefer only displaying one object ag a time.
As a result I rarely use Modal property for forms or reports as its just not needed.

I'd be interested to see if your app has any application interface features that I haven't included.
Can you upload a sample dB with no confidential data or send to me using the email address below. Its linked in my signature line

EDIT
For info, your Win64 conditional compilation is incorrect. It will compile but won't work.
For example, hWnd is a pointer so needs to be LongPtr not Long.
You can find correct APIs in my demo.

I prefer using #If VBA7 Then....

[Lhoj]

Yes, I see why keeping more than one object open can be troublesome, but for some cases (most times for fast consulting or even aesthetic reasons, for example when adding items to the inventory or components to a workstation I'd like to keep the workstation data or the last inventory search open side by side when I launch the "add item" form. For that I coded some functions to position the open forms around, while the modal property keeps them visible but inaccessible). Regarding the code for hiding the window, it's a code an acquaintaince of mine provided me. I'm a complete beginner in VB and it was way out of my league so I mostly left it untouched.

Anyway, since it would take me much time to clean the database of personal data, I attached a real simple one with a single form and report to reproduce the issue (using the modules from the demo, actually just the hideappwindow and setaccesswindow functions). I hide the interface in the form load event, then show the window again on the report load event (without closing the form), but the open popup form seems to merge back to the interface as a normal form. Then if I try to hide it again on the report close event, it hides the interface but the form becomes stuck. The obvious workaround would be to hide it in the form but neither the gotFocus, activate or current events seem to trigger. Tried a custom Hide public function on the form and invoked it from the report close event but the form gets stuck anyways.

Any tips on how to tackle this without closing and reopening the forms?

[Lhoj]

OK, gave up on the preview view. Either I show the interface and set the report as non modal giving the user the chance to close the whole application or I set the report as modal and get me stuck. I'm using the open in report view method and including a button to print the report and it's an acceptable outcome so I'm gonna go on with that. Thanks for all your help Colin!

[isladogs]

I've only just seen your last two posts. Do you still want me to look at the sample file you uploaded or have you now abandoned the approach?

[Lhoj]

Hey Colin. Yes, I've gone back to the preview view (the report view doesn't show the report as I want it to) but finally resorted to .minimize and .restore methods for the reports because it's the only way my modal form doesn't seem to lock up. However I've noticed that if you do that with the desktop clean it works alright, but if you have any other window open the report opens behind it, making it invisible if there's a big enough or a maximized window on the desktop (check it with the "open report view" option of your demo). Alt tabbing or clicking the taskbar brings access back on top but restores the minimized form which spawns on top of the open report. I've fixed that with the 'SetForegroundWindow' function from the user32 lib, just calling it to bring access window to the front just after opening the report, which seems to bring access to the front without restoring the form. Just letting you know in case you wanna check it and search a cleaner workaround for that.