What is syntax for sub subform's sourceobject?

[LisaOnMSN]

I have a main form that has a tabbed control on it. The tab control has a Feature tab that loads a generic feature form AND a subform specific to the particular feature type.

MAIN FORM frmParkWithTab
SUBFORM frmFeatureWithSubform
SUB-SUBFORM frmFeatureSubform

I use a table that contains 2 columns, the feature type and the form name, so that based on a particular feature type I can load the appropriate sub-sub form. Unfortunately I cannot for the life of me deliver the proper syntax to refer to the sourceobject of the sub-sub form. The plan was something like:

If( Me.featuretype <> "") Then
Forms!frmParkWithTab.frmFeatureWithSubform.frmFeatureSubform.SourceObject = strFormName

Where the strFormName is loaded from a lookup on the feature type table.
That part works just fine. What does NOT work is my incorrect syntax that gets me errors 458, 2101, and 2465 depending on how badly off I am in my guessing. Suggestions humbly apprecated.

Lisa

 

[pbaldy]

This should help:

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

 

[LisaOnMSN]

Thanks so much. I'll go read up.

Lisa

 

[pbaldy]

Happy to help Lisa.

 

[LisaOnMSN]

I reviewed the chart. It appears pretty straightforward. But despite the chart, I have somehow managed to get it to not work properly. At this point I gave up on using what I believed was the right syntax and simply used every iteration listed (whether I thought they reflected the scenario or not. Frustration has won), yet I cannot properly get that assignment statement to work:

Forms!frmParkWithTab!frmFeatureWithSubform.Form!frmFeatureSubForm.SourceObject = strFormName

Humbly request assistance.

 

[pbaldy]

That looks like the correct syntax (I assume the forum added the space, which it is known to do). What does the variable contain? Do you get an error, or ? Can you post the db?

 

[LisaOnMSN]

