Code returns unexpected Null for Listbox Column

[Douglas Buchanan]

I cannot access certain column values of a list box using code.

I have a list box 'lstPrv' populated by the query below.

SELECT tblPrv.fkPrvID, lkpCat.CatNm, lkpSrv.SrvNm, lkpCat.pkCatID,
lkpSrv.pkSrvID FROM (lkpCat INNER JOIN lkpSrv ON lkpCat.pkCatID =
lkpSrv.fkCatID) INNER JOIN tblPrv ON lkpSrv.pkSrvID = tblPrv.fkSrvD

These are the columns displayed
tblPrv.fkPrvID, lkpCat.CatNm, lkpSrv.SrvNm, lkpCat.pkCatID,
lkpSrv.pkSrvID

Selected properties of the list box;
ColumnCount = 5
ColumnWidths = 1;1;1;1;1;

The list box displays correctly.

Why do lines indicated in the following error when executed?

"Run-time error '94':
Invalid use of Null"

Test code:

Private Sub lstPrv_DblClick (Cancel As Integer)
'MsgBox Me.lstPrv.Colum n(0)
'MsgBox Me.lstPrv.Colum n(1)
'MsgBox Me.lstPrv.Colum n(2)
'MsgBox Me.lstPrv.Colum n(3) << Results in Null error
MsgBox Me.lstPrv.Colum n(4) << Results in Null error

End Sub

[Hans-Joerg Karpenstein]

Am Tue, 13 Apr 2004 09:16:33 -0700 schrieb Douglas Buchanan:

I cannot access certain column values of a list box using code.

I have a list box 'lstPrv' populated by the query below.

SELECT tblPrv.fkPrvID, lkpCat.CatNm, lkpSrv.SrvNm, lkpCat.pkCatID,
lkpSrv.pkSrvID FROM (lkpCat INNER JOIN lkpSrv ON lkpCat.pkCatID =
lkpSrv.fkCatID) INNER JOIN tblPrv ON lkpSrv.pkSrvID = tblPrv.fkSrvD

These are the columns displayed
tblPrv.fkPrvID, lkpCat.CatNm, lkpSrv.SrvNm, lkpCat.pkCatID,
lkpSrv.pkSrvID

Selected properties of the list box;
ColumnCount = 5
ColumnWidths = 1;1;1;1;1;

The list box displays correctly.

Why do lines indicated in the following error when executed?

"Run-time error '94':
Invalid use of Null"

Test code:

Private Sub lstPrv_DblClick (Cancel As Integer)
'MsgBox Me.lstPrv.Colum n(0)
'MsgBox Me.lstPrv.Colum n(1)
'MsgBox Me.lstPrv.Colum n(2)
'MsgBox Me.lstPrv.Colum n(3) << Results in Null error
MsgBox Me.lstPrv.Colum n(4) << Results in Null error

End Sub

Hello,

are the columns 3 and 4 in your Listbox empty,
if they do so they are really NULL.

You can't use NULL with a string-function.
To avoid this error, check with the "ISNULL" function.

HTH
Karpi
<fluctuat nec mergitur>

[Don Leverton]

Hi Douglas,

I think you need to assign a variable of type Variant in order to accomodate
Null values.
(Message boxes will come up empty if the field is null, but this code should
run without errors.)

Try the code below, which is just a modified version of the help example
from the "ItemsSelec ted" topic.

Note that you can use Debug.Print (as I have commented out below) to view
all of the results in the debug window at once instead of multiple message
boxes.
=============== =============== =========
Private Sub lstPrv_DblClick (Cancel As Integer)
Dim ctl As Control
Dim varItm As Variant, intI As Integer

Set ctl = Me.lstPrv

For Each varItm In ctl.ItemsSelect ed
For intI = 0 To ctl.ColumnCount - 1
'Debug.Print ctl.Column(intI , varItm)
MsgBox ctl.Column(intI , varItm)
Next intI
Next varItm

Set ctl = Nothing

End Sub
=============== =============== =========
HTH,
Don
Douglas Buchanan <db*********@co mcast.net> wrote in message
news:db******** *************** **@posting.goog le.com...

I cannot access certain column values of a list box using code.

I have a list box 'lstPrv' populated by the query below.

