Cascading combo box on subform (1 Viewer)

[ctieppo]

I just joined this forum about a week ago and I've had great luck finding examples of what I need in existing threads. This is my first post, and I'm pretty frustrated, so please be gentle...

I have read several of the other threads on cascading combo boxes and they've been very helpful. I'm really close to getting mine to work, just one problem left.

I have a form (frmProjectEntry) with a subform (subfrmTask Create). The subform has two combo boxes which cascade correctly when I open only the subform from the database window. When I open the main form, I get one of those pop ups asking for a field value on my "forms!subfrmTask Create!cbotasksubtype" field (the child combo in the cascade).

I'm sure I'm probably missing something obvious but I just can't see it. I've tried to apply all of the suggestions I've found in other threads:
- My main form, subform and combo boxes are all based on queries
- parent combo (tasktype) has requery of child combo (tasksubtype) on its afterupdate event; subform and main form both have requery of child combo in oncurrent event
- double checked column counts on each combo box - parent has two columns for tasktypeid and tasktype, child has three columns for subtasktypeid, tasksubtype, tasktypeid(FK)
- changed my subform from a datasheet or continuous form to a single form

Here's some of the code:
main form code:
Private Sub Form_Current()
Me.frmTask_Create_Subform.Requery
End Sub

subform code:
Private Sub Actual_Date_AfterUpdate()
If IsNull(Me!ActualDate) Then
Me!TaskCompleted = 0
Else
Me!TaskCompleted = -1
End If
End Sub


Private Sub cboTaskType_AfterUpdate()
Me.cboTaskSubType.Requery
End Sub

Private Sub Form_Current()
Me.cboTaskSubType.Requery
End Sub


Subform Combo boxes:

Tasktype (parent)
Row source
SELECT tblTaskType.TaskTypeID, tblTaskType.TaskType
FROM tblTaskType
ORDER BY tblTaskType.TaskType;


Tasksubtype (child)
Row source
SELECT [tblTaskSubType].[TaskSubTypeID], [tblTaskSubType].[TaskTypeID], [tblTaskSubType].[TaskSubType]
FROM tblTaskSubType
WHERE ((([tblTaskSubType].[TaskTypeID])=[forms]![subfrmTask Create].[cbotasktype]))
ORDER BY [tblTaskSubType].[TaskSubType];



Can anyone see my problem? Thanks bunches!

 

[Pat Hartman]

Cascading combos don't actually work in a continuous form. I explained this at length this week. Look for a post by me on the topic.

The problem you are having is that the subform is not being properly referenced in the query. The syntax is:

Forms!MainFormName!SubformName.Form!ControlName

So, your query would look like -
WHERE ((([tblTaskSubType].[TaskTypeID])=[forms]![your main form name]![subfrmTask Create].Form![cbotasktype]))

 

[ctieppo]

thanks for the help

I did change the subform to a single form when I read one of your other posts. I'll try your code, I bet it will do the trick. Thanks a lot.

 

[ctieppo]

still have same problem

Pat:
I tried updating the criteria on my child combo box's query, as you suggested:
SELECT [tblTaskSubType].[TaskSubTypeID], [tblTaskSubType].[TaskTypeID], [tblTaskSubType].[TaskSubType]
FROM tblTaskSubType
WHERE ((([tblTaskSubType].[TaskTypeID])=[forms]![frmProjectEntry]![subfrmTask Create].Form!cbotasktype))
ORDER BY [tblTaskSubType].[TaskSubType];

I'm now getting the popup looking for a value on the field forms!frmprojectentry!subfrmtaskcreate on both the main form and the subform. With my old code, I only got the popup when I opened the main form - the subform worked fine if I opened it alone.

I wasn't sure which query you were referring to in your post. You were talking about the child combo box query, right? If not, please let me know.

Thanks for your patience. This is my first attempt at cascading combos.

 

[Pat Hartman]

The names still are not right. The query for the second combo must refer to the form field that holds the first combo.

When your reference "passes through" a subform, the name of the subform is NOT the name you see in the Forms tab of the database window, it is the "Name" property of the subform control. Sometimes they are different. Find the Name of the subform control. It is on the Other tab.

 

[ctieppo]

Success!

I found the correct name for the subform and it works now! Thanks so much for your guidance.

 

[jgrayson]

First time here - amazed by the information on this site!

If you can't do multiple cascading combo boxing in a subform, is there a way around this? Is there another way you can achieve the same type of thing?

 

[ctieppo]

subforms can work

You can have cascading combo boxes on a subform, you just can't use a datasheet or continuous forms kind of subform (must be a single form).

My database has only two combos - one parent and one child. Are you trying to have one parent with multiple children? Seems like that should work, too. Check some of the other postings on the forum regarding cascade combos, they were extremely helpful to me.

 

[Pat Hartman]

Take a look at this "brilliant" solution to the problem of Cascading Combos on continuous forms.