Strangest thing I have seen in VBA (1 Viewer)

[MajP]

This one makes no sense to me. I am looping a multiselect listbox. Everything works fine except for the very first time after opening the form you click into the listbox. It was returning null in this line of code
val = (ctrl.Column(TheColumn, ctrl.ItemsSelected(I)))
However the arguments all return legit values. So the column property had proper inputs, but still returned NULL. After several different approaches I mistakenly left in some code where I called the same line of code a second time and it worked. Not sure how that is even possible. Anyone seen anything like this? Or how this is possible.

For I = 0 To ctrl.ItemsSelected.Count - 1
     val = (ctrl.Column(TheColumn, ctrl.ItemsSelected(I)))
       If IsNull(val) Then
            debug.print "Null"  'verify NULL
           'call same code again and it returns proper value
           val = (ctrl.Column(TheColumn, ctrl.ItemsSelected(I)))
       End If
   ...other code
  Next I

 

[theDBguy]

Hi. I have no idea... Probably not, but would naming the variable "val" have anything to do with it? Just a thought...

 

[The_Doc_Man]

True, theDBguy... Val IS the name of a function.

Question, MajP... did you originally detect this via single-stepping? And then add the "If IsNull" test later?

Because the IF ISNULL(VAL) is CERTAINLY ambiguous because of the previous line. Remember that often Access will allow you to leave out trailing parentheses when you have empty arguments. I'm not saying I know the mechanism of this problem, I'm just speculating.

 

[MajP]

did you originally detect this via single-stepping?

No did not even have to do it because it was throwing an error because Val is dimensioned a string.
I think this issue is similar to the record set property. Not always but depending on how your combo or listbox is set up, when you initially enter the combo or list the recordset is Nothing. Only when you click into the control is the recordset set. I am assuming this is to conserve resources. However, I checked this and in this case the recordset is set. If I do a requery the code works. So My guess is when you call the column property something has to be set. When you first enter the Multi select that thing is not set.

Having arguments or local variables the same as function names will never fail. I could call a local variable Date, Field, Table, Instr and have no problems ever. I think the only thing I cannot use vba code definitions like dim integer as integer, or Dim For as String, Dim With as string.

 

[Galaxiom]

Remember that often Access will allow you to leave out trailing parentheses when you have empty arguments.

Really? Unmatched parentheses?

 

[Galaxiom]

Having arguments or local variables the same as function names will never fail. I could call a local variable Date, Field, Table, Instr and have no problems ever.

Maybe so but not nice for someone following your code.

 

[The_Doc_Man]

No. BOTH parentheses. Trailing pairs of otherwise empty parentheses.

Date and Date() both have worked for me. Similarly Now and Now(). If it works for those functions, it can work (syntactically, at least) for other functions.

 

[MajP]

@The_Doc_Man
Here is another one of the misunderstood vba nuances. It is like Me. and Me! where people say things and it is just wrong. Here is another one where most vba "experts" get it wrong.

What do you mean 'cannot use parentheses?'

docs.microsoft.com

You need to read it real carefully because it is pretty subtle.

 

[The_Doc_Man]

When you first enter the Multi select that thing is not set.

All Access code that we can write will be a sub or function activated by either a RunCode macro or an event, because Access is the Main segment so we can't run our own code or specify the starting address of the EXE file. We have to wait until Access calls us. A list box is involved so a simple RunCode macro is less likely. Therefore, your code is in the context of an event of some kind. Which one?

If this is happening at Form_Open then I could believe the combo box isn't ready. However, at least from my myopic understanding of event flow, unless you have late-bound or dynamically rebound the .Recordsource for the list box, it should be populated by the time Access fires the Form_Load event because at that point all controls are available. If it is even later, after Form_Current, then ALL controls that were bound should be "good to go."

 

[MajP]

Therefore, your code is in the context of an event of some kind. Which one?

It is in the after update event.

 

[The_Doc_Man]

My thoughts on this problem are that it is one of:

1. Something is getting confused by use of a variable that has the same name as a function. You claim this isn't a problem. I will NOT call you a liar but I am skeptical because we have seen issues crop up before based on this principle.
2. Something is getting confused by the syntax because of variable that matches a function name in a context where it would return null.
3. Due to event timing, this code must be in an event prior to Form_Current and maybe before Form_Load
4. You've found a real bug

Playing the odds, #4 is least likely simply because I would think that the syntax/semantics portion of the compiler would have been pretty well tested by now. There is a hair of difference between #1 and #2. I'm actually thinking #3 and waiting for your answer to the "which event" question.

 

[The_Doc_Man]

Wow, AfterUpdate certain IS later than Form_Current. But now there is the question of whether the .RowSource of the list is sensitive to something in the .RecordSource of the form, because at that point after an update but before the next Form_Current, the underlying recordset gets changed and MS has not bestowed upon us the knowledge of that exact sequence. So IF there is a dependency between the .RowSource and the .RecordSource, you might expect to see some things changing. Which would make this a race condition. Your first "read" loses the race but the next one wins it.

 

[CJ_London]

don't suppose you have column headings set to true and the heading for that column is not specified? though would think val would remain as null

Also don't understand - you say val is dimmed as string - but the statement val=null will provoke an 'invalid use of null' error, so not sure how your isnull is working

and the code

dim val as string
debug.print isnull(val) >> shows false

 

[apr pillai]

For I = 0 To ctrl.ItemsSelected.Count - 1 val = (ctrl.Column(TheColumn, ctrl.ItemsSelected(I))) If IsNull(val) Then debug.print "Null" 'verify NULL 'call same code again and it returns proper value val = (ctrl.Column(TheColumn, ctrl.ItemsSelected(I))) End If ...other code Next I

For I = 0 To ctrl.ItemsSelected.Count - 1
     val = ctrl.Column(TheColumn, I)
       If IsNull(val) Then
            Debug.Print "Null"  'verify NULL
           'call same code again and it returns proper value
           'val = (ctrl.Column(TheColumn, ctrl.ItemsSelected(I)))
       Else
          Debug.Print "Val: " & val
       End If
   '...other code
  Next I

 

[MajP]

@CJ_London
Thanks sorry for the confusion

Also don't understand - you say val is dimmed as string - but the statement val=null will provoke an 'invalid use of null' error, so not sure how your isnull is working

Originally it was a string and since it was failing changed it to a variant. But no column heading.

@apr pillai Thanks, but that would not help. The code that you commented out is the necessary code to make it work. My Only question is why?

@The_Doc_Man
My guess is you are right it is some kind of race condition. I tried doevents but no help. I will try to put a delay and see if that does anything.

 

[CJ_London]

since it was failing changed it to a null.

you mean changed it to a variant?

 

[MajP]

you mean changed it to a variant?

Oops. Yes, fixed it. Thanks.

 

[apr pillai]

'val = (ctrl.Column(TheColumn, ctrl.ItemsSelected(I)))
The above commented out statement also can be corrected as below:

val = ctrl.Column(TheColumn,I)

Since "I" already represents the selected ListBox Item, it is not necessary to repeat ctrl.ItemsSelected(I) as second parameter. It works either way. Even though val is a built-in function it doesn't trigger any error in Access2007.

Yah, I declared val as Variant, instead of String. I tried after declaring val as String as well, but there was no error.

May be I didn't get your point correctly, val variable never runs into Null if there are Selected Items in the ListBox. If not the For . . . Next Loop never executes. I am not ruling out a Validation check after the first read attempt altogether but a second attempt to get the selected item points to logical error..