How to Build a Combobox in a Subfrom

[Steve R.]

I was trying to build a combobox in a subform. The problem has been "solved", but I would still like to get some insight into what went wrong.

Code located in the Subform:

Call BuildState(me.name, Me.D6Field1001.Name)

The VBA Module Code:

Public Sub BuildState(strFormName As String, strControlName As String)
    Forms(strFormName).Controls(strControlName).Properties("RowSource") = ""
    Set BuildComboBoxRS = CurrentDb.OpenRecordset("SELECT * FROM " & csStateList & " ORDER by StateAbbreviation;", dbOpenDynaset, dbSeeChanges)
    BuildComboBoxRS.MoveFirst
    Forms(strFormName).Controls(strControlName).Properties("RowSourceType") = "Value List"
    Forms(strFormName).Controls(strControlName).Properties("ColumnHeads") = False
    strItem = "0;No Selection"
    Forms(strFormName).Controls(strControlName).AddItem Item:=strItem
    Do Until BuildComboBoxRS.EOF
        strItem = BuildComboBoxRS!StateIDnum & ";" & BuildComboBoxRS!StateAbbreviation
        Forms(strFormName).Controls(strControlName).AddItem Item:=strItem
        BuildComboBoxRS.MoveNext
        Loop
    BuildComboBoxRS.Close
    Set BuildComboBoxRS = Nothing
End Sub

The code above would work, when the subform was opened by itself. But it would NOT work when the mainform was opened. I would get an error message that the subform (strFormName) could not be found.

I have solved the problem by moving the VBA code into the subform itself and modifying it appropriately. Though the problem is solved by relocating the code, I still would like to know why it didn't work and (for future reference) how it could be made to work.

 

[jdraw]

A quick guess is that Me refers to the form you are on.
Have you tried explicitly identifying the subform

http://access.mvps.org/access/forms/frm0031.htm

 

[boblarson]

Subforms exist on a main form not as a form but as a CONTROL. So, to refer to a subform on a parent form you would need to use

Forms(ParentNameHere).Controls(subformNameHere).Form.Controls(controlName)

So you would need to build in an Optional strParent parameter in your function which can then be checked to see if there is a value. If so, use the extended version of the code and, if not, use what you have currently.

 

[Steve R.]

Humph??? Seems that the "SOLVED" check does not show-up for me on the thread tools. Anyway the issued is solved, but I am still open for any insight on this issue.

jdraw: Thanks. The code is running from the subform's load event. The error message correctly identified the name of the subform, but said that the subform could not be found. My suspicion is that the code is running before Access has actually had the opportunity to "register" the name of the subform as a valid object.

boblarson: I'll experiment some later. For now, I have to get back to my "real' work.
At least, it is working to give the correct results.

For the sake of others who may be looking, the working code version below is the modified one that I referred to which works in the subform. Essentially, the code was moved into the subform itself and syntax "Forms(strFormName).Controls(strControlName)" was replaced.

Public Sub BuildState1()
    Me.D6Field1001.Properties("RowSource") = ""
    Set BuildComboBoxRS = CurrentDb.OpenRecordset("SELECT * FROM " & csStateList & " ORDER by StateAbbreviation;", dbOpenDynaset, dbSeeChanges)
    BuildComboBoxRS.MoveFirst
    Me.D6Field1001.Properties("RowSourceType") = "Value List"
    Me.D6Field1001.Properties("ColumnHeads") = False
    strItem = "0;No Selection"
    Me.D6Field1001.AddItem Item:=strItem
    Do Until BuildComboBoxRS.EOF
        strItem = BuildComboBoxRS!StateIDnum & ";" & BuildComboBoxRS!StateAbbreviation
        Me.D6Field1001.AddItem Item:=strItem
        BuildComboBoxRS.MoveNext
        Loop
    BuildComboBoxRS.Close
    Set BuildComboBoxRS = Nothing
End Sub

 

[ChrisO]

Subforms are not opened, they are instantiated.

A Form which is opened is unique by name and so can go into the Forms collection. The Forms name must be unique because its name can be used as an index in that collection.

A Form which is instantiated does not have a unique name; there can be many instances of that Form with the same name. (Up to about 150 instances in A2003, from memory.) Because the names are not unique the name can not be used as an index in the Forms collection. Hence instantiated Forms are not in the Forms collection and trying to reference them in that collection will raise the error ~Form can’t be found~

Subforms are unique even though they may share a name with another Form. The reference must not be by Name but by Form or by Me. Note that neither Form nor Me use the Forms name.

It follows that a Subform can not be the Screen.ActiveForm because the Screen.ActiveForm has a Name property as in Screen.ActiveForm.Name and, being a Subform, its name may not be unique.

So the Forms collection is a collection of open Forms and they must have unique names.
Any other Form we can see has been instantiated and is not in the Forms collection because its name may not be unique.

Chris.

 

[Steve R.]

Thanks. I think it may take me a while to digest what you said. It seems to "match" the issues that I was having in that the form was not "instantiated".

I had tested with a form loaded function and it had reported that the subform was not loaded. The name of the subform is unique.

In the load event of the subform I had the code segment below, which referenced a subprogram in a module. It worked as expected when the subform was opened by itself, but not when the code ran from subform within the mainform. So I was guess that referencing the module occurred before the sub form was instantiated??????

Forms(strFormName).Controls(strControlName).Properties("RowSource") = ""

 

