Listbox with Bound Second Column

[GrexP]

This is an odd one for me. I’ve programmed in VB for years and I’m making the transition to Access. The VB ListBox does not have columns like the Access one.

I have an Access form with no control source or record selector. There is a ListBox with 2 columns and a query that is the control source. The first column is First and Last Name and the second column is an integer value. The integer is from an auto-number field. The second column with the integer is bound.

When a user clicks on an item in the listbox I use the value from the bound column to do some DlookUps to fill some Labels. There is other stuff going on on the form but nothing else interacts with the ListBox until a Save button is clicked.

When the Save button is clicked data from other controls is written to a table. A value from the same bound column from the listbox is stored in the this table along with the data from the other controls.

Here’s the odd thing.

When the form loads and the first DlookUps happen I get an error because it is getting info from the unbound first column. Instead of doing the search on the integer from the bound second column, it uses the value from the first column. This causes a type mismatch and this only happens on start-up. All other Dlookups are fine after that.

Also, when I close the form, what ever was the first item clicked on in the list box, the integer value is written to the name field. If I just load the form and click a few times on the list box and then close, the next time I open it, the first name is gone and there is a number in it’s place.

John Smith – 46 becomes 46 – 46.

No where in code am I writing to that table. Also, all properties for the listbox (e.g. control source, bound column, etc) are set at design time. It seems to think that the first column is bound for the initial start-up and then it switches to the second column.

What the heck is going on!

 

[Pat Hartman]

A listbox or combobox has only ONE bound column. Typically, the bound column will be the first column which will be the unique indentifier and it will be hidden. When referring to a listbox or combo, you can refer to the bound column like this:
Me.MyListbox
Me.MyListbox.Value

If you want to refer to a column other than the bound column, you must use the column property:
Me.MyListbox.Column(2) <--- the columns are a 0-based array so (2) is actually the third column in the listbox's RowSource.

 

[GrexP]

Thanks Pat. Maybe accessing the Column() property would be the way to go. As it is I had to re-write the code so it was not a bound list box.

This begs the question though, Why does Access let me bind the second column if it is not reliable. To repeat what I wrote earlier, everything worked fine except on start-up. Once the form loaded, using the 2nd column as the bound column worked fine.

Very frustrating!

 

[Pat Hartman]

I always bind the first column and hide it if necessary so I haven't run into this issue. You do need to be careful with where you put code that expects to find data. Subforms actually run their events before the main form so to avoid certain types of messages, you need to add error trapping so you can ignore some errors.

 

[GrexP]

I think this may be a Form event issue. I had code to access the bound column in the Form_Load event, but maybe it should be in the Form_Activate event.

As I said, I've already re-written the code, and the ListBox is no longer bound, so I can't test this. It seems what was happening was the ListBox bound the first column by default and then after the form loaded it read the property and bound the second column as I wanted.

If you ask me that's a bug in VBA. If I set the second column as the bound column at design time, the program should not let me access the bound column until it is properly bound. I would consider that bad programming if I had written the underlying VBA code that way.

I've noticed a lot of odd things like this concerning Access VBA. I think the thing that bothers me the most about the differences between Acccess VBA and pure VB Code is the fact that you can't access a control's property unless it has the focus. In VB you can access any controls properties, at any time on any form in the project. It will never generate an error.

 

[Pat Hartman]

The .text property in VB is not the same as the .text property in VBA. I didn't write the languages and I think that there is no excuse for anomolies like this. In VBA, the .text property is only available when the control has the focus so it is rarely used. You would only use it if you wanted to work in the on Change event for example, where you were doing something with each character as it was typed. In ALL other cases, you would use the .value property which is also the default property. So if you just refer to the control as Me.MyControl, that is exactly the same as Me.MyControl.value.

It is possible that your code belongs in the form's Current event. The Open and Load events only run a single time - when the form is opened wheras the Current event runs each time the record pointer moves to a new record.

 

[GrexP]

Thanks for the info on the Text property. I will test it, but I assume that means that I don't need to set the focus to the control to be able to read the .Value property.

These little things have been driving me batty over the last few months. A lot of it seems to have to do with the fact that Access VBA is focused on DB programming, as it should be. It works from the standpoint that controls will be bound to a records source. Unfortunately for me, that's not how I think as a programmer, so it's causing me a lot of problems.

I'll never forget three months ago when I sat down to write my first Access application and I couldn't figure out how to clear a ListBox of its contents. In VB it would be MyListBox.Clear. It took me 20 minutes to figure out that in Access it was MyListBox.RecordSource = ""

Very frustrating.

 

[Pat Hartman]

Yes, there is a definite difference when working with bound forms/reports. You need to readjust your thinking so that you don't immediately jump into code. The reason that Access is such a great RAD tool is because you can do so much without code Make coding your last option. Start with property settings, then queries, and finally code.