Nested Subform's control events

[amrut]

Forms : Main------>SubForm1------>Subform2
Controls : Main ------->SupplierName
SubForm2-------->Product
The Product combo on SubForm2 has list of products based on SupplierName on MainForm. If the user does not select the supplier name, the Product combo will not have any data. I am trying to prompt the user to select Supplier in such case. The following code achieves the required result but ....

---

Private Sub Product_GotFocus()
If IsNull(Forms!Main!SupplierName) = True Then
MsgBox "Select  supplier name", vbInformation
Forms!Main.SupplierName.SetFocus
Forms!Main.SupplierName.Dropdown
End If
End Sub

---

Problem : If the user leaves the SupplierName blank and then clicks on any of the controls on SubForm1 or SubForm2, the above code continues to execute till SupplierName is selected.
How to prevent repeated execution of the above code except when the Product combo on SubForm2 has focus?

[June7]

That seems odd. The code should not trigger if other controls are clicked.

An alternative is to not make the subform available until SupplierName is selected.

[amrut]

June7, thanks for the suggestion. Your alternative will certainly sort out the problem.

This is from the MSDN http://msdn.microsoft.com/en-us/libr.../ff836988.aspx for the subform's exit event.

"If the control you move to on the subform previously had the focus, neither its Enter event nor its GotFocus event occurs, but the Enter event for the subform control does occur. If you move the focus from a control on a subform to a control on the main form, the Exit and LostFocus events for the control on the subform don't occur, just the Exit event for the subform control and the Enter and GotFocus events for the control on the main form."

I think when the user leaves SupplierCombo on mainform null and then clicks anywhere on the sub form1 or subform2 the last control that had focus was the product combo on subform2.Hence, the code executes repeatedly.

[June7]

I did a test of that scenario and yes, the combobox GotFocus event triggers when returning to the subform if the combobox held focus when clicking out of the subform. Would not have thought that but I guess does make a kind of sense. Learn something new every day.