CODEWITHSUNDEEP
ms-accesscomboboxvbams-access-2013
Mike
Mike

Reputation: 1011

Display Issue for filtering a combobox in a continuous form

I have a continuous form containing many records. Each record contains a combo box that has vba code filtering the viewable selections when it gains focus (it does this by changing the RowSource.

My problem is that if i click the combo box in record A and then click the combo box is record B, the display-filter for record B is applied to record A. I do not have this problem if I click another control in between clicking the combo boxes.

Why is the filter applied to A's combo box even though focus has (supposedly) been lost? And how do I prevent this display error from happening? The only solution I have now is to make an arbitrary control gain focus when focus for the combobox is lost, but obviously that can be annoying for the user...

Possibly relevant code (SuperintendentID is the name of my combobox):

Private Sub SuperintendentID_GotFocus()
'Filters SuperintendentID based on the task's division
    Me!SuperintendentID.RowSource = "SELECT tblJoinDivisionSuperintendent.SuperintendentID, [FirstName] & "" "" & [LastName] AS Name, tblJoinDivisionSuperintendent.DivisionID FROM tblSuperintendent RIGHT JOIN (tblDivision RIGHT JOIN tblJoinDivisionSuperintendent ON tblDivision.ID = tblJoinDivisionSuperintendent.DivisionID) ON tblSuperintendent.ID = tblJoinDivisionSuperintendent.SuperintendentID WHERE tblJoinDivisionSuperintendent.DivisionID = " & Me!DivisionID & ";"
    Me!SuperintendentID = Me!SuperintendentID.ItemData(0)
    Me!SuperintendentID = Me!SuperintendentID.OldValue 'Prevents a new value from being assigned when the control first gains focus
End Sub

Upvotes: 1

Views: 203

Answers (1)

D. Bachen
D. Bachen

Reputation: 178

You might be firing both the GotFocus events for comboboxA and comboboxB, but the screen is only showing you the last value you changed. You could add use a Form.Repaint method (where Form=your form object) as part of the GotFocus event to ensure that the screen was refreshed after changing the underlying datasource.

You could test this to make sure both events are firing by putting a debug.print statement in the event as well, such as debug.print "GotFocus ComboboxA"

Upvotes: 1

Related Questions