Why does subform behave differently with different load methods?

Two form load methods:

1) Open the main form from the Project Window.

2) Open the main form from an AutoExec macro - the main form containing the subform is loaded using DoCmd.OpenForm method and setting Form_frmMyMainForm.Visible = True.

When the main form has been opened from the Project window I can set the RecordSource property of the subform using the following:

   Form_sfrMySubForm.RecordSource = "SELECT ..."

and it works. If I execute the same line of code when the main form has been opened with the AutoExec macro the above line does not work. However this line does:

   Form_frmMyMainForm.sfrMySubForm.Form.RecordSource = "SELECT ..."

Why does opening the main form from the Project window allow me to implicitly reference the subform while if I load the main form myself I can't? In my quest for answers I compared the properties of the unqualified subform object after loading and found these differences (the first value listed is from the load via the project window, the second value from the AutoExec method):

PageLogicalWidth = 0|9360
Parent = Valid Object|Nothing
Printer = Nothing|Valid Object
Visible = True|False

What I am really after here is the correct method to use to load a form. In other words, when I load a form from VBA I should be able to produce the same results as when loaded via the Project window, right? Or am I placing too much faith on Access always doing the right thing?

Kevin
LVL 81
zorvek (Kevin Jones)ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

harfangCommented:
When you use a class module name, VB will automatically create an instance of the class object for you. Since the module is packaged with a form, the form is also created in the Access window. However, it will not be included in the Forms() collection.
(From Access' point of view, there is code "behind the form", for VB, the form is just an additional object predeclared together with all the controls and fields on it...)

Now I know that opening a form from the project window and using DoCmd.OpenForm is exactly equivalent. Both create an instance of the form from Access' point of view, included in Forms().
I suspect that the culprit is your line:
    Form_frmMyMainForm.Visible = True
If you try this alone, without opening the form first, you will see that a new form is created. Using this method, you can in fact create several instances of the same form (With New Form_frmAny : .Visible = True : End With)

Try to remove that line. It could be that the synchronization is bad and that you have in fact created two instances of the form. When you refer to the class module of your subform, you simply do not refer to the same object. Normally, DoCmd will not let you create a second instance of a form. Your method might.

Basically, when you refer to Form_sfrMySubForm, you create a new instance, still invisible (you can set it's Visible property to true as well), as form, not as subform.

By the way, why do you need to make the main form visible? DoCmd does not create invisible forms...

> "What I am really after here is the correct method to use to load a form."
Do not use class module references to manipulate your forms. This is like yelling "Mommy" in the play ground. If there is only one mother present, it works. If there is several, it might work. If you want the same behavior as the project window, use only DoCmd and refer to your forms through the Forms() collection.

Hope this helps
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GreymanMSCCommented:
The line:
   Form_sfrMySubForm.RecordSource = "SELECT ..."

... will open a new and invisible instance of sfrMySubForm (if one is not already in the Forms collection, otherwise it will use that), then assign the recordsource.  The instance will be opened as a form, rather than a subform.  It's equivalent to doing the following:

  Dim sF as Form_sfrMySubForm, F as Form, Found as Boolean
  For Each F in Forms
    If F.Name = "sfrMySubForm" Then
       Found=True
       Exit For
    End If
  Next
  If Not Found Then
    DoCmd.OpenForm "sfrMySubForm"
    Set sF = Forms("sfrMySubForm")
    sF.Visible = False
  Else
    Set sF = Forms("sfrMySubForm")
  End If
  sF.RecordSource = "Select ...."
  Set sF = Nothing
-----

The line:
  Form_frmMyMainForm.sfrMySubForm.Form.RecordSource = "SELECT ..."

... will open a new, invisible instance of frmMyMainForm or, if one is already open, will use that.  It will then set the recordsource of the form contained by the sfrMySubForm child object.  It is Equivalent to doing:

  Dim mF as Form_ frmMyMainForm, sF as Form_sfrMySubForm, F as Form, Found as Boolean, C as SubForm
  For Each F in Forms
    If F.Name = "frmMyMainForm" Then
       Found=True
       Exit For
    End If
  Next
  If Not Found Then
    DoCmd.OpenForm "frmMyMainForm"
    Set mF = Forms("frmMyMainForm")
    mF.Visible = False
  Else
    Set mF = Forms("frmMyMainForm")
  End If
  Set C = mF.Controls("sfrMySubForm")
  Set sF = C!Form
  sF.RecordSource = "Select ...."
  Set sF=Nothing
  Set C=Nothing
  Set mF = Nothing

Which is quite different (but what you seem to wish to do).
0
harfangCommented:
This is not entirely true. For example, grab a copy of Northwind and open the form Orders. Now type in the immediate window:

    [Form_Orders Subform].Recordset.MoveLast

You will notice that the subform's current record does move, so it is not a new instance, although it is not in the Forms() collection.
Now open the subform alone, while keeping the form Orders open. Try the same command, and observe that the subform is still referenced, although there is now a valid entry in Forms(), so this is different from:

    Forms![Form_Orders Subform].Recordset.MoveLast

Now close Orders and observe that both syntaxes now move the record on the only remaining instance...

The point is that Access has no control on instanciation of objects, even form's class modules. The VB engine decides which object to return. I suspect is will simply find the first matching instance in some internal memory management table, or create the instance if needed.

Cheers!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

zorvek (Kevin Jones)ConsultantAuthor Commented:
Alrighty then...before a fight starts I'll try to narrow the scope a little. I've noticed that you guys over here in Access are a little more testy than the folks in the Excel department. Or maybe they're just on more medication.

I really would like to understand how Access/VBA instantiates and binds BUT, first, I want to know the proper way to load a form and reference a form and subform. I see some hints above but I would like to see some code snippets and discussion of what the Access experts see as best practices.

The reason I prefer the dot approach is it allows context sensitive auto-complete menus - usually. Using the bang (exclamation mark) does not allow that. That is why I migrated to the approach documented in my question. However, if that will get me into more trouble than it is worth then I will change my habits. I'm coming at this from a strong Excel/VBA/VB perspective.

Educate me...please.

I have increased the points to 500 to cover the additional effort.

Kevin
0
harfangCommented:
Well, as I said above, the "normal" way is to have Access start the process, not VB. To open a form:

    DoCmd "frmNameOfForm"

There are several additional arguments of which the most used are:

    WhereCondition:= <filter>

The filter is the WHERE clause of an SQL sentence, whithout the word WHERE. For example "bytCountryCode = 3" or "strCurrency = 'CHF'". This opens the form and places the string as filter of the form (Form.Filter = <filter> and Form.FilterOn = True). Also, there is:

    WindowMode:=acDialog

This is important, as VBA comes without user forms or dialog boxes. When you create a form in Access, it has default controls for record management (navigation buttons, record selector, scroll bars, resizable border), but you can turn an Access form into a dialog box. The WindowMode argument does three things: it modifies the appearance of the window and makes it modal; it trapps the PageUp and PageDown keys (so the user cannot navigate a multi-page dialog box manually); and it suspends code execution until the form is closed.

Now to reference a form, there is only one way: use the Forms collection. As DoCmd.OpenForm does not return a form variable, you very often have to create a reference to a form just after opening it. The standard methods for working with collections are available:

    Forms!frmFromName
    Forms("frmFormName")
    Forms(3)

To reference a subform, you need to know not the name of the form, but the name of the subform control that contains it. For example, removing a filter:

    Forms!frmFormName!subSubControl.Form.FilterOn = False

To close a form, use DoCmd.Close. Take the habit to always provide for the arguments, as the same instruction can close a report or even the database window. This is especially important when closing modal forms:

    DoCmd.Close acForm, Me.Name    ' run from the form itself
    DoCmd.Close adForm, "frmFormName"

(You will notice the "Me" self-reference, a kind of "ThisForm", excel style. This reference is stricktly VB, Access and VB both accept Form as self reference as well. You can use = Form.Name in a text box, for instance...)

You can also use the module name approach (only for forms with a module, mind you), but this is usually reserved for special cases. If you just want the auto-complete feature when programming for another form, use this:

    Dim frmOther As Form_frmFormName
    Set frmOther = Forms("frmformName")
    frmOther.cbo [list of combo boxes appear...]

(You can also just throw a "With Form_frmOther" before programming and remove is later....)


I hope I made things a little clearer.

BTW, great job over in the Excel channel. Have been surfing a bit and you seem very active :)
0
GreymanMSCCommented:
For the standard approach you'll need DoCmd.OpenForm, not just DoCmd.  Thats:
    DoCmd.OpenForm "frmMyMainForm"
or:
    DoCmd.OpenForm FormName:="frmMyMainForm"
0
zorvek (Kevin Jones)ConsultantAuthor Commented:
Gentlemen,

Sorry it took so long to close this question...no real clear answer but a lot of great information that led me down a path I needed to go. I developed some reasonably good code to handle the forms which got me through the project unscathed. Thank you for your very detailed remarks.

Kevin
0
harfangCommented:
you are welcome. it is an interesting topic ;)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.