?
Solved

Why does subform behave differently with different load methods?

Posted on 2004-11-22
8
Medium Priority
?
2,901 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:zorvek (Kevin Jones)
  • 4
  • 2
  • 2
8 Comments
 
LVL 58

Accepted Solution

by:
harfang earned 1000 total points
ID: 12651437
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
 
LVL 16

Assisted Solution

by:GreymanMSC
GreymanMSC earned 1000 total points
ID: 12651521
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
 
LVL 58

Expert Comment

by:harfang
ID: 12651812
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 12651984
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
 
LVL 58

Expert Comment

by:harfang
ID: 12652428
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
 
LVL 16

Expert Comment

by:GreymanMSC
ID: 12661609
For the standard approach you'll need DoCmd.OpenForm, not just DoCmd.  Thats:
    DoCmd.OpenForm "frmMyMainForm"
or:
    DoCmd.OpenForm FormName:="frmMyMainForm"
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 14050828
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
 
LVL 58

Expert Comment

by:harfang
ID: 14086005
you are welcome. it is an interesting topic ;)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question