Specific Column in ListBox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list box that displays 3 columns of information based on a combo box
which runs a SQL statement. For example, the statement is something like
SELECT [field1],[field2],[field3] from Table.

The list box will display field1, field2 and field 3 in 3 columns. However,
I need to perform some kind of check with field3 or column3 in the listbox.
How do I go about doing that?

I know I can use the .Column() function using the combo box but it doesn't
seem to work with the list box. Thanks.
 
Column works with Listboxes as well. Be aware that the 3rd column will be
Column(2), though, since column numbering starts at 0.
 
Actually, it just occurred to me your problem may be due to having
multiselect turned on for your listbox. When you have multiselect on, you
can't just refer to the listbox to get at its values: you have to look at
the ItemsSelected collection of the listbox.

Sub BoundData()
Dim frm As Form, ctl As Control
Dim varItm As Variant

Set frm = Forms!Contacts
Set ctl = frm!Names
For Each varItm In ctl.ItemsSelected
Debug.Print ctl.Column(2, varItm)
Next varItm
End Sub

Alternatively, you could use

Sub BoundData()
Dim frm As Form, ctl As Control
Dim intLoop As Integer

Set frm = Forms!Contacts
Set ctl = frm!Names
For intLoop = 0 to (ctl.ListCount - 1)
If ctl.Selected(intLoop) = True Then
Debug.Print ctl.Column(2, intLoop)
End If
Next intLoop
End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Douglas J. Steele said:
Column works with Listboxes as well. Be aware that the 3rd column will be
Column(2), though, since column numbering starts at 0.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



CK said:
I have a list box that displays 3 columns of information based on a
Click to expand...
combo
box
which runs a SQL statement. For example, the statement is something like
SELECT [field1],[field2],[field3] from Table.

The list box will display field1, field2 and field 3 in 3 columns. However,
I need to perform some kind of check with field3 or column3 in the listbox.
How do I go about doing that?

I know I can use the .Column() function using the combo box but it doesn't
seem to work with the list box. Thanks.
Click to expand...
Click to expand...
 
Thanks, Douglas. How do I know if my listbox has multiselect turned on? THe
listbox is based on a query. I forgot to mentioned that it is on a different
form. OK, the situation is like this: in Form1, I have a combo box and a
listbox as mentioned. When I double-click on one of the entries in the
listbox, it will open Form2 where I can enter a number. I want to compare
this number with the number in Column4 of the listbox in Form1 so that I can
display some messages.

ck

Douglas J. Steele said:
Actually, it just occurred to me your problem may be due to having
multiselect turned on for your listbox. When you have multiselect on, you
can't just refer to the listbox to get at its values: you have to look at
the ItemsSelected collection of the listbox.

Sub BoundData()
Dim frm As Form, ctl As Control
Dim varItm As Variant

Set frm = Forms!Contacts
Set ctl = frm!Names
For Each varItm In ctl.ItemsSelected
Debug.Print ctl.Column(2, varItm)
Next varItm
End Sub

Alternatively, you could use

Sub BoundData()
Dim frm As Form, ctl As Control
Dim intLoop As Integer

Set frm = Forms!Contacts
Set ctl = frm!Names
For intLoop = 0 to (ctl.ListCount - 1)
If ctl.Selected(intLoop) = True Then
Debug.Print ctl.Column(2, intLoop)
End If
Next intLoop
End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Douglas J. Steele said:
Column works with Listboxes as well. Be aware that the 3rd column will be
Column(2), though, since column numbering starts at 0.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



CK said:
I have a list box that displays 3 columns of information based on a
Click to expand...
combo
box
which runs a SQL statement. For example, the statement is something like
SELECT [field1],[field2],[field3] from Table.

The list box will display field1, field2 and field 3 in 3 columns. However,
I need to perform some kind of check with field3 or column3 in the listbox.
How do I go about doing that?

I know I can use the .Column() function using the combo box but it doesn't
seem to work with the list box. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
In Form2, I run some check in the AfterUpdate event of Field4 and it looks
like:

If Field4 > Forms!Form1.[listbox].Column(x) Then
MsgBox "The value is too large", vbOKOnly

Is this correct?

ck
----------------------------------------------------------

CK said:
Thanks, Douglas. How do I know if my listbox has multiselect turned on? THe
listbox is based on a query. I forgot to mentioned that it is on a different
form. OK, the situation is like this: in Form1, I have a combo box and a
listbox as mentioned. When I double-click on one of the entries in the
listbox, it will open Form2 where I can enter a number. I want to compare
this number with the number in Column4 of the listbox in Form1 so that I can
display some messages.

