Going Nuts with Selecting the Value of List Box using an unbound TextBox

[David C. Holley]

I'm a big fan of supplying users with a means to select a value in a list
box by manually entering it since it gives them a better overall experience.
The code below was copied as-is from a form that has an unbound text box and
an unbound list box. When the value is entered in the text box, it
successfully sets the value of the list box and selects the appropriate item
in the list box. However, I've copied it as-is to a new form and while I've
changed the form & control names appropriately, its not friggin' selecting
the item in the list box.

I have confirmed that the .BoundColumn property of the new list box is set
to the relevant field in the record source.

Private Sub pg1_txtTrailerNumberSearch_AfterUpdate()

On Error GoTo Err_pg1_txtTrailerNumberSearch_AfterUpdate

[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory] =
[Forms]![frmTrailerInventoryMaintenance]![pg1_txtTrailerNumberSearch]

If
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory].ItemsSelected.Count
= 0 Then
MsgBox "Trailer number not found.", vbInformation + vbOKOnly
End If

Exit_pg1_txtTrailerNumberSearch_AfterUpdate:
Exit Sub

Err_pg1_txtTrailerNumberSearch_AfterUpdate:
MsgBox getDefaultErrorMessage(Me.Name,
"pg1_txtTrailerNumberSearch_AfterUpdate", Err.Number), vbCritical
Resume Exit_pg1_txtTrailerNumberSearch_AfterUpdate

End Sub

 

[Stuart McCall]

I'm a big fan of supplying users with a means to select a value in a list
box by manually entering it since it gives them a better overall
experience. The code below was copied as-is from a form that has an
unbound text box and an unbound list box. When the value is entered in the
text box, it successfully sets the value of the list box and selects the
appropriate item in the list box. However, I've copied it as-is to a new
form and while I've changed the form & control names appropriately, its
not friggin' selecting the item in the list box.

I have confirmed that the .BoundColumn property of the new list box is set
to the relevant field in the record source.

Private Sub pg1_txtTrailerNumberSearch_AfterUpdate()

On Error GoTo Err_pg1_txtTrailerNumberSearch_AfterUpdate

[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory] =
[Forms]![frmTrailerInventoryMaintenance]![pg1_txtTrailerNumberSearch]

If
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory].ItemsSelected.Count
= 0 Then
MsgBox "Trailer number not found.", vbInformation + vbOKOnly
End If

Exit_pg1_txtTrailerNumberSearch_AfterUpdate:
Exit Sub

Err_pg1_txtTrailerNumberSearch_AfterUpdate:
MsgBox getDefaultErrorMessage(Me.Name,
"pg1_txtTrailerNumberSearch_AfterUpdate", Err.Number), vbCritical
Resume Exit_pg1_txtTrailerNumberSearch_AfterUpdate

End Sub

Check that the listbox's MultiSelect property is None. A multi-select
listbox doesn't have a valid Value property, which because it's the default
property for a listbox, is what you're assigning.

 

[David C. Holley]

Multi-select is set to None. What's odd is that after the code runs and I
check the value in the Immediate Window, the value IS set to the value that
was entered in the unbound text box, its just that the specific item isn't
highlighted.

I'm a big fan of supplying users with a means to select a value in a list
box by manually entering it since it gives them a better overall
experience. The code below was copied as-is from a form that has an
unbound text box and an unbound list box. When the value is entered in
the text box, it successfully sets the value of the list box and selects
the appropriate item in the list box. However, I've copied it as-is to a
new form and while I've changed the form & control names appropriately,
its not friggin' selecting the item in the list box.

I have confirmed that the .BoundColumn property of the new list box is
set to the relevant field in the record source.

Private Sub pg1_txtTrailerNumberSearch_AfterUpdate()

On Error GoTo Err_pg1_txtTrailerNumberSearch_AfterUpdate

[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory] =
[Forms]![frmTrailerInventoryMaintenance]![pg1_txtTrailerNumberSearch]

If
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory].ItemsSelected.Count
= 0 Then
MsgBox "Trailer number not found.", vbInformation + vbOKOnly
End If

Exit_pg1_txtTrailerNumberSearch_AfterUpdate:
Exit Sub

Err_pg1_txtTrailerNumberSearch_AfterUpdate:
MsgBox getDefaultErrorMessage(Me.Name,
"pg1_txtTrailerNumberSearch_AfterUpdate", Err.Number), vbCritical
Resume Exit_pg1_txtTrailerNumberSearch_AfterUpdate

