Subform sourceobject can't assign 1

[wendyp]

I'm stuck. I have a product table and in it, a field called product_form. Each product has its own form to be displayed as a subform on a main order form.

I want to dynamically assign the subform at load time for the product selected.

In the Form Load event I have:

form_name = DLookup("product_form", "tListProds", "id = " & Me.product_id)

' ** This returns a value like: "fProdDoors" or "fProdGates"

I also have the following in the Form Load event:

Debug.Print "The source is: " & Forms("fOrderProductMain").Form.Controls("ProductSubForm").SourceObject
Debug.Print "The short source is: " & Me!ProductSubForm.SourceObject

' ** I put in the debug statements to make sure I was referencing the control the correct way. Both show the default sourceobject of fProdDoors. I've tried removing the default sourceobject, but then the references give me an error that the control can't be found (and I didn't delete the subform container - just the text from the sourceobject property)

I've tried
Me!ProductSubForm.SourceObject = form_name
'** this gives a Runtime 2101 - the setting isn't valid for this property

And
Me!ProductSubForm.SourceObject = Eval(form_name)
'** this gives a Runtime 2482 - Can't find the name "fProdDoors"

Shouldn't I be able to dynamically assign the SourceObject at load time? And use a variable to hold the name of the SourceObject? Is it thinking that I'm trying to feed it a query name?

 

[Zion7]

If "fProdDoors", is truly, a name of a subform,
then maybe try,
Me!ProductSubForm.Form.SourceObject =...

Possibility 1,
you may need to re-assign the Master & child links?
Possibilty 2,
Subforms ALWAYS load BEFORE main forms.
Try OnOpen event of main form, not OnLoad?


?...good luck!

 

[BuilderSpec]

Hi

Just tried your code and all seemed to work ok . I created a subform and had it changing it's source on form load, form open , a button etc and all i did was..

Me.Child64.SourceObject = "Products"

even

Dim nf As String

nf = "orders"
Me.Child64.SourceObject = nf

works ok..

I did misspell the form once and that gave me the runtime 2101 error . Check the spelling of the form etc..

The simple answer is yes, you should be able to do what you are doing..i suspect the answer lies in your database and could be a simple typo etc.




Hope this helps!

Regards

BuilderSpec

 

[wendyp]

I ended up have to delete the master / child links from the form properties. Then I reassign them with code.

Here's the code that worked:

    'Find the right form for this product
    Dim frmName As Variant
    frmName = DLookup("product_form", "tListProds", "id = " & Me.product_id)
    frmName = IIf(IsNull(frmName), "fProdBlank", frmName)
    
    Dim tblName As Variant
    tblName = DLookup("product_table", "tListProds", "id = " & Me.product_id)
    
    Me.ProductForm.SourceObject = frmName
    
    If Not IsNull(tblName) Then
    
        Me.ProductForm.LinkChildFields = tblName & ".order_item_id"
        Me.ProductForm.LinkMasterFields = "tOrderItems.id"
        
    End If

Thanks for the ideas. Zion7 a star for getting me to look at the links.
/Wendy