Help with Data Entry ComboBox (LimitToList)

[frank]

Hello All,
I have a data entry form with a combo box. I would like a User to
select an existing value or enter a new value.
When I try to set LimitToList = No, I get the Error:

"Microsoft Access can't set the LimitToList property to No right now,
The first visible column, which is set by the ColumnWidths property,
isn't equal to the bound column."

I tried making the ColumnWidths larger and the fields size smaller,
but that did not help.
I have done this in the past and I can't remember having this problem.
All help is welcome. Thanks

 

[Tom van Stiphout]

On Tue, 6 Oct 2009 17:54:30 -0700 (PDT), frank

Here is the translation of that error message:
Take the first visible column. Remember that if the ColumnWidth is set
to 0 it is not visible. So if for example you have 3 columns and your
widths are 0;1;1 then the second column is the first visible one.
Now compare that column with the one in the bound column. With that we
mean the ControlSource property. The error indicates the two are not
the same. For example the query for this combobox may return
CustomerID, CustomerName, CustomerCountry. Since you set the widths to
0;1;1 the CustomerName column is the first visible one. If your
ControlSource references CustomerID or any other field, the error
would be triggered.

-Tom.
Microsoft Access MVP

 

[Frank H]

In addition then, what can you do about it?
The Lookup Wizard will create a combo box like this; what the user sees in
the combo box (in column 2) is not what is actually being stored in the
table, since the 0 width, column 1 is the bound column whose value is
actually being entered into the table.
The common technique is to use the NotInList event, which gets triggered
when user types a value into the combo box. Typically, you cause this event
to bring up a different form, which allows the user to create the new record
you desire. When the user is done entering the new record, you cause the
closing of that form to requery the combo box, allowing the new entry to show
in the combo box's list. There are a lot of fine points to this approach, but
that is the strategy.

If you are using access 2007, there's a new property of the combo box that
lets you specifiy the name of a form for entering the new record.

When you did this in the past with no problem, you would not have had the
first (bound) column set to 0 width.