0

I have an entry form with a sub. I have three combo boxes that work together. combo 1 sets up with vba and an after event combo 2 and on into three. They work fine independently of the main form, but when I sue the form as a sub the cascade stops working.

Forms

Main Form Name "1A-Event Entry"
SubForm Name "1B-Event sub"

ComboBox 1 (After Update)

Private Sub Category_AfterUpdate()
Me.Type = Null
Me.Type.Requery
Me.Type = Me.Type.ItemData(0)
End Sub

ComboBox 2 (SQL)

SELECT Type.ID, Type.Type, Type.Category
FROM Type
WHERE (((Type.Category)=[Forms]![1B-Event sub]![Category]))
ORDER BY Type.Type;

CombBox2 (After Update)

Private Sub Type_AfterUpdate()
Me.Detail1 = Null
Me.Detail1.Requery
Me.Detail1 = Me.Detail1.ItemData(0)
End Sub

ComboBox3 (SQL)

SELECT Detail.ID, Detail.Detail, Detail.Type
FROM Detail
WHERE (((Detail.Type)=[Forms]![1B-Event sub]![Type]))
ORDER BY Detail.Detail;

I am sure is has something to do with the Form/ Subform scripting in the SQL, but it escapes me.

Desert Spider
  • 744
  • 1
  • 13
  • 31

1 Answers1

1

As a subform, you need a different reference:

WHERE Type.Category=[Forms]![1A-Event Entry]![Subform Control Name Here].Form![Category]

When a form is run as a subform of another form, you must refer to the subform control.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • So Remou, the main takeaway would be for any combo reference the form name needs to be the primary form and not the sub form? – Desert Spider Dec 31 '12 at 23:46
  • 1
    I did not quite catch that. If you refer to the main form, you can just say `Forms!Formname.Control`, if you refer to a subform, you must use additional properties, so: `Forms!Formname.Control^^.Form.Control` where `control^^` is a subform control. – Fionnuala Dec 31 '12 at 23:51