Access Form On Top Of UserForm

[patriciaxxx]

I Have an Access Database and from it I open a VBA UserForm as modal (which I need), from this UserForm I click a button which opens an Access form as PopUp, Modal, Dialog which opens on Top of the UserForm (which I need and so far sounds OK)

The problem is the Access form doesn't seem to stay on top of the UserForm even though it is opened as PopUp, Modal, Dialog whenever you touch the UserForm behind the Access form the UserForm comes to the top and the Access form is lost behind it.

Whatever I do I can't seem to get the Access form to stay on top (if I use two UserForms or two Access forms they behave correctly)

Any help to solve this would be much appreciated.

 

[missinglinq]

UserForms are intended to be used primarily with Excel, and to a lesser extent, with Word, I believe, which begs the question...why do you insist on using them with Access, instead of using standard Access Forms? This is not the first problem you've posted because of mixing the two.

Hope this helps!

There's always more than one way to skin a cat!

All posts/responses based on Access 2003/2007

 

[strongm]

Ask yourself the following question: if I have two application[sup]1[/sup] modal windows (no matter what the source) which one should be on top?

Now, generally this scenario does not arise, since an applciation can only show one modal form at a time - but Access is a slight ooddity as it has two seperate form engines, each of which can have a modal (to the form engine) window showing.

So, back to the question. The answer is that an application modal window that has the focus should be on top. Windows cannot magically know that you want a different modal window on top.

But obviously you can cheat - by telling Windows which modal window you actually want to have the focus (and thus be on top). here's a simple example that involves a single Access form set as modal popup and a VBA form with two command buttons, also set as modal. The code given here goes into the UserForm:

[blue]Option Compare Database
Option Explicit

Private OtherModal As Boolean

Private Sub CommandButton1_Click()
    OtherModal = True
    DoCmd.OpenForm "Form1"
End Sub

Private Sub CommandButton2_Click()
    OtherModal = False
End Sub

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
   If OtherModal Then Form_Form1.SetFocus
End Sub[/blue]

Note this just illustrates the idea, and is far from a perfect solution.

[sup]1[/sup]Windows supports two types of modal window: system modal and application modal. Both Access forms and VBA userforms are examples of application modal windows (actually there is a third type, task modal, but let's not complicate things)

 

[MajP]

What is the need of both Access and User Forms? I can think of 2
1. Have a few really complicated user forms and do not want to spend the resources to convert them
2. Have a whole lot of user forms and do not want to spend the resources to convert them

If not 1 or 2, I would think the amount of time to convert most forms from a User Form to an Access form is less than the time to post the thread and explore kludged work arounds.

3. There is a functionality that you can only get from a User Form.
I did not include 3 because I cannot think of anything I could do with a user form that I cannot do with an Access form. If 3, then please explain because maybe it can be done in Access.

 

[combo]

Access seems to be lazy, I've had a case when imported userform could be displayed directly ("run" button in VBE), but was not visible by the code.

combo