Since the listbox is not multiselect, it will return simply return a value
from the selected row. To get the value of a different column than the Bound
Column, you would use the Column statement. Specifying the row won't be
necessary. You'll get the value from the selected row if you don't specify.
The problem you are probably running into is that the Column property is
zero based. This means that the first column is zero, the second is one, the
third is two, and the fourth (the one you want) is three.
strCriteria = "[ConSurname] = '" & Me!lbxContacts.Column(3) & "'"
Also, judging by the name of your field, the values are Surnames. You are
enclosing the value in single quotes because the value is a string. This is
normally correct; however, surnames can contain apostrophes (i.e. O'Hare)
and if you have some like this, it will break your code. It is possible to
change it to double quotes to solve this problem, but it is harder to read.
strCriteria = "[ConSurname] = """ & Me!lbxContacts.Column(3) & """"
or
strCriteria = "[ConSurname] = " & Chr(34) & Me!lbxContacts.Column(3) &
Chr(34)
--
Wayne Morgan
MS Access MVP
"Alvey Sidecast" <al************@yahoo.com> wrote in message
news:ed**************************@posting.google.c om...
This is probably embarrassingly simple, but I've been trawling through
this ng for hours now and my brain hurts.
I've got an unbound multi-column listbox (multi-select=none) whose
rowsource is a query based on a combobox selection. After the
selection is made from the combobox the listbox may, or may not, have
records in it. If it doesn't, no problems, I've got;
If Me.lbxContacts.ItemsSelected.Count = 0 Then
to handle that. But when there *are* multiple rows returned I want to
select a row and take the value of column 4 from the selected row to
use in a strCriteria statement. Something like;
strCriteria = "[ConSurname] = '" & Me!lbxContacts.Column(4,r) & "'"
The thing that's caused me to stop dead is that I can't find a way to
get the value/count of the highlighted row. Can someone please point
me in the right direction?
cheers