End Sub

Check that the listbox's MultiSelect property is None. A multi-select
listbox doesn't have a valid Value property, which because it's the
default property for a listbox, is what you're assigning.

 

[Stuart McCall]

Multi-select is set to None. What's odd is that after the code runs and I
check the value in the Immediate Window, the value IS set to the value
that was entered in the unbound text box, its just that the specific item
isn't highlighted.

I'm a big fan of supplying users with a means to select a value in a
list box by manually entering it since it gives them a better overall
experience. The code below was copied as-is from a form that has an
unbound text box and an unbound list box. When the value is entered in
the text box, it successfully sets the value of the list box and selects
the appropriate item in the list box. However, I've copied it as-is to a
new form and while I've changed the form & control names appropriately,
its not friggin' selecting the item in the list box.

I have confirmed that the .BoundColumn property of the new list box is
set to the relevant field in the record source.

Private Sub pg1_txtTrailerNumberSearch_AfterUpdate()

On Error GoTo Err_pg1_txtTrailerNumberSearch_AfterUpdate

[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory] =
[Forms]![frmTrailerInventoryMaintenance]![pg1_txtTrailerNumberSearch]

If
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory].ItemsSelected.Count
= 0 Then
MsgBox "Trailer number not found.", vbInformation + vbOKOnly
End If

Exit_pg1_txtTrailerNumberSearch_AfterUpdate:
Exit Sub

Err_pg1_txtTrailerNumberSearch_AfterUpdate:
MsgBox getDefaultErrorMessage(Me.Name,
"pg1_txtTrailerNumberSearch_AfterUpdate", Err.Number), vbCritical
Resume Exit_pg1_txtTrailerNumberSearch_AfterUpdate

End Sub

Check that the listbox's MultiSelect property is None. A multi-select
listbox doesn't have a valid Value property, which because it's the
default property for a listbox, is what you're assigning.

I just re-read your code and found that, despite you've set multiselect to
None, you're treating it as if it were multiselect, here:

If
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory].ItemsSelected.Count
= 0 Then

I think that line ought to read:

If [Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory].Value
= [Forms]![frmTrailerInventoryMaintenance]![pg1_txtTrailerNumberSearch] Then

or (easier on the eye) :

With [Forms]![frmTrailerInventoryMaintenance]
If ![pg1_lstTrailerInventory] = ![pg1_txtTrailerNumberSearch] Then
...
End With

 

[David C. Holley]

The logic behind the code is correct.

'Set the value of the list box to the value of the txtBox
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory] =
[Forms]![frmTrailerInventoryMaintenance]![pg1_txtTrailerNumberSearch]

'If nothing is selected, the value does not appear in the list box
If
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory].ItemsSelected.Count
= 0 Then
MsgBox "Trailer number not found.", vbInformation + vbOKOnly
End If

This DOES work on the original form. I just can't figure out why it doesn't
work on the other form. I've checked all of the properties to be
certain that everything is the same. The listbox's .BoundColumn is [1] which
has the correct value from its RecordSource.

Multi-select is set to None. What's odd is that after the code runs and I
check the value in the Immediate Window, the value IS set to the value
that was entered in the unbound text box, its just that the specific item
isn't highlighted.

"David C. Holley" <David.C.Holley> wrote in message
I'm a big fan of supplying users with a means to select a value in a
list box by manually entering it since it gives them a better overall
experience. The code below was copied as-is from a form that has an
unbound text box and an unbound list box. When the value is entered in
the text box, it successfully sets the value of the list box and
selects the appropriate item in the list box. However, I've copied it
as-is to a new form and while I've changed the form & control names
appropriately, its not friggin' selecting the item in the list box.

I have confirmed that the .BoundColumn property of the new list box is
set to the relevant field in the record source.

Private Sub pg1_txtTrailerNumberSearch_AfterUpdate()

On Error GoTo Err_pg1_txtTrailerNumberSearch_AfterUpdate

[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory] =
[Forms]![frmTrailerInventoryMaintenance]![pg1_txtTrailerNumberSearch]

If
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory].ItemsSelected.Count
= 0 Then
MsgBox "Trailer number not found.", vbInformation + vbOKOnly
End If

