Sync two combo boxes; Error 7777

[HickoryII]

This is my first post and I'm not an expert in Access nor VBA but I've solved a lot of problems in programming by using this forum. It's been a great help.
I have an Access .mdb database of about 1300 volunteers. On my main volunteer form I have two unbound combo boxes that I use to select individual volunteers; one by name, the other by their number. They both work but when I select either one the other doesn't sync.
In an effort to get them to sync I entered the code in the "change " event but it's giving me an error message with the number 7777.
Attached are pictures of what I have coded along with the the two variables set as Public in a standard module.
I've read that trying to set the listindex of a combo/listbox when that listbox does not have the focus can cause an error. I'm not sure if that's the problem I'm having or not but if someone can help me solve this it would be much appreciated.

 

[Beetle]

First, you should be using the After Update event, not the Change event.

Second, what do the Row Sources of each combo box look like? (post the sql of each here)

 

[Solo712]

I've read that trying to set the listindex of a combo/listbox when that listbox does not have the focus can cause an error. I'm not sure if that's the problem I'm having or not but if someone can help me solve this it would be much appreciated.

Try setting the focus to the combo box before assigning the listindex.

Best,
Jiri

 

[HickoryII]

Hi Beetle,
Thanks for your input.
Here's the rowsource for Combo208: SELECT DISTINCTROW [Main Volunteer List].[Volunteer Number], [Main Volunteer List].[Last Name], [Main Volunteer List].First, [Main Volunteer List].Middle FROM [Main Volunteer List] ORDER BY [Main Volunteer List].[Last Name], [Main Volunteer List].First;

And the RowSource for cboSelectVol is: SELECT DISTINCTROW [Main Volunteer List].[Volunteer Number] FROM [Main Volunteer List] ORDER BY [Main Volunteer List].[Volunteer Number];

And Solo712, likewise thanks for your interest,
I've attached a pic of what is in the Combo208 "After Update" event.
Can you provide some direction on how to set the focus to the Combo Box?

 

[Beetle]

Assuming that [Volunteer Number] is the bound column of Combo208, this really should as simple as;

Private Sub Combo208 After_Update ()

    Me.cboSelectVol = me.Combo208

End Sub

Private Sub cboSelectVol After_Update()

    Me.Combo208 = Me.cboSelectVol

End Sub

 

[HickoryII]

thanks so much (Beetle)Sean. It worked perfectly; and so simple. I told you I was new at this.
I wanted to mark this thread as solved but don't know where or how to do it. Anyway thanks again. Bob (Hickory)