ck

Douglas J. Steele said:
Actually, it just occurred to me your problem may be due to having
multiselect turned on for your listbox. When you have multiselect on, you
can't just refer to the listbox to get at its values: you have to look at
the ItemsSelected collection of the listbox.

Sub BoundData()
Dim frm As Form, ctl As Control
Dim varItm As Variant

Set frm = Forms!Contacts
Set ctl = frm!Names
For Each varItm In ctl.ItemsSelected
Debug.Print ctl.Column(2, varItm)
Next varItm
End Sub

Alternatively, you could use

Sub BoundData()
Dim frm As Form, ctl As Control
Dim intLoop As Integer

Set frm = Forms!Contacts
Set ctl = frm!Names
For intLoop = 0 to (ctl.ListCount - 1)
If ctl.Selected(intLoop) = True Then
Debug.Print ctl.Column(2, intLoop)
End If
Next intLoop
End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Douglas J. Steele said:
Column works with Listboxes as well. Be aware that the 3rd column will be
Column(2), though, since column numbering starts at 0.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a list box that displays 3 columns of information based on a combo
box
which runs a SQL statement. For example, the statement is something like
SELECT [field1],[field2],[field3] from Table.

The list box will display field1, field2 and field 3 in 3 columns.
However,
I need to perform some kind of check with field3 or column3 in the
listbox.
How do I go about doing that?

I know I can use the .Column() function using the combo box but it doesn't
seem to work with the list box. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
[listbox] should be whatever the listbox is named, and x should be 3 if
you're looking for the value in the 4th column of the selected row in the
listbox.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



CK said:
In Form2, I run some check in the AfterUpdate event of Field4 and it looks
like:

If Field4 > Forms!Form1.[listbox].Column(x) Then
MsgBox "The value is too large", vbOKOnly

Is this correct?

ck
----------------------------------------------------------

CK said:
Thanks, Douglas. How do I know if my listbox has multiselect turned on? THe
listbox is based on a query. I forgot to mentioned that it is on a different
form. OK, the situation is like this: in Form1, I have a combo box and a
listbox as mentioned. When I double-click on one of the entries in the
listbox, it will open Form2 where I can enter a number. I want to compare
this number with the number in Column4 of the listbox in Form1 so that I can
display some messages.

ck

Douglas J. Steele said:
Actually, it just occurred to me your problem may be due to having
multiselect turned on for your listbox. When you have multiselect on, you
can't just refer to the listbox to get at its values: you have to look at
the ItemsSelected collection of the listbox.

Sub BoundData()
Dim frm As Form, ctl As Control
Dim varItm As Variant

Set frm = Forms!Contacts
Set ctl = frm!Names
For Each varItm In ctl.ItemsSelected
Debug.Print ctl.Column(2, varItm)
Next varItm
End Sub

Alternatively, you could use

Sub BoundData()
Dim frm As Form, ctl As Control
Dim intLoop As Integer

Set frm = Forms!Contacts
Set ctl = frm!Names
For intLoop = 0 to (ctl.ListCount - 1)
If ctl.Selected(intLoop) = True Then
Debug.Print ctl.Column(2, intLoop)
End If
Next intLoop
End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Column works with Listboxes as well. Be aware that the 3rd column will be
Column(2), though, since column numbering starts at 0.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a list box that displays 3 columns of information based on a
combo
box
which runs a SQL statement. For example, the statement is something like
SELECT [field1],[field2],[field3] from Table.

The list box will display field1, field2 and field 3 in 3 columns.
However,
I need to perform some kind of check with field3 or column3 in the
listbox.
How do I go about doing that?

I know I can use the .Column() function using the combo box but it
doesn't
seem to work with the list box. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
The funny thing is that when I use:

If Field4 > Forms!Form1.[listbox].Column(x) Then
MsgBox "The value is too large", vbOKOnly
End If

It doesn't work - the message does not popup- but when I use:

If Field4 < Forms!Form1.[listbox].Column(x) Then
MsgBox "The value is too large", vbOKOnly
End If

The message will pop up but it still doesn't actually performs the "<"
comparison. In other words, whether the number I enter in Field4 is actually
greater or lesses than Column(x), the message still pops up. Looks like the
If statement still doesn't work. Any ideas? Thanks.
ck



Douglas J. Steele said:
[listbox] should be whatever the listbox is named, and x should be 3 if
you're looking for the value in the 4th column of the selected row in the
listbox.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



