Curious listbox behavior after requery

[platedslicer]

This is one of those problems that appear for apparently no good reason.

Essentially, I have a listbox with a _Click() sub that fills some text boxes and other controls with details from the selected register. The primary key to the register is in the hidden last column of the list box.

What happens is, in the first click after a requery, the last column of the listbox returns NULL, when it's not null at all. In fact, if you simply click "End" at the error popup and click the listbox again, it works like a charm.

For the moment it's been patched with the following code:

Dim gayvba As Variant

If IsNull(ListItems.Column(ListItems.ColumnCount - 1)) Then
On Error Resume Next
gayvba = ListItems.Column(ListItems.ColumnCount - 1)
End If

Works fine from there.

Is there a reason why this happens, and a way to fix it that doesn't involve jury rigging the code?

Thanks in advance.

 

[boblarson]

This doesn't quite make sense. You have code that says if it is null to then set the value to that column. Shouldn't it be this instead?

Dim gayvba As Variant

If [B][COLOR=red]Not [/COLOR][/B]IsNull(ListItems.Column(ListItems.ColumnCount - 1)) Then
   gayvba = ListItems.Column(ListItems.ColumnCount - 1)
End If

And the use of On Error Resume Next is not the best because it can obscure real errors.

 

[boblarson]

And actually I would use LEN instead of Is Null because it may not be null but be an empty string.

So,

Dim gayvba As Variant

If [B][COLOR=red]Len([/COLOR][/B]Me.ListItems.Column(Me.ListItems.ColumnCount - 1) [B][COLOR=red]& vbNullString) > 0[/COLOR][/B] Then
On Error Resume Next
gayvba = Me.ListItems.Column(Me.ListItems.ColumnCount - 1)
End If

And I would use Me. with the listbox name to help Access along because sometimes it does get messed up and by doing Me. it helps with making sure it is explicit.

 

[platedslicer]

The unexplained error I'm getting is a NULL value in the last column that shouldn't be there. It's actually not null, because simply ignoring the error and clicking on the listbox again returns the right value in that column.

So the code I posted was just trying to get the null value out of the way - an equivalent to clicking "End" on the error popup that I was getting when trying to assign the null value to the SQL string.

In fact, I just figured that the "colorfully" named variable and assigning the listbox null value to it doesn't make any difference. It's the "on error resume next" doing all the work. Strangely enough, it fills up the detail fields just fine, so the query is running and receiving the right primary key value.

"This column is NULL... nope, not really. Just trollin' ya."

 

[boblarson]

Again, the use of NULL in checking is something I like to avoid if possible. I use the

If Len([FieldOrControlNameHere] & vbNullString) > 0 Then

which deals with nulls and empty strings. Much more efficient that using the Is Null especially when it may be blank, but not null. So did you try my fix?