Thanks for your reply. the strFormName variable returns correct info -frmStructure or frmSystem etc depending on the feature. I get error 2101
(I'll attach a screen shot). My db exceeds max size (3.73mb). I can post the routine if that helps? I've changed the things so many times I can't recall what syntax is loaded in there at the moment:

Private Sub ChooseSubFormInfo()
' When this tab is selected you need to load up a default feature type
' sub form.
MsgBox ("ChooseSubFormInfo")
On Error GoTo ErrorHandler
Dim strFormName As String 'store the name of form to load as a subform based on the featuretype
' look up the appropriate subform name based on the feature type value named in the
' featuretypename control
If (Forms!frmParkWithTab.frmFeatureWithSubform.Form.featuretype.Value <> "") Then
strFormName = DLookup("[featuretypeform]", "[featuretype]", "[featuretypename] = " & "'" & Forms!frmParkWithTab.frmFeatureWithSubform.Form.featuretype.Value & "'")
Forms!frmParkWithTab.frmFeatureWithSubform.Form!frmFeatureSubForm.SourceObject = strFormName
Else
Forms!frmParkWithTab.frmFeatureWithSubform.Form!frmFeatureSubForm.SourceObject = "frmBlank"
End If
CleanUpAndExit:
Exit Sub
 
ErrorHandler:
MsgBox ("Error encountered in ChooseFeatureSubForm." & vbCrLf & _
vbCrLf & "Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description)
Resume CleanUpAndExit
End Sub

 

[Galaxiom]

Set a breakpoint to check if your DLookup is returning False which is what it does if there is no match. That would be an invalid value for the property.

Disable the Error Handler (by commenting out the On Error line) because the code will stop on the bad line rather than just showing the error message which might not apply to the line you are assuming.

BTW Please post you code in a codebox. Put [ code ] before it and [ /code ] after it (including brackets but without the spaces). You can edit you existing post. The code tags allow it to be indented and prevents the forum inserting that space. It also uses a font that makes single quotes easy to see.

Note also that if the code is located in the module belonging to the form frmParkWithTab then you can simply refer to it as Me.

Aternatively if you use the full reference there is no need to concatenate the name in a Domain function.

Also note that Value is the default propery of a control and can be omitted.

 

[LisaOnMSN]

Appreciate the info about how to format to display as a code module in a a posting (I didn't know how to do that). I added code to handle possible 'False' return from DLookup and switched back syntax to Me. style. Still getting error, but noticed that my blank default sub sub form (frmBlank) is loading up BEFORE this routine ever gets called, which means I have a problem further upstream - maybe at sub form Form Load/On Current. Will dig further and share if I suddenly have a breakthrough. Thanks again.

 

[boblarson]

Lisa:

One thing that may have escaped your attention is that the subform object that is referred to in the code needs to be the subform CONTROL (control on the parent form which houses the subform) name and not the subform name itself. Many times it ends up working fine because the subform control and subform may have the same exact name. However, in cases where they do not, you must use the control name instead of the subform name.

So, if I have:

Main form: frmMain
Subform : sfrmTest
Sub-Subform: sfrmOther


and I want to refer to the sub-subform's source object, I would use

Forms!frmMain.sfrmTest.Form.sfrmOther.Form.SourceObject = "frmSomething"

 

[LisaOnMSN]

Bob, Paul, thanks for all your help but problem remains (error 2101 or 2467 or....). From what I have seen posted on this and other forums, I'm not alone on having massive headaches trying to swap out sub-sub forms based on sourceobject. Not sure if something gets trashed when the object gets assigned or what have you. Short story: I'm going to write the longest if statement in history - which works just fine. Others have defaulted to loading a mess of subforms on the main then using visible/invisible but it is unwieldy doing that when you have over twenty subforms to swap. It seems like 2007 has a deep issue somewhere regarding nested subforms. But thanks for trying!

 

[boblarson]

So you can't post a copy of the database with bogus data and let us have a crack at it before you go and do a bunch of work that might not be needed? I've not had subform problems (in 2007 or any) and would like to see if I could figure out the problem.

 

[LisaOnMSN]

It's pretty huge. Let me try and squish it down to under the 2mg max and get it to you. I would LOVE to let an actual doctor check for the common cold. Give me an hour.

 

[boblarson]

Remember to run compact and repair on it first and then zip it (to find the final total size). And if it is too big you could email it to me but I won't be able to look at it very soon at all as I am pretty much booked up in the evenings the rest of the week. I could possibly put it on a flash drive and bring it to work with me (that is, if the size can't be made small enough).

 

[LisaOnMSN]

I think I took enough out to get it under the requirements...barely.

Thank you again for even considering looking at it. Very much appreciated.

Lisa

 

[boblarson]

I think I took enough out to get it under the requirements...barely.

You really wouldn't have had such a problem BECAUSE if you had paid attention to what I said:

Remember to run compact and repair on it first and then zip it

By running Compact and Repair (Big Round Office Button > MANAGE > COMPACT AND REPAIR) it took it down to 1,972 Kb and then zipping it (right click on the file and select SEND TO > COMPRESSED FOLDER) took it to 970 Kb. So you probably wasted a lot of time for nothing.

I'll be taking a look.

 

[boblarson]

Oh, and which form is the main form and what did you want where?

 

[LisaOnMSN]

Sorry: Read post, ran to mtg, tried to squish before next mtg, had db set to autocompress on close so thought was being done automatically.

MAIN form is "frmParkWithTab"
SUB form is "frmFeatureWithSubform"
SUB SUB form is "frmFeatureSubform"

Lisa

 

[LisaOnMSN]

Would a giant "never mind-I was SO very wrong" be sufficient? I referenced the wrong table in the DLookup: value I was looking for didn't exist in that table (strike one) and I didn't properly test for null (strike two) and since the value I was looking for was in a different table altogether, it required TWO Dlookups to actually find the appropriate form to load. When I did that it worked exactly as predicted in two lines of code. I'm sure I'll now find 87 additional errors. I'll go work on them now.

Deepest thanks for your patience!
Lisa

 

[ChrisO]

Lisa.

On Error GoTo ErrorHandler
Dim strFormName As String
strFormName = DLookup(Blah, Blah, Blah)

If the Dlookup function fails to find a value it will return Null.
A Null can not be asigned to a String.

When debugging code, the offending line of code in the above is the: -
On Error GoTo ErrorHandler

I would strongly recommend to people to turn off error handling during debugging.
Just let the code crash on the offending line. When asking for help also quote the line the code crashed on.

There are other ways to do it but the way I have settled for is as follows: -

Create a public constant: -
Public Const conHandleErrors As Boolean = False

Each error go to line, which does not require redirection for the procedure to work, can be written as: -
If (conHandleErrors) Then On Error GoTo ErrorHandler

When the smoke settles and all the gremlins have gone home, switch back to: -
Public Const conHandleErrors As Boolean = True


If you want a reminder you can place the following code in the first Form that’s opened: -
If Not (conHandleErrors) Then
MsgBox "Global error handling is off."
End If

You will save yourself and other people a lot of time trying to chase down errors.

Chris.