SELECT tblPrv.fkPrvID, lkpCat.CatNm, lkpSrv.SrvNm, lkpCat.pkCatID,
lkpSrv.pkSrvID FROM (lkpCat INNER JOIN lkpSrv ON lkpCat.pkCatID =
lkpSrv.fkCatID) INNER JOIN tblPrv ON lkpSrv.pkSrvID = tblPrv.fkSrvD

These are the columns displayed
tblPrv.fkPrvID, lkpCat.CatNm, lkpSrv.SrvNm, lkpCat.pkCatID,
lkpSrv.pkSrvID

Selected properties of the list box;
ColumnCount = 5
ColumnWidths = 1;1;1;1;1;

The list box displays correctly.

Why do lines indicated in the following error when executed?

"Run-time error '94':
Invalid use of Null"

Test code:

Private Sub lstPrv_DblClick (Cancel As Integer)
'MsgBox Me.lstPrv.Colum n(0)
'MsgBox Me.lstPrv.Colum n(1)
'MsgBox Me.lstPrv.Colum n(2)
'MsgBox Me.lstPrv.Colum n(3) << Results in Null error
MsgBox Me.lstPrv.Colum n(4) << Results in Null error

End Sub

[Douglas Buchanan]

Hans

are the columns 3 and 4 in your Listbox empty,
if they do so they are really NULL.
No, colums 3 and 4 are not empty! That is why I am so mystified.
In fact every row dispalys all columns. In fact the reason these
records *are displayed* is because those values are present.

I can see the values but I cannot reach them with code! Does anyone
have an explaination for that?

Douglas

Hans-Joerg Karpenstein <hj***********@ web.de> wrote in message news:<pa******* *************** ******@web.de>. .. Am Tue, 13 Apr 2004 09:16:33 -0700 schrieb Douglas Buchanan:

I cannot access certain column values of a list box using code.

I have a list box 'lstPrv' populated by the query below.

SELECT tblPrv.fkPrvID, lkpCat.CatNm, lkpSrv.SrvNm, lkpCat.pkCatID,
lkpSrv.pkSrvID FROM (lkpCat INNER JOIN lkpSrv ON lkpCat.pkCatID =
lkpSrv.fkCatID) INNER JOIN tblPrv ON lkpSrv.pkSrvID = tblPrv.fkSrvD

These are the columns displayed
tblPrv.fkPrvID, lkpCat.CatNm, lkpSrv.SrvNm, lkpCat.pkCatID,
lkpSrv.pkSrvID

Selected properties of the list box;
ColumnCount = 5
ColumnWidths = 1;1;1;1;1;

The list box displays correctly.

Why do lines indicated in the following error when executed?

"Run-time error '94':
Invalid use of Null"

Test code:

Private Sub lstPrv_DblClick (Cancel As Integer)
'MsgBox Me.lstPrv.Colum n(0)
'MsgBox Me.lstPrv.Colum n(1)
'MsgBox Me.lstPrv.Colum n(2)
'MsgBox Me.lstPrv.Colum n(3) << Results in Null error
MsgBox Me.lstPrv.Colum n(4) << Results in Null error

End Sub

Hello,

are the columns 3 and 4 in your Listbox empty,
if they do so they are really NULL.

You can't use NULL with a string-function.
To avoid this error, check with the "ISNULL" function.

HTH
Karpi
<fluctuat nec mergitur>

[Douglas Buchanan]

Don,

Your code works, but what's wrong with this code?

Private Sub lstPrv_DblClick (Cancel As Integer)
MsgBox Me.lstPrv.Colum n(3)
End Sub

I've never before had to create an object and use a set statement to
access a column value in the past. What's so special this time?

There are *no* null values in the list box! The presence of columns 3
and 4 are part of queries where statement - they display in the list
box - they aren't null.

What is wrong with "MsgBox Me.lstPrv.Colum n(3)"?

Am I missing something?

Douglas

"Don Leverton" <My*****@Telus. Net> wrote in message news:<q%Zec.265 $aD.12@edtnps89 >...

Hi Douglas,

I think you need to assign a variable of type Variant in order to accomodate
Null values.
(Message boxes will come up empty if the field is null, but this code should
run without errors.)

