keep a form at front but still be able to use other forms

[Jaye7]

I am after a script to lock a userform to the front but still be able to work on other forms.

I have an excel userform in access which does this and better still it is like a sticky note as it stays on the screen even when going to another program, but as I am working in vba all day long, every time I change something the form closes, it works great when not using vba though.

 

[Rx_]

If I understand this, the issue appears when the Excel User Form is being activated by Access VBA? It works fine stand-alone running under Excel.

http://msdn.microsoft.com/en-us/library/aa232711(v=vs.60).aspx
This article won't solve your problem, but it covers MDI.
Access, unlike VB6 or other applicaitons is one Application object with MDI (child objects).

My guess is that the Excel Application object variable is going out of scope during some MDI event such as a sub form closing.
It is possile to keep the Excel Application Object variable in scope during the session.
The term for this is Remote Automation.

You will have to give us some more information about how the Excel Object is created and managed from Access. The Scope of the Variable, the method used to open Excel and how the Excel Object is managed from its creation to its destruction.

 

[Jaye7]

Thanks for replying, the excel part was just giving an example of what I am currently doing, what I actually want to do is to keep an access form locked to the front but still be able to use other access forms.

 

[Jaye7]

Anyone else got a suggestion on how to keep an access userform locked to the front while still being able to work on other pop up forms.

 

[gemma-the-husky]

make it small, so it does not obscure what's underneath!


eg, I maintain a small popup form which contains a record of all the open forms, so a user can select the one he actually wants (if he has a few open at the same time)

 

[Jaye7]

Thanks Gemma, but the question is how to lock an access userform to the front.

 

[gemma-the-husky]

set the popup property to "true"

but if its a big form it will obscure anything else. so it has to be relatively small, like a msgbox

 

[Jaye7]

It is set to true but so is every other form, so they pop up in front or that one.

 

[gemma-the-husky]

why would you want to try to set every form as a popup form?

 

[Jaye7]

Every form is set to popup as that is how my boss like's the forms, he likes the look of popups and they also cover the ribbon and taskbar without having to add in other scripts

 

[essaytee]

Every form is set to popup as that is how my boss like's the forms, he likes the look of popups and they also cover the ribbon and taskbar without having to add in other scripts

Set the form in question to 'Extra Popup' that should do it.

Steve.

ps. Tell your boss to .........................

 

[Jaye7]

What is extra popup, never heard of it? is that a joke?

 

[Jaye7]

I found the following API which I converted to suit Win7 but it doesn't lock the form, but it does lock the access window so that you can not use any other application including the vba editor, may come in handy sometime to prevent users from exiting access.

I put the OnTop (Application.hWndAccessApp) part in the form load event but the form is not locked to topmost.

Option Compare Database

Option Explicit
Private Declare PtrSafe Function SetWindowPos Lib "user32" (ByVal hWnd As LongPtr, ByVal hWndInsertAfter As LongPtr, ByVal x As LongPtr, ByVal y As LongPtr, ByVal cx As LongPtr, ByVal cy As LongPtr, ByVal wFlags As LongPtr) As LongPtr
 
' Topmost window
Private Const HWND_TOP = 0
Private Const HWND_TOPMOST = -1
Private Const HWND_NOTOPMOST = -2
 
Private Const SWP_NOMOVE = &H2
Private Const SWP_NOSIZE = &H1
 
Public Sub OnTop(ByVal lhWnd As LongPtr)
  SetWindowPos lhWnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE
End Sub
Public Sub NotOnTop(ByVal lhWnd As LongPtr)
  SetWindowPos lhWnd, HWND_NOTOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE
End Sub

Private Sub Command0_Click()
OnTop (Application.hWndAccessApp)
End Sub
 
Private Sub Command1_Click()
NotOnTop Application.hWndAccessApp
End Sub

 

[essaytee]

What is extra popup, never heard of it? is that a joke?

Yes, was my attempt at humour. Your boss needs a speaking to (I think I put that nicely).

I would have thought that by default most forms (windows) should be normal non modal, non popup, types. If all are popups then what's the point (question for your boss)?

 

[gemma-the-husky]

since the point is that a popup form is always on top (but not like a modal form such as a msgbox which gets the focus and must be closed) - it's hard to see any benefit from a system where every form is popup.

I assume it either works like a normal system where no form is a popup, or it works strangely.

 

[Jaye7]

I found the answer finally, it locks the form to the screen whether you are in access or not.

http://support.microsoft.com/kb/184297

In the form open event put

Private Sub Form_Open(Cancel As Integer)
  Dim lR As Long
           lR = SetTopMostWindow(Forms!Form5.hwnd, True) ‘change Form5 to your access form
  End Sub

In a module put.

Option Explicit
        Public Const SWP_NOMOVE = 2
        Public Const SWP_NOSIZE = 1
        Public Const FLAGS = SWP_NOMOVE Or SWP_NOSIZE
        Public Const HWND_TOPMOST = -1
        Public Const HWND_NOTOPMOST = -2
   
        Declare Function SetWindowPos Lib "user32" Alias "SetWindowPos"  _
              (ByVal hwnd As Long, _
              ByVal hWndInsertAfter As Long, _
              ByVal x As Long, _
              ByVal y As Long, _
              ByVal cx As Long, _
              ByVal cy As Long, _
              ByVal wFlags As Long  ) As Long
   
        Public Function SetTopMostWindow(hwnd As Long, Topmost As Boolean) _
           As Long
   
           If Topmost = True Then 'Make the window topmost
              SetTopMostWindow = SetWindowPos(hwnd, HWND_TOPMOST, 0, 0, 0, _
                 0, FLAGS)
           Else
              SetTopMostWindow = SetWindowPos(hwnd, HWND_NOTOPMOST, 0, 0, _
                 0, 0,FLAGS)
              SetTopMostWindow = False
           End If
        End Function

 

[essaytee]

Oh, so you wanted a system wide window (Access Form) to be on top, on top of other programs that may be running. I didn't pick up on that. I thought you were only referring to Access forms.