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
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.RecordSo
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.sfrMySu
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
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
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.RecordSo
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.sfrMySu
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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!subSubCo
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 :)
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!subSubCo
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 :)
For the standard approach you'll need DoCmd.OpenForm, not just DoCmd. Thats:
DoCmd.OpenForm "frmMyMainForm"
or:
DoCmd.OpenForm FormName:="frmMyMainForm"
DoCmd.OpenForm "frmMyMainForm"
or:
DoCmd.OpenForm FormName:="frmMyMainForm"
ASKER
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
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
you are welcome. it is an interesting topic ;)
[Form_Orders Subform].Recordset.MoveLas
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.MoveLas
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!