List Box Error

[The Professor]

Hi All,



I have a Listbox control on a pop-up Form (dialog mode), which by default has it's RowSource a Select Query (RowSource Type: Table/Query). When activated the dialog pops up nicely and displays the correct data as designed.

The Form also has an contains an Option Group, with two options. Depending on which option the user chooses, the data displayed in the ListBox changes. This is managed in code via two SQL strings. This seems to work well.

Option one is the default, and the RowSource is set when the Form loads, with the following SQL string:
"SELECT BookingID, CustCompany, CustID FROM qrySearchInvoice ORDER BY BookingID"

When the user selects option two the RowSourcec is set to the following SQL string:
"SELECT CustCompany, CustID, BookingID FROM qrySearchInvoice ORDER BY CustCompany"

Choosing and switching between the two options works well and the ListBox requeries nicely and displays the correct data, correctly ordered. The ListBox is set to Bound Column 1, in the Properties Sheet, not in code. The ListBox is requeried after each SQL string is assigned to the RowSource.

When the ListBox displays option one data, and the user selects a row, all is well. However, if option two data is displayed, as soon as the user selects a row, it triggers an Error Message which says:

"The Value you entered isn't valid for the Field. You may have entered text in a numeric field, or a number that is larger than the FieldSize permits"

Whilst I understand what the Error Message means, I don't know what is causing it, or how to get rid of it. The selected Fields in the SQL have the following data types:

BookingID = Long
CustCompany = String
CustID = Long.

Bound Column is 1 in each case. The Error message is probably being triggered by CustCompany being datatype String and not a Long. But I have no idea what to do about it. Help please, anyone?

Prof.

[NTC]

dynamically replacing a row/record source is a design concept I generally avoid - for the reasons you are now discovering. The simple solution is of course to have 2 list boxes, one for selection by Book ID and the other for selection by Customer..... but then I also avoid pop ups entirely and put all selectors in the header of the main form...beauty is in the eye of the beholder ....

your issue is the bound field changing from ID (number) to customer (string)....you need to keep the bound field the same field in both cases. This fact in turn impacts the display and so then you must also control the field widths dynamically.

[ranman256]

I ALWAYS set listbox bound col= 1. Always have the 1st column = index field.
That way theres never a misunderstanding what it is pulling. (indexs are best)

Now, you may not always want to see the index , so you can set the col 1 length = 0. Nobody cares what the index is, they just want to see 'John SMith' and pick John Smith.
Try this method.

[amrut]

as soon as the user selects a row, it triggers an Error Message which says:

Can you post the code that executes when you select a listbox item ?

[The Professor]

Hi

Many thanks for all your help.

Yes, Ranman, good idea and have done (more or less) what you said. I had been putting the Fields I wanted into Bound Column 1, because that was the data I needed. But, you got me thinking I don't have to do that, because of course I can access data from any column using the column(n) index. That way I can keep the datatype of column 1 the same in each SQL string - thus avoiding the datatype error - and simply hide columns I don't need. I hadn't thought of hiding a bound column, but of course you can. Now it's all working very well, and - even if I say so myself - it's actually quite slick.

[The Professor]

Can you post the code that executes when you select a listbox item ?

The only code I have behind the ListBox was in the AfterUpdate event, but the error was occurring before that code ran, so the problem I had was, there was no code to debug, as such. The error was occurring as a result of a 'design flaw', you might say, rather than faulty code. I needed a slightly different approach, which is what I've now done.

Prof.