CK said:
In Form2, I run some check in the AfterUpdate event of Field4 and it looks
like:

If Field4 > Forms!Form1.[listbox].Column(x) Then
MsgBox "The value is too large", vbOKOnly

Is this correct?

ck
----------------------------------------------------------

CK said:
Thanks, Douglas. How do I know if my listbox has multiselect turned on? THe
listbox is based on a query. I forgot to mentioned that it is on a different
form. OK, the situation is like this: in Form1, I have a combo box and a
listbox as mentioned. When I double-click on one of the entries in the
listbox, it will open Form2 where I can enter a number. I want to compare
this number with the number in Column4 of the listbox in Form1 so that I can
display some messages.

ck

:

Actually, it just occurred to me your problem may be due to having
multiselect turned on for your listbox. When you have multiselect on, you
can't just refer to the listbox to get at its values: you have to look at
the ItemsSelected collection of the listbox.

Sub BoundData()
Dim frm As Form, ctl As Control
Dim varItm As Variant

Set frm = Forms!Contacts
Set ctl = frm!Names
For Each varItm In ctl.ItemsSelected
Debug.Print ctl.Column(2, varItm)
Next varItm
End Sub

Alternatively, you could use

Sub BoundData()
Dim frm As Form, ctl As Control
Dim intLoop As Integer

Set frm = Forms!Contacts
Set ctl = frm!Names
For intLoop = 0 to (ctl.ListCount - 1)
If ctl.Selected(intLoop) = True Then
Debug.Print ctl.Column(2, intLoop)
End If
Next intLoop
End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Column works with Listboxes as well. Be aware that the 3rd column will be
Column(2), though, since column numbering starts at 0.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a list box that displays 3 columns of information based on a
combo
box
which runs a SQL statement. For example, the statement is something like
SELECT [field1],[field2],[field3] from Table.

The list box will display field1, field2 and field 3 in 3 columns.
However,
I need to perform some kind of check with field3 or column3 in the
listbox.
How do I go about doing that?

I know I can use the .Column() function using the combo box but it
doesn't
seem to work with the list box. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
CK said:
The funny thing is that when I use:

If Field4 > Forms!Form1.[listbox].Column(x) Then
MsgBox "The value is too large", vbOKOnly
End If

It doesn't work - the message does not popup- but when I use:

If Field4 < Forms!Form1.[listbox].Column(x) Then
MsgBox "The value is too large", vbOKOnly
End If

The message will pop up but it still doesn't actually performs the "<"
comparison. In other words, whether the number I enter in Field4 is
actually greater or lesses than Column(x), the message still pops up.
Looks like the If statement still doesn't work. Any ideas? Thanks.
Click to expand...

Bear in mind that the columns of a list box or combo box are always
text -- well, character strings -- regardless of whether the table
fields from which they are drawn are numeric. If you compare numeric
*strings", as opposed to number values themselves, you get a lexical
comparison, which is seldom what you want. For example,

?"12345" < "56"
True

If this is the problem you're experiencing, you might try this:

If Val(Field4) > Val(Forms!Form1.[listbox].Column(x)) Then
MsgBox "The value is too large", vbOKOnly
End If
 
Thanks, Dirk. You were absolutely right about that one! I didn't know that
listbox and combo columns are string and I've just learned something new.

ck

Dirk Goldgar said:
CK said:
The funny thing is that when I use:

If Field4 > Forms!Form1.[listbox].Column(x) Then
MsgBox "The value is too large", vbOKOnly
End If

It doesn't work - the message does not popup- but when I use:

If Field4 < Forms!Form1.[listbox].Column(x) Then
MsgBox "The value is too large", vbOKOnly
End If

The message will pop up but it still doesn't actually performs the "<"
comparison. In other words, whether the number I enter in Field4 is
actually greater or lesses than Column(x), the message still pops up.
Looks like the If statement still doesn't work. Any ideas? Thanks.
Click to expand...

Bear in mind that the columns of a list box or combo box are always
text -- well, character strings -- regardless of whether the table
fields from which they are drawn are numeric. If you compare numeric
*strings", as opposed to number values themselves, you get a lexical
comparison, which is seldom what you want. For example,

?"12345" < "56"
True

If this is the problem you're experiencing, you might try this:

If Val(Field4) > Val(Forms!Form1.[listbox].Column(x)) Then
MsgBox "The value is too large", vbOKOnly
End If

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top