Solved Unbound List Box value not updating, but display does

[snow-raven]

I have an unbound form with unbound lookup boxes looking up from several tables/queries. The lookup functions are correctly returning a visible in my list boxes, but I can't seem to grab that list box values to use in my code unless I select the list box with the mouse.

Selecting in either of the 'Look up' combo boxes correctly updates the corresponding list box to the right.

For example, the first Accepted Name list box populates during the AfterUpdate event of the combo box using Me.WhichAcName.Requery. The RowSource for that list box is: "SELECT my_List.my_Scientific_Name FROM my_List INNER JOIN DB_Species_Synonyms ON my_List.my_ID = my_Synonyms.my_ID WHERE (((my_Synonyms.Syn_Mis_Err)=[Forms].[Dashboard].[Syn_Lookup])); "

I want to use the Accepted Name value in another field lookup, but the value will not update until I physically select the list box content with the mouse. I've tried forcing this by using SetFocus or requerying on the list box GotFocus event, but nothing will get the value to update except the mouse.

Here you can see some of my 7 million experiments:

'----------------------------------------------------------------------------------------------------------------
Private Sub Syn_Lookup_AfterUpdate()
'Debug.Print Me.WhichAcName.Value
'Debug.Print Me.WhichAcName.OldValue
'Debug.Print Me.WhichAcName.RowSource
'Debug.Print Me.WhichAcName.Column(0)
'Debug.Print Me.WhichAcName.Column(0, WhichAcName.ItemsSelected)
Me.WhichAcName.Requery
Me.WhichAcName.SetFocus
'Me.Repaint
Debug.Print Me.WhichAcName.Value
'Debug.Print Me.WhichAcName.Value
'Debug.Print Me.WhichAcName.OldValue
'Debug.Print Me.WhichAcName.RowSource
'Debug.Print Me.WhichAcName.Column(0)
'Debug.Print Me.WhichAcName.Column(0, WhichAcName.ItemsSelected)
End Sub
'----------------------------------------------------------------------------------------------------------------
'Private Sub WhichAcName_AfterUpdate()
'Debug.Print Me.WhichAcName.Value
'End Sub
'----------------------------------------------------------------------------------------------------------------
Private Sub WhichAcName_GotFocus()
Me.WhichAcName.Requery
Debug.Print Me.WhichAcName.Value
End Sub

After a bunch of experiements that only returned Null values, I happened to click inside the box & started getting at least a name, but it won't update until I click in the box again. SetFocus does not have the same results. What the heck do I need to do differently?

 

[MajP]

I do not get why you are using a one row listbox. Is that returning a single value? Can your rowsource return multiple values. If not use a textbox and dlookup instead of a rowsource.
A listbox can have many rows and one or more can be selected. Setting the focus is not going to set the value. If you want to set the value of the listbox then which row?
If you want to set it to the first row then something like
Me.lstBox = Me.lstBox.ItemData(0)

 

[MajP]

I think you are returning a single value so the whole listbox makes no sense. In that case make it a textbox and something like

me.txtBox = dlookup("my_Scientific_Name","SomeQuery", "my_Synonyms.Syn_Mis_Er )= " & me..[Dashboard].[Syn_Lookup])

 

[snow-raven]

I do not get why you are using a one row listbox. Is that returning a single value? Can your rowsource return multiple values. If not use a textbox and dlookup instead of a rowsource.
A listbox can have many rows and one or more can be selected. Setting the focus is not going to set the value. If you want to set the value of the listbox then which row?
If you want to set it to the first row then something like
Me.lstBox = Me.lstBox.ItemData(0)

Thanks, MajP! Your ItemData(0) got me exactly what I want from my list box. Which is a list box & not a text box because, reasons.