Exit_pg1_txtTrailerNumberSearch_AfterUpdate:
Exit Sub

Err_pg1_txtTrailerNumberSearch_AfterUpdate:
MsgBox getDefaultErrorMessage(Me.Name,
"pg1_txtTrailerNumberSearch_AfterUpdate", Err.Number), vbCritical
Resume Exit_pg1_txtTrailerNumberSearch_AfterUpdate

End Sub

Check that the listbox's MultiSelect property is None. A multi-select
listbox doesn't have a valid Value property, which because it's the
default property for a listbox, is what you're assigning.

I just re-read your code and found that, despite you've set multiselect to
None, you're treating it as if it were multiselect, here:

If
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory].ItemsSelected.Count
= 0 Then

I think that line ought to read:

If
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory].Value =
[Forms]![frmTrailerInventoryMaintenance]![pg1_txtTrailerNumberSearch] Then

or (easier on the eye) :

With [Forms]![frmTrailerInventoryMaintenance]
If ![pg1_lstTrailerInventory] = ![pg1_txtTrailerNumberSearch] Then
...
End With

 

[Stuart McCall]

The logic behind the code is correct.

Sorry to have to contradict you, but the answer is 'yes and no'

In the following snippet, you are coding for a non-multiselect listbox by
assigning a value to it's default property, .Value

'Set the value of the list box to the value of the txtBox
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory] =
[Forms]![frmTrailerInventoryMaintenance]![pg1_txtTrailerNumberSearch]

Whereas here you refer to the .ItemsSelected hidden collection, which is
invalid when the .MultiSelect property is set to 'None'.

'If nothing is selected, the value does not appear in the list box
If
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory].ItemsSelected.Count
= 0 Then
MsgBox "Trailer number not found.", vbInformation + vbOKOnly
End If

This DOES work on the original form. I just can't figure out why it
doesn't work on the other form. I've checked all of the properties to be
certain that everything is the same. The listbox's .BoundColumn is [1]
which has the correct value from its RecordSource.

If that's true (all properties identical) then I'm stumped.

Multi-select is set to None. What's odd is that after the code runs and
I check the value in the Immediate Window, the value IS set to the value
that was entered in the unbound text box, its just that the specific
item isn't highlighted.

"David C. Holley" <David.C.Holley> wrote in message
I'm a big fan of supplying users with a means to select a value in a
list box by manually entering it since it gives them a better overall
experience. The code below was copied as-is from a form that has an
unbound text box and an unbound list box. When the value is entered in
the text box, it successfully sets the value of the list box and
selects the appropriate item in the list box. However, I've copied it
as-is to a new form and while I've changed the form & control names
appropriately, its not friggin' selecting the item in the list box.

I have confirmed that the .BoundColumn property of the new list box is
set to the relevant field in the record source.

Private Sub pg1_txtTrailerNumberSearch_AfterUpdate()

On Error GoTo Err_pg1_txtTrailerNumberSearch_AfterUpdate

[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory]
=
[Forms]![frmTrailerInventoryMaintenance]![pg1_txtTrailerNumberSearch]

If
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory].ItemsSelected.Count
= 0 Then
MsgBox "Trailer number not found.", vbInformation + vbOKOnly
End If

Exit_pg1_txtTrailerNumberSearch_AfterUpdate:
Exit Sub

Err_pg1_txtTrailerNumberSearch_AfterUpdate:
MsgBox getDefaultErrorMessage(Me.Name,
"pg1_txtTrailerNumberSearch_AfterUpdate", Err.Number), vbCritical
Resume Exit_pg1_txtTrailerNumberSearch_AfterUpdate

End Sub

Check that the listbox's MultiSelect property is None. A multi-select
listbox doesn't have a valid Value property, which because it's the
default property for a listbox, is what you're assigning.

I just re-read your code and found that, despite you've set multiselect
to None, you're treating it as if it were multiselect, here:

If
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory].ItemsSelected.Count
= 0 Then

I think that line ought to read:

If
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory].Value
= [Forms]![frmTrailerInventoryMaintenance]![pg1_txtTrailerNumberSearch]
Then

or (easier on the eye) :

With [Forms]![frmTrailerInventoryMaintenance]
If ![pg1_lstTrailerInventory] = ![pg1_txtTrailerNumberSearch] Then
...
End With

 

[Dirk Goldgar]

The logic behind the code is correct.

