How to syncronize a Combo Box?

[Guest]

Hi!

I have an unbound combo box with a Query RowSource with LimitToList set to
True.

What I want is to make sure that the combo box stays current with the
underlaying data that the query selects from.

I have a mechanism setup already that explicitly calls a form's procedure
when that data changes. So far, in that proecdure I do
"Me.MyComboBox.Requery".

That updats the drop-down list on the combo box, but if the current combo
box Value is no longer in the list, it is allowed to stay there. I want to
set that Value to Null if it is no longer valid (but keep it if it is ok).

Any ideas on what to do, short of doing DCount("*", ...)? I would hate the
DCount route since:
a) the "Domain" for the combo box is a JOIN
b) The "WHERE" predicate changes a lot (this is not a static rowsource -- I
change it depending on user input)
c) It seems too slow.

So, am I overlooking something?

- Igor

 

[Ken Snell [MVP]]

Loop through the combo box's ItemData array and test the value that is in
the combo box. In this example, I assume that the value in the combo box is
a Long integer value; if it's anything else, change the casting function
that I use to change the ItemData value from a string into the correct data
type:

Dim lngC As Long
For lngC = 0 To Me.ComboBoxName.ListCount - 1
If Me.ID11.Value = CLng(Me.ComboBoxName.ItemData(lngC)) Then
MsgBox lngC
Exit Sub
End If
Next lngC
Me.ComboBoxName.Value = Null

 

[Guest]

Thank you! I'll try this right away.

I've overlooked ItemData property.

- Igor