Synchronizing record navigation control and record selection combo box

Question

I have a form bound to a query, with controls bound to fields of the query. I also have an unbound combobox, whose Row Source is the same query, used to select the current form record via its After Update event handler:

Private Sub Loc_cbo_AfterUpdate()
    DoCmd.SearchForRecord , "", acFirst, "ID = " & Str(Nz(Loc_cbo, 0))
End Sub

The combobox has three columns:

the Bound Column, an invisible ID,
a location code,
a location name.

The combo's Row Source is SELECT * FROM Sites ORDER BY Loc DESC. (Originally, it was just Sites but since I have the form's Order By as Site.Loc DESC, I wanted the order to match.)

It works fine. Of course, I can also navigate through the form’s records via its navigation control (First, Previous, Next, Last). That, too, works. However, I’d like the combobox to be synchronized with the current record as it changes via the navigation control.

I’ve tried to do it via the form’s Current event handler, three different ways. First, to set the combobox’s Text value to match that of the corresponding textbox control

Private Sub Form_Current()
    Loc_cbo.SetFocus
    Loc_cbo.Text = Loc_txt ‘ ERROR
End Sub

but I get

Run-time error ‘2101’: The setting you entered isn’t valid for this property.

Second, to set the combobox’s ListIndex value relative to the current record

Private Sub Form_Current()
    Loc_cbo.SetFocus
    Loc_cbo.ListIndex = CurrentRecord - 1 ‘ ERROR
End Sub

but I get either the same Run-time error ‘2101’ or

Run-time error ‘7777’: You’ve used the ListIndex property incorrectly.

in the same place.

Third, (thanks to HansUp's early answer) to set the combobox’s Value property:

Private Sub Form_Current()
    Loc_cbo.Value = Loc_txt.Value 
End Sub

This "works" in so far as there is no error message and, according to debug.prints, the value of Loc_cbo does change to match that of Loc_txt. However, the visible textbox-like portion of the combobox now appears to be empty/blank (nothing in it) always.

How can I get the record selector combobox to agree with the current record when the latter has been changed via the record navigator? I cannot believe users will be happy having the two out of synch. I also cannot believe I haven't found this problem described anywhere else!

HansUp HansUp · Accepted Answer · 2015-05-06T23:52:41

Assign to the combo's .Value property to change its selection. For .Value, you don't need to SetFocus.

Private Sub Form_Current()
    Me.Loc_cbo.Value = Me.Loc_txt.Value
End Sub

Note this suggestion assumes the combo's .Value is what you want to match to the text box value. Check to confirm you want the match between the combo and text box to be based on the combo's .Value instead of another of the combo's columns.

Post mortem:

The content of the text box was intended to match one of the combo's columns. However, since the combo's Bound Column contains numbers associated with that other column, Martin used a DLookup expression to fetch the number which corresponds to the text box content and assigned that number to the combo's .Value property:

Me.Loc_cbo.Value = DLookup("ID", "Sites", "Loc= '" & Me.Loc_txt.Value & "'")

Note a combo's .Value is the value in the Bound Column of its selected row.

Synchronizing record navigation control and record selection combo box

1 Answers