How to Open a Form Synchronously

[Mihail]

Hope this is the window for question.

My question:
I have two forms (say FORM_1 and Form_2)
As you know (more of you), in Excel, I can open FORM_2 from FORM_1 using code (in FORM_1):

FORM_2.Show()
If I use vbModal mode (FORM_2.Show(vbModal) the next line in FORM_1 code will not be executed until FORM_2 is closed.

The question is: Can I obtain the same effect in Access VBA ? I wish to avoid to continue code execution in FORM_1 before I close FORM_2.

I use 2007 version of MS Office.

Thank you in advance !

[NeoPa]

Forms in Access also have a property .Modal which allows them to take full control.

[Mihail]

Thank you for your reply, NeoPa.
Unlike in Excel VBA, the .Modal in Access only stop the form to lost focus.

Private sub Open_FORM_2
     On Error .......
     .............
     DoCmd OpenForm FORM_2 ....
     First line AFTER DoCmd
     ............
End sub

I do not wish that first line after DoCmd to be executed while FORM_2 is open. Unfortunately this happened.
Maybe somewhere is a setting ?!?!?

Thank you again for the reply.

[Mihail]

I find the "solution":

DoCmd.OpenForm stDocName, , , , , acDialog

But work only one level.

If I have n forms FORM_1, FORM_2, ... FORM_k ... FORM_n and, in each form, this code:
-------------------------------------------

Me.Visible = FALSE
   DoCmd.OpenForm FORM_k+1, , , , , acDialog
Me.Visible = TRUE

-------------------------------------------
where k is the current form (k = 1 ... n-1)

then the instruction "Me.Visible = TRUE" in FORM_k is not executed until I close FORM_k+1. That's OK. But, once I close the "k+1" form, the instruction

    "Me.Visible = TRUE"

will be executed for all k, k-1, k-2, ... 1 forms.

Why ? How stop this cascade ? Is it possible ?

Thank you in advance.

[TheSmileyCoder]

Now I have not tried your exact setup, but just want to add that IF the form K+1 is allready open in design mode, it can be forced open by DoCmd.OpenForm, however the Dialog/modal property does not always take in such a case. So when your testing, ensure that all your forms are closed. (Its good practise anyway to ensure all forms are closed, before testing/modifying code)

[Mihail]

TheSmileyCoder, thank you for reply.
I do that. Sometimes I close the Access application before (re)test the code. It works (in both cases) as I say.
My last "solution" is:

Private Sub cmdDefinire_Click()
On Error GoTo Err_cmdDefinire_Click
 
        Me.Visible = False
    DoCmd.OpenForm "10_Localitati", , , , , , Me.Name
 
Exit_cmdDefinire_Click:
    Exit Sub
 
Err_cmdDefinire_Click:
    MsgBox Err.Description
    Resume Exit_cmdDefinire_Click
End Sub
 

This is in FORM_k in order to open FORM_k+1".


And, in FORM_k+1 I have:

Private Sub Form_Close()
Dim frm As Form
    For Each frm In Application.Forms
        If frm.Name = Me.OpenArgs Then
            frm.Visible = True
Exit sub
        End If
    Next frm
End Sub
 

It seems work OK. But is not what I wish.
I wish that run code in FORM_k AFTER I close FORM_k+1 (i e to refresh FORM_k informations). "Me.visible = TRUE" is MINIMUM what I need.

Now is necessary to manual refresh the FORM_k:

Private Sub cmdRefreshForm_Click()
On Error GoTo Err_cmdRefreshForm_Click
 
    DoCmd.RunCommand acCmdRefresh
 
Exit_cmdRefreshForm_Click:
    Exit Sub
 
Err_cmdRefreshForm_Click:
    MsgBox Err.Description
    Resume Exit_cmdRefreshForm_Click
 
End Sub

Thank you again for reply. Maybe we (all) can find a solution. I google the web and there are many other posts on this subject but no one can find a true solution.

[Mihail]

Update to previous post:
I wish to kill Bill Gates.

The code in previous post work fine until... the FORM_k+1 is resized or moved. After that the OpenArgs become... empty. Why ? Maybe Bill Gates know.

So, in order to use the OpenArgs in the CLOSE event is necessary to declare a variable at the module level:

Option Explicit
Option Compare Database
Dim MeOpenArgs
 
then:
 
Private Sub Form_Load()
    MeOpenArgs = Me.OpenArgs
End Sub
 
then:
 
Private Sub Form_Close()
Dim frm As Form
    For Each frm In Application.Forms
        If frm.Name = MeOpenArgs Then
            frm.Visible = True
Exit Sub
        End If
    Next frm
End Sub

[TheSmileyCoder]

Hi again

I have tried to create a form and open it while passing a openarg. I tried resizing it, moving it, requery it, and setting the visible False/True, but I was not able to "loose" the openArgs. So not sure why you are experiencing that. What access version are you using?

I also tried creating a form, and have it open a form, which opened a form.....until I had 5 open forms. Each form had a close button and when I clicked the close button, my next line of code (after the Docmd.Openform) would run as intended.

I have no idea why you are getting the results you are.

[Mihail]

I use MS Office 2007.
It is the first time when I write a "seriously" code for Access. But I write hundred lines for Excel and the keywords work like the help files say. I don't understand too what happen now. I reinstall Office but no results. It work the same.

I close my forms using CLOSE BUTTON from CONTROL BOX.
Where you place the code for close form ? In the form CLOSE event or under the OnClick event of your CLOSE BUTTON ?

I place the code under the FORM close event. Tomorrow I'll try to use an button to close my forms.

Thank you very much for reply.

[TheSmileyCoder]

Well I didn't really have any code in my Close event. I just made a button which closes the form, but I could as well have used the X of the form itself. I will see if my results are different in a Ac2007 version when I have the chance.

[Mihail]

Thank you !
My private address is ** Edit - email address removed in accordance with rules and for member protection **

You are much "older" than me here (on this forum) so you can decide better where you wish to help me. (Hope you understand what I wish to say because my english is very specialized. I learn from the computers help files).

Thank you again.

[NeoPa]

Just like Smiley, I find that what you're reporting is not what I see on my systems. I don't use Access 2007 I admit, but I'd be surprised if it worked so differently from all the other versions.

I understand that your English is not too strong. You're doing well in the circumstances (Much better than I could handle Romanian). Please note though, that you must use CODE tags when posting code. Already too many posts updated for you.

Let us know how you get on though. Clearly you are clever enough to progress with this, but it's strange that you have unexpected results from your tests.

[Mihail]

Hi all

No progress on this topic.
The code do not stop in the FORM_k+1.
More, if I open the form with acDialog option, the form loose the sizable property and my forms MUST BE sizable.

So, I use the last "solution" I post and I decide to not waste anymore my time in this direction.

Thank you very much for yours help.