Sorry to have to contradict you, but the answer is 'yes and no'

In the following snippet, you are coding for a non-multiselect listbox by
assigning a value to it's default property, .Value

'Set the value of the list box to the value of the txtBox
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory] =
[Forms]![frmTrailerInventoryMaintenance]![pg1_txtTrailerNumberSearch]

Whereas here you refer to the .ItemsSelected hidden collection, which is
invalid when the .MultiSelect property is set to 'None'.

Stuart, what makes you think the .ItemsSelected property and collection are
invalid when .MultiSelect is set to "None"? The help file doesn't say so,
and experimentation shows that they are not. There are some things that
don't work well with non-multiselect list boxes, but I don't think the
..ItemsSelected property is one of them.

I can't help thinking the problem here is in the .BoundColumn property
somehow, or the field that corresponds to that column, though David has said
it isn't.

David, what is the rowsource of the list box?

 

[Stuart McCall]

The logic behind the code is correct.

Sorry to have to contradict you, but the answer is 'yes and no'

In the following snippet, you are coding for a non-multiselect listbox by
assigning a value to it's default property, .Value

'Set the value of the list box to the value of the txtBox
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory] =
[Forms]![frmTrailerInventoryMaintenance]![pg1_txtTrailerNumberSearch]

Whereas here you refer to the .ItemsSelected hidden collection, which is
invalid when the .MultiSelect property is set to 'None'.

Stuart, what makes you think the .ItemsSelected property and collection
are invalid when .MultiSelect is set to "None"? The help file doesn't say
so, and experimentation shows that they are not. There are some things
that don't work well with non-multiselect list boxes, but I don't think
the .ItemsSelected property is one of them.

I've had a little time to experiment with this myself today and I've found
that you're right. I'm sure I've had an issue with this in the past, but
naturally I can't remember what it was now. Must be having a CRAFT moment..

To David: I retract my statement contradicting you and apologise for wasting
your time.

It does seem odd to be referring to a collection when the count can only
ever be 1 or 0. Personally I use the listindex to determine whether anything
has been selected and if so which one.

 

[David C. Holley]

I did a side-by-side comparison of both controls and figured out that it was
the InheritValueList property that didn't match. Not being familar with the
property I had ignored it when I first started trying to figure out the
problem

When I initially came up with the concept of using an unbound text box to
select a value in a listbox, I was using DAO to search through the recordset
of the listbox and select the correct value. At the time, we were using two
different values to refer to the same vehicle (license plate and vehicle
number). The user could enter either value in the text box and the code
would search for a match in one column and if not found then search again in
the other value. That has all changed hence the stripped down version of it.

"David C. Holley" <David.C.Holley> wrote in message
The logic behind the code is correct.

Sorry to have to contradict you, but the answer is 'yes and no'

In the following snippet, you are coding for a non-multiselect listbox
by assigning a value to it's default property, .Value


'Set the value of the list box to the value of the txtBox
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory] =
[Forms]![frmTrailerInventoryMaintenance]![pg1_txtTrailerNumberSearch]


Whereas here you refer to the .ItemsSelected hidden collection, which is
invalid when the .MultiSelect property is set to 'None'.

Stuart, what makes you think the .ItemsSelected property and collection
are invalid when .MultiSelect is set to "None"? The help file doesn't
say so, and experimentation shows that they are not. There are some
things that don't work well with non-multiselect list boxes, but I don't
think the .ItemsSelected property is one of them.

I've had a little time to experiment with this myself today and I've found
that you're right. I'm sure I've had an issue with this in the past, but
naturally I can't remember what it was now. Must be having a CRAFT
moment..

To David: I retract my statement contradicting you and apologise for
wasting your time.

It does seem odd to be referring to a collection when the count can only
ever be 1 or 0. Personally I use the listindex to determine whether
anything has been selected and if so which one.

I can't help thinking the problem here is in the .BoundColumn property
somehow, or the field that corresponds to that column, though David has
said it isn't.

David, what is the rowsource of the list box?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[David C. Holley]

Since InheritValueList was added in A2007, the fact that the code actually
works is simply by accident. When I was using A2003, the search was being
powered by a search. I'm guessing that the InheritValueList changed how the
list box operates. Since in the code below, I'm setting the value of the
list box to the value found in the recordset.

Private Sub txtTrailerNumber_AfterUpdate()

On Error GoTo Err_txtTrailerNumber_AfterUpdate

