Combo box not showing row source values

[ehsguy77]

I've made a combo box with a row source query that is 42 columns of information. I need this information available as default values for several fields within the form (which I've done with an afterupdate event code).

Problem is, the values show up initially in the drop-down (only 1 bound column, 1 visible, but 42 set as column count), then after saving, closing, and re-opening, the values are no longer available.

Does Access have a problem with handling more than a certain number of columns in a row source query for a combo box? Does anyone know a solution?

 

[scriverb]

The additional field/column values can be obtained by referring to the combobox's .column(x) property. The total column count is a direct count of the columns but they are referred to in this property starting with .column(0) being the first column.

You should set your column widths to only show the data that you want to be visible when the dropdown is displayed. (i.e. 0;2;0;0;0; will only show the second column of data). The Bound Column property identifies that value of the combobox after a selection is made. In the above example if column 1 is the AutoNumber field for the table being displayed it may be that is the data that you want the combobox to be bound to. So, set the Bound Column to 1. But, the second column will be the only information displayed in the dropdown as well as the combobox when it doesn't have the focus. Other information can be assigned to other textboxes by the following example:

Me.Text1 = Me.Combo1.column(2)
Me.Text2 = Me.Combo1.column(3)

This code assigns the values from column 3 and 4 from the selected record in the combobox to the two text boxes.

Post back if you have any further questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[docmeizie]

Why do you need all those columns for the combo box? A good rule of thumb is to only use what you need when building a record source for a control. I think that you might want to build a query for that control using the query builder(the ellipse next to the record source line). Only add the fields that you definitely need to show in the combobox and fields that narrow your information down using the criteria field. All the other riff-raff keep off the query. Then run a test with the red exclamation button to see that it shows all your information related to that combo date.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv

 

[ehsguy77]

Thanks for the suggestions. To Bob, the steps you posted are exactly what I've done - thanks anyway.

To docmeizie, I'm using the values from my row select query as default values in another table of information. The form I'm making is to make waste profiles, which are based on a table of general waste information. When someone selects the general waste code from the dropdown box, it will default most information into the profile, and the user will have the ability to change any of the default info to customize the profile. My forms get weird and inconsistent when I have 40 or more columns. Sometimes it will give me the values, sometimes not. Sometimes the whole combo box will disappear! If there is a better way to do this, I'd love to know how.