Subform Combo Box data disappears when lost focus

[Cameron.Turner]

Hi All,

Not sure there is a solution to this one, but I thought I'd ask anyway

I have a main form with a subform embedded. The parent/child links are all working perfectly. The data inserted into the subform is dependent on selection from a combo box on the main form. This also works perfectly.

One of the fields in the subform is also a combo box, but this one is dependent on selection of a another combo box within the subform. This also works.

However, when I navigate to a field on the main form, the entries in the subform combo box disappear. All entries in the subform except the last one disappear on lost focus. They are still there, saved to the table, but they are no longer showing.

It is only the data in the subform combo box that disappears. All the other data in the subform remains visible as you navigate back and forth between the main form and subform. So I am pretty sure the problem lies with the code I am using to create the combo box:

Private Sub ReasonID_Enter()

Dim sReasonSource As String

    sReasonSource = "SELECT [tblQAReason].[ReasonID], [tblQAReason].[Reason] " & _
        "FROM tblQAReason " & _
        "WHERE [CriteriaID] = " & Me.CriteriaID.Value
    Me.ReasonID.RowSource = sReasonSource
  
End Sub

When you enter the combo box field, I call the above code to limit the drop down options to where the criteria matches the current criteria.

Is there anything I can do to stop the data disappearing from the combo box when I navigate to the main form?

 

[John Big Booty]

Using fields that derive their value from another control on a continuous form are always going to be problematic. As ALL instances of the dependant control will reflect the value held in the record that currently hold focus.

So in the case of Cascading Combo Boxes, only those boxes whose Row Source aligns with the selection in the master combo in the Current Record will show a value all others will be blank.

 

[Cameron.Turner]

John Big Booty - thanks for the response (which is awesome btw). Any ideas on how I can overcome it without major database redesign?

 

[John Big Booty]

What I have done in the past to get around this, is to us a query to populate the subform, so rather than a combo I just use a Text box. Then to add items to the sub form I use a pop-up form on which I can have my cascading combo work as expected.