List column(x) reads null

[edson]

Good evening! Hello from Brazil to you all !!!

OVERVIEW
I have attached a small db to illustrate my problem.
TblAtzNor is a price table. I can input prices today to be effective today or at any future date, meaning the ordinary keys will not necessarily match the ascending effective dates. This table is the data source for the FrmBuscaPrNor.
FrmBuscaPrNor will be triggered thru the FrmCadastro, so if I select Price Type "NORMAL" from the combo in the FrmCadastro then the FrmBuscaPrNor will pop up showing a price list. I want to click in that price list to pick a price based on its Effective Date. The picked price should load my FrmCadastro. However, I want to warn the user when he does not pick the current price.
Keep in mind that my price list does not show future effective date prices. In addition, current price can be from a month ago (still current). That is the reason why I based my instruction of the Private Sub LstPrNor_Click() event on DMax effective date and not on DLast input date.

THE PROBLEM


In the Private Sub LstPrNor_Click() event the system does not get into the first leg of the "If" instruction because it reads null for Me.LstPrNor.Column(6). As a result I get the warning in all cases, in spite of the user choice. I don't have a clue on how to make it work. I do not understand why it reads null in the Me.LstPrNor.Column(6).
Why is it so tricky? What is wrong, please? Thank you.

[pbaldy]

There are only 6 fields in the listbox row source. The column property is zero based, so Column(6) refers to a non-existent 7th field.

[edson]

Hello Paul. Thank you for your prompt reply.
You are partially right. I forgot to include the 7th column when I prepared the example. But, please note that even adding the 7th column (NORHVIG) in the List source data query (marked as not to show) the problem remains, e.g., the very first leg of the IF does not read and the warning message remains in spite of the price picking. Did you find any sintax / logic error in my code?
Thanks

[pbaldy]

Let's clarify, you want it to show in the row source query, you hide it in the listbox by using the column widths property of the listbox. Is that how you did it?

[pbaldy]

And I'm never partially right. I'm always completely right...except when I'm completely wrong.

[edson]

And I'm never partially right. I'm always completely right...except when I'm completely wrong.

That is a good way to put it, eh, eh, eh... I like your sense of humor.
I am attaching a new version of the db example where I fixed the list quantity of columns.
Please check the list data source query. It has now the 7th column which is marked to be hidden when you run the query. Also, the list has the 7th column lenth property set as zero to not show in the list.... but it is there for processing purposes. Meaning I do not need to see NORDTVIG in the list but its value should be considered in my instruction.
So you'll verify that even with the 7th column in place (NORHVIG) the problem remains, e.g., the warning message will pop up in spite of the price you select, which is wrong. I need to warn the user only when he picks an old price. If he picks the current price (the one which matches Max NORDTVIG <= Current Date) no warning message should pop up.
I believed my code would do that but it does not... that is why I posted this thread. Is it clear enough now? I apologize for my foreign English... It is not native and maybe it is not clear enough.
Thanks
Edson

[pbaldy]

It's me who's not being clear apparently. You need to check the "Show" box in the row source query for that field. Otherwise it is not available to your code. If you want to hide it in the listbox, set the list width for that column to 0, as you have for others.

[edson]

WOW!!! Amazing! It worked! I am so glad to have learned this basic lesson. I really had in mind that every field of a query would be available for the code even if marked as "not show". I thought it would be kept in the Access "memory"... and you have demonstrated that it is not true. Thank you Paul for being patient with me. I'll mark this thread as solved. Have a blessed day and a nice weekend.
Edson

[pbaldy]

Happy to help Edson! Only fields in the SELECT clause are returned by the query, and un-clicking Show removes a field from the SELECT clause.