Cascading ComboBox Subform Error.

[Lister]

Cascading ComboBox Subform Error.

Right I am stuck, and I hope someone out there can help.
I have a main form and a sub form. The subform is a form.

On this sub form, when I put the form together before linking/inserting onto the main form, I set up your classic Cascading Combo Box, select the Branch in the first Box and the lower combo box lists all the persons whom work there. Click Select No Problems at all.

Now when I linked/inserted this form into the main for as the subform and run the Main form. Everything works just as it should, EXCEPT I GET AN ERROR AND THE LOWER COMBO FAILS.

Thinking that it was my code I mucked around and around but got nowhere.

I knocked up a sample to see if it was me or something else and it’s got the same problem.

I have posted a screen shot of the error that I am getting.

I just don’t understand it works fine if you enter the correct value into the input box, and it works fine if you view just the subform, but as a subform, it falls over.

I am thinking that it’s the…

Private Sub cboBatch_AfterUpdate()
On Error GoTo Err_ErrorHandler

Me.Combo32.Requery

Exit_ErrorHandler:
Exit Sub

Err_ErrorHandler:
    MsgBox Err.Discription, vbExclamation, "Error #" & Err.Number
    Resume Exit_ErrorHandler


End Sub

The SQL for the first box is...

SELECT [tblParent].[ParentId], [tblParent].[Partent] 
FROM tblParent ORDER BY [tblParent].[Partent];

And the second..

SELECT [tblChild].[ChildID], [tblChild].[Child], [tblChild].[ParentID] 
FROM tblChild 
WHERE [tblChild].[ParentID]=[Forms]![frmExample]![cboParent] ORDER BY [tblChild].[Child];

But I’m quite at a loss. I have the awful feeling that its going to be something stupidly simple.
If anyone could help out that would be great.

Thanks

 

[WayneRyan]

Hi Lister,

The following works, but Mile posted the "proper" syntax in his FAQ on
how to reference subform objects.

SELECT [tblChild].[ChildID], 
       [tblChild].[Child], 
       [tblChild].[ParentID] 
FROM   tblChild 
WHERE  [tblChild].[ParentID] = [Forms]![frmWithTheProblem]![frmExample]!cboParent 
ORDER BY [tblChild].[Child];

Wayne

 

[Jhitch]

You need to refer specifically to the subform control on your mainform in your sql ie =[forms]![frmexample]![Mainchildsub].[Form]![cboParent]. Mainchildsub is the name of the control on your mainform.

Sometimes you can get away without the .[Form] bit as well.

 

[WayneRyan]

Lister,

J's right.

From Mile's thread:

Me.SubformA.Form!SubformB.Form!ControlName

http://www.access-programmers.co.uk/forums/showthread.php?t=66115

Wayne

 

[Lister]

Idiot <=

Knew it would be something simple

Thanks Wayne and Hitch for seeing the forest where I only saw tree's

Larger on me if you are ever down this way.

Thanks