[spikepl]

No. Translating ChrisO's contribution into English: You cannot treat a subform as a form - it does not exist by itself. Your entire code is built on the assumption that the calling form is passed by name, and then that name is both findable and then found in the forms' collection. This is not so. If you wish to do stuff on a subform, you still need to pass it as a form, just like Chris O said.

Your current

Forms(strFormName).Controls(strControlName).....

Presumes your form strFormName is "findable" in the forms' collection. A subform isn't!

so your code should be :

Public Sub BuildState(MyForm As Form,...

the call would be

BuildState(Me,...

and the code would be

MyForm.Controls(strControlName).Properties("RowSource") = ""

 

[ChrisO]

>>Translating ChrisO's contribution into English:<<

Okay, so now I’ll have a go at some more non-English…

Let’s see if we can go through this one step at a time, it might explain things better.

>>It seems to "match" the issues that I was having in that the form was not "instantiated".<<

That is incorrect; the Subform was instantiated not opened.
Note that there is a difference between the words open and opened.
A Form which is opened is open and it exists in the Forms collection. The Forms collection defines which Forms are open. Forms which are instantiated are not open they simply exist and they are not in the Forms collection.


So, even though the Subform was not open it must have existed because code was running in its class module.

Call BuildState(me.name, Me.D6Field1001.Name)

But here you are passing Me.Name which is the name of a subform. That name may have been unique at the time but it can’t be guaranteed to be unique. Since the name can not be guaranteed to be unique it will not be in the Forms collection.

Public Sub BuildState(strFormName As String, strControlName As String)
Forms(strFormName).Controls(strControlName).Properties("RowSource") = ""

This tries to find strFormName in the Forms collection but it is not in that collection because strFormName is the name of a subform and the names of subforms can not be guaranteed to be unique. So the compiler could not find it in that collection simply because it wasn’t in that collection.

So the mistake which was made was trying to use the Name of a subform and not the pointer to that subform. Had you passed just Me, which is a unique pointer to the unique subform, and received it as a Form pointer then it would have worked. You would also have to drop the reference to the Forms collection and use something like:-

Public Sub BuildState(frm As Form, strControlName As String)
frm.Controls(strControlName).Properties("RowSource") = ""


And even that is too much code.
You do not need a reference to the subform at all, a reference to the Control would do.

Call BuildState(me.name, Me.D6Field1001.Name)
would become
Call BuildState(Me.D6Field1001)
or even
BuildState Me.D6Field1001
and
Public Sub BuildState(strFormName As String, strControlName As String)
would become
Public Sub BuildState(cbo As ComboBox)
and
Forms(strFormName).Controls(strControlName).Properties("RowSource") = ""
would become
With cbo
.RowSource = ""

So eventually you may get down to something like this untested code:-
(But you should turn Option Explicit On.)

Public Sub BuildState(cbo As ComboBox)

    With cbo
        .RowSource = ""
        .RowSourceType = "Value List"
        .ColumnHeads = False
        .AddItem "0;No Selection"
    End With
    
    With CurrentDb.OpenRecordset("SELECT * FROM " & csStateList & " ORDER by StateAbbreviation", 2)
        Do Until .EOF
            cbo.AddItem !StateIDnum & ";" & !StateAbbreviation
            .MoveNext
        Loop
    End With
    
End Sub

Now the reason why that would work when debugged:-
Me.D6Field1001 is now a Pointer to a Control not the Name of a Control.
Pointers are unique even on instantiated Forms.
Instantiated Forms are unique but their Names may not be.
Controls on Forms, opened or instantiated, are unique but their Names may not be.
Pointers to Forms and Controls, and any other Object, are unique.

The main reason for the original failure was not using the unique Pointer but instead trying to use a non-unique Name.

Chris.

 

[Steve R.]

You cannot treat a subform as a form - it does not exist by itself. Your entire code is built on the assumption that the calling form is passed by name, and then that name is both findable and then found in the forms' collection. This is not so. If you wish to do stuff on a subform, you still need to pass it as a form, just like Chris O said.

Thanks for the translation. Makes sense.

But here you are passing Me.Name which is the name of a subform. That name may have been unique at the time but it can’t be guaranteed to be unique. Since the name can not be guaranteed to be unique it will not be in the Forms collection. ...

So the mistake which was made was trying to use the Name of a subform and not the pointer to that subform. ....

The main reason for the original failure was not using the unique Pointer but instead trying to use a non-unique Name.
Chris.

Excellent write-up. Very educational and conforms to all the "symptons" that I was experiencing. Thanks.

 

[Steve R.]

ChrisO: Beautiful!!! Worked like a charm.
Revised and tested code below.

Code located in the load event of the subform.

Public Sub Form_Load()
    Call BuildState(D6Field1001)
End Sub

Code located in the (external) module

Public Sub BuildState(cbo As ComboBox)
    With cbo
        .RowSource = ""
        .RowSourceType = "Value List"
        .ColumnHeads = False
        .AddItem "0;No Selection"
    End With
    
    With CurrentDb.OpenRecordset("SELECT * FROM " & csStateList & " ORDER by StateAbbreviation;", dbOpenDynaset, dbSeeChanges)
        Do Until .EOF
            cbo.AddItem !StateIDnum & ";" & !StateAbbreviation
            .MoveNext
        Loop
    End With
End Sub

Thanks ChrisO and spikepl for your very educational posts.