Combobox column number qwerky

[Guest]

I'm working with a combo box on a form in Access 2000, and trying to pick up
the value of the third column (actually column number 2, ie. 0, 1, 2) using
the following code:

Dim PWSN As Integer
PWSN = Me.cboSNum.Column(2)

When the code runs, I get a run-time error '94': Invalid use of Null.
However, while I'm in debug mode, if I change the column number to 0 or 1
(these are text values in the combo box), I get the error stating a type
mismatch (which is correct). Continuing while I'm still in debug mode, I
change the column number back to 2 (after the type mismatch error), suddenly
the code works and picks up the Integer value I want and works fine.
Unfortunately, when I run the code outside of debug mode I get the "Invalid
use of Null" error. In case you are curious, the third column has an Integer
value for each item in the combo box, it just won't pick it up unless I trick
it this way.

In the past I have noticed that when I try to pick up column values higher
than column 1, I have trouble. Is this some kind of a qwerky bug within
Access or am I doing something wrong?

TIA,
Al

 

[Guest]

It sounds like you are hitting some instances where the 3rd column does not
have an integer, but a Null value. There are a couple of things you can try.

Dim PWSN As Variant
This way, if it gets a Null, it wont get the error; however, you will have
to test for it so it doesn't cause problems down stream.

Or
PWSN = Nz(Me.cboSNum.Column(2),0)
This way, if PWSN is Null, it will be converted to 0. You could change the
0 in the Nz function to anything you want that is the correct data type for
PWSN

 

[Guest]

Thank you for your response; however, as I stated in my original question,
the 3rd column doesn't contain any Null values. It is a key field that has
an Integer for every record in the table. "cboSNum" is an unbound combo box
with a query for a rowsource that does work. And column(2) does get
populated with the correct Integer when I trick it using the method I
specified in debug mode. I'm just trying to grab this value on the Change
event of the combo box.

Thanks,
Al

 

[Guest]

I know what you said; however, we all sometimes think we have covered all
bases only to find we missed a small detail. The symptom points to a Null
value (Like when you get an Error 94, for example), so it was meant as a
friendly reminder you may want to verify all your entries do have value. But,

Ah, the Change event!
Is it necessary to use that event? Are you aware it fires for every
keystroke?
Between the Change event firing and the AutoExpand, it is possible it it
tripping over it's own feet. In other words, a timing issue. When you are
running in Debug mode, things happen one at a time and have time to complete.

Unless there is some reason the fire an event after every keystroke, I would
suggest trying to use the After Update or Before Update events instead,
depending on what you are trying to accomplish.

 

[Guest]

Yes, that was it. The On Change event was the wrong place to have it firing.
I moved it to the AfterUpdate event and it works fine now. Thank You!
Al