Try the code below, which is just a modified version of the help example
from the "ItemsSelec ted" topic.

Note that you can use Debug.Print (as I have commented out below) to view
all of the results in the debug window at once instead of multiple message
boxes.
=============== =============== =========
Private Sub lstPrv_DblClick (Cancel As Integer)
Dim ctl As Control
Dim varItm As Variant, intI As Integer

Set ctl = Me.lstPrv

For Each varItm In ctl.ItemsSelect ed
For intI = 0 To ctl.ColumnCount - 1
'Debug.Print ctl.Column(intI , varItm)
MsgBox ctl.Column(intI , varItm)
Next intI
Next varItm

Set ctl = Nothing

End Sub
=============== =============== =========
HTH,
Don
Douglas Buchanan <db*********@co mcast.net> wrote in message
news:db******** *************** **@posting.goog le.com...

I cannot access certain column values of a list box using code.

I have a list box 'lstPrv' populated by the query below.

SELECT tblPrv.fkPrvID, lkpCat.CatNm, lkpSrv.SrvNm, lkpCat.pkCatID,
lkpSrv.pkSrvID FROM (lkpCat INNER JOIN lkpSrv ON lkpCat.pkCatID =
lkpSrv.fkCatID) INNER JOIN tblPrv ON lkpSrv.pkSrvID = tblPrv.fkSrvD

These are the columns displayed
tblPrv.fkPrvID, lkpCat.CatNm, lkpSrv.SrvNm, lkpCat.pkCatID,
lkpSrv.pkSrvID

Selected properties of the list box;
ColumnCount = 5
ColumnWidths = 1;1;1;1;1;

The list box displays correctly.

Why do lines indicated in the following error when executed?

"Run-time error '94':
Invalid use of Null"

Test code:

Private Sub lstPrv_DblClick (Cancel As Integer)
'MsgBox Me.lstPrv.Colum n(0)
'MsgBox Me.lstPrv.Colum n(1)
'MsgBox Me.lstPrv.Colum n(2)
'MsgBox Me.lstPrv.Colum n(3) << Results in Null error
MsgBox Me.lstPrv.Colum n(4) << Results in Null error

End Sub

[Hans-Joerg Karpenstein]

Am Tue, 13 Apr 2004 21:14:13 -0700 schrieb Douglas Buchanan:

Hi,

the column property has an optional parameter row.
Maybe you have to use this parameter.
Also check in the help for the selected property.

HTH
Karpi
<fluctuat nec mergitur>

Hans

are the columns 3 and 4 in your Listbox empty,
if they do so they are really NULL.

No, colums 3 and 4 are not empty! That is why I am so mystified.
In fact every row dispalys all columns. In fact the reason these
records *are displayed* is because those values are present.

I can see the values but I cannot reach them with code! Does anyone
have an explaination for that?

Douglas

Hans-Joerg Karpenstein <hj***********@ web.de> wrote in message news:<pa******* *************** ******@web.de>. ..

Am Tue, 13 Apr 2004 09:16:33 -0700 schrieb Douglas Buchanan:

> I cannot access certain column values of a list box using code.
>
> I have a list box 'lstPrv' populated by the query below.
>
> SELECT tblPrv.fkPrvID, lkpCat.CatNm, lkpSrv.SrvNm, lkpCat.pkCatID,
> lkpSrv.pkSrvID FROM (lkpCat INNER JOIN lkpSrv ON lkpCat.pkCatID =
> lkpSrv.fkCatID) INNER JOIN tblPrv ON lkpSrv.pkSrvID = tblPrv.fkSrvD
>
> These are the columns displayed
> tblPrv.fkPrvID, lkpCat.CatNm, lkpSrv.SrvNm, lkpCat.pkCatID,
> lkpSrv.pkSrvID
>
> Selected properties of the list box;
> ColumnCount = 5
> ColumnWidths = 1;1;1;1;1;
>
> The list box displays correctly.
>
> Why do lines indicated in the following error when executed?
>
> "Run-time error '94':
> Invalid use of Null"
>
> Test code:
>
> Private Sub lstPrv_DblClick (Cancel As Integer)
> 'MsgBox Me.lstPrv.Colum n(0)
> 'MsgBox Me.lstPrv.Colum n(1)
> 'MsgBox Me.lstPrv.Colum n(2)
> 'MsgBox Me.lstPrv.Colum n(3) << Results in Null error
> MsgBox Me.lstPrv.Colum n(4) << Results in Null error
>
> End Sub