'Loop throught the values in the listBox and select the appropriate
record, this
'will update the trailer activity subform as the Link Master/Child
fields are tied
'to the list box

Dim rs As Recordset

Set rs = [Forms]![frmTrailerActivity]![lstSelectTrailer].Recordset

rs.FindFirst "[DOT No] = '" &
[Forms]![frmTrailerActivity]![txtTrailerNumber] & "'"
If Not rs.NoMatch Then
[Forms]![frmTrailerActivity]![lstSelectTrailer] =
[Forms]![frmTrailerActivity]![txtTrailerNumber]
Me.cmdFocusTarget.SetFocus
[Forms]![frmTrailerActivity]![subfrmTrailerActivity].Requery
Call showHideControls
Else
rs.FindFirst "[Internal No] = '" &
[Forms]![frmTrailerActivity]![txtTrailerNumber] & "'"
If Not rs.NoMatch Then
[Forms]![frmTrailerActivity]![lstSelectTrailer] =
rs.Fields("[DOT No]")
Me.cmdFocusTarget.SetFocus
[Forms]![frmTrailerActivity]![subfrmTrailerActivity].Requery
Call showHideControls
Else
'*MUST* set the value first before calling showHideControls as
showHideControls operates
'based on the value of the listBox
[Forms]![frmTrailerActivity]![lstSelectTrailer] = Null
Me.cmdFocusTarget.SetFocus
[Forms]![frmTrailerActivity]![subfrmTrailerActivity].Requery
Call showHideControls
MsgBox "Trailer number not found.", vbInformation + vbOKOnly
End If
End If

Exit_txtTrailerNumber_AfterUpdate:
Exit Sub

Err_txtTrailerNumber_AfterUpdate:
MsgBox getDefaultErrorMessage(Me.Name, "txtTrailerNumber_AfterUpdate",
Err.Number), vbCritical
Resume Exit_txtTrailerNumber_AfterUpdate

End Sub

I did a side-by-side comparison of both controls and figured out that it
was the InheritValueList property that didn't match. Not being familar with
the property I had ignored it when I first started trying to figure out the
problem

When I initially came up with the concept of using an unbound text box to
select a value in a listbox, I was using DAO to search through the
recordset of the listbox and select the correct value. At the time, we
were using two different values to refer to the same vehicle (license
plate and vehicle number). The user could enter either value in the text
box and the code would search for a match in one column and if not found
then search again in the other value. That has all changed hence the
stripped down version of it.

"David C. Holley" <David.C.Holley> wrote in message
The logic behind the code is correct.

Sorry to have to contradict you, but the answer is 'yes and no'

In the following snippet, you are coding for a non-multiselect listbox
by assigning a value to it's default property, .Value


'Set the value of the list box to the value of the txtBox
[Forms]![frmTrailerInventoryMaintenance]![pg1_lstTrailerInventory] =
[Forms]![frmTrailerInventoryMaintenance]![pg1_txtTrailerNumberSearch]


Whereas here you refer to the .ItemsSelected hidden collection, which
is invalid when the .MultiSelect property is set to 'None'.

Stuart, what makes you think the .ItemsSelected property and collection
are invalid when .MultiSelect is set to "None"? The help file doesn't
say so, and experimentation shows that they are not. There are some
things that don't work well with non-multiselect list boxes, but I don't
think the .ItemsSelected property is one of them.

I've had a little time to experiment with this myself today and I've
found that you're right. I'm sure I've had an issue with this in the
past, but naturally I can't remember what it was now. Must be having a
CRAFT moment..

To David: I retract my statement contradicting you and apologise for
wasting your time.

It does seem odd to be referring to a collection when the count can only
ever be 1 or 0. Personally I use the listindex to determine whether
anything has been selected and if so which one.

I can't help thinking the problem here is in the .BoundColumn property
somehow, or the field that corresponds to that column, though David has
said it isn't.

David, what is the rowsource of the list box?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Dirk Goldgar]

I did a side-by-side comparison of both controls and figured out that it
was the InheritValueList property that didn't match. Not being familar with
the property I had ignored it when I first started trying to figure out the
problem

I'm not familiar with it, either -- it hasn't arisen in the work I've done
with A2007.

 

[David C. Holley]

The documentation seems a bit sparse on it, but the property seems to have
something to do with how the control operates when used with a Lookup field.