Hello,

are the columns 3 and 4 in your Listbox empty,
if they do so they are really NULL.

You can't use NULL with a string-function.
To avoid this error, check with the "ISNULL" function.

HTH
Karpi
<fluctuat nec mergitur>

[Lyle Fairfield]

db*********@com cast.net (Douglas Buchanan) wrote in
news:db******** *************** **@posting.goog le.com:

I cannot access certain column values of a list box using code.

I have a list box 'lstPrv' populated by the query below.

SELECT tblPrv.fkPrvID, lkpCat.CatNm, lkpSrv.SrvNm, lkpCat.pkCatID,
lkpSrv.pkSrvID FROM (lkpCat INNER JOIN lkpSrv ON lkpCat.pkCatID =
lkpSrv.fkCatID) INNER JOIN tblPrv ON lkpSrv.pkSrvID = tblPrv.fkSrvD

These are the columns displayed
tblPrv.fkPrvID, lkpCat.CatNm, lkpSrv.SrvNm, lkpCat.pkCatID,
lkpSrv.pkSrvID

Selected properties of the list box;
ColumnCount = 5
ColumnWidths = 1;1;1;1;1;

The list box displays correctly.

Why do lines indicated in the following error when executed?

"Run-time error '94':
Invalid use of Null"

Test code:

Private Sub lstPrv_DblClick (Cancel As Integer)
'MsgBox Me.lstPrv.Colum n(0)
'MsgBox Me.lstPrv.Colum n(1)
'MsgBox Me.lstPrv.Colum n(2)
'MsgBox Me.lstPrv.Colum n(3) << Results in Null error
MsgBox Me.lstPrv.Colum n(4) << Results in Null error

End Sub

My recollection is:

1. all columns from list or combo boxes are either strings or nulls,
regardless of the field type which underlies them;
2. msgbox errs on trying to display a null.

So I suggest that you use some variation of the null to zero function such
as:

msgbox Nz(Me.lstPrv.Co lumn(3),"null" or "0" or "" or whatever)

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Don Leverton]

Hi Douglas,

IMHO, it's kinda like the Cadbury secret. :)

Seriously, I'm no expert, but I suspect that it has to do with ambiguity.
You're asking the code to return the contents of the 4th column, but you're
not specifying from which ROW.
Using the ItemsSelected Property says "give me the contents of the selected
row" (or rows if you have a multi-select list-box setup. The code that I
gave you will also handle multi-selection, BTW.)

As far as the "Set" thing goes ... that just gives you access to all of the
control's properties and saves a whole lot of typing.
-------------------------------------------------
You *may* have been able to do it like this instead:

For Each varItm In Me.lstPrv.Items Selected
For intI = 0 To Me.lstPrv.Colum nCount - 1
'Debug.Print Me.lstPrv.Colum n(intI, varItm)
MsgBox Me.lstPrv.Colum n(intI, varItm)
Next intI
Next varItm
-------------------------------------------------
Or *maybe* even like this:

With Me.lstPrv

For Each varItm In .ItemsSelected
For intI = 0 To .ColumnCount - 1
'Debug.Print .Column(intI, varItm)
MsgBox .Column(intI, varItm)
Next intI
Next varItm

End With
-------------------------------------------------

But I do think the Set method would be the preferred method.

HTH,
Don
=============== =============== ===============

Douglas Buchanan <db*********@co mcast.net> wrote in message
news:db******** *************** **@posting.goog le.com...

Don,

Your code works, but what's wrong with this code?

Private Sub lstPrv_DblClick (Cancel As Integer)
MsgBox Me.lstPrv.Colum n(3)
End Sub

I've never before had to create an object and use a set statement to
access a column value in the past. What's so special this time?

There are *no* null values in the list box! The presence of columns 3
and 4 are part of queries where statement - they display in the list
box - they aren't null.

What is wrong with "MsgBox Me.lstPrv.Colum n(3)"?

Am I missing something?

Douglas

<Previous threads snipped>