limit to list error

  • Thread starter Thread starter mark R
  • Start date Start date
M

mark R

error message is: Can't set limit to list property to
NO, 1st visible column is not equal to the bound column,
adjust the column width property.



I have no clue as to what this means - I need insightful
explanation.
 
Hi Mark

The BoundColumn property of the combo box signifies which column will have
its contents returned as the Value property of the control. Typically this
column would contain the primary key of your RowSource query, or sone other
unique value. If the combo box is bound to a RecordSource field through its
ControlSource, then it is the value in the bound column that gets stored in
the table.

The ColumnWidths property is a string which specifies the widths of all the
columns in the list when the combo box is dropped down. For example:
3 cm; 3 cm; 0 cm
means that the first two columns are 3 centimetres wide and the third is
invisible (because it has zero width).

The first column that is visible (has non-zero width) is the column that
shows when the combo box is not dropped down. It is also the column that
matches any text that is typed into the combo box.

Often (in fact, usually) the bound column is hidden, or at least is not the
first visible column. If you have, say, a list of customers bound to the
CustomerID field in an Orders table, then you want the customer *name* to
show in the combo, not the customer ID.

Now we come to the reason for your error:

If you set LimitToList to No, you are saying that you want Access
automatically to add a new record to your rowsource if the typed text is not
in the list. However, Access has no way of automatically filling in the
value of the bound column, unless it is the bound column that is also the
first visible column. Without a value for the bound column, the combo box
has no value, and therefore the current row is undefined.

There is a very simple workaround: provide a NotInList event procedure
which takes the newly entered text and, either automatically or after a
"please confirm" MsgBox, writes it to the rowsource table, and then sets the
response to acDataErrAdded. Access will then automatically requery the
combo box, find the text has been added to the list, and then proceed
happily.

I hope that was insightful enough for you ;-)
 
mark said:
error message is: Can't set limit to list property to
NO, 1st visible column is not equal to the bound column,
adjust the column width property.

I have no clue as to what this means - I need insightful
explanation.
Click to expand...


In order for you to be able to enter values that are not in
the list, the value that's saved (BoundColumn property) must
be the first visible column in the list. This allows the
thing you typed to be consistent with items selected from
the list.

You make a column invisible by setting the column's width to
zero (ColumnWidths property). For example, you may have the
ColumnWidths set to 0;2 and the BoundColumn of 1. This is
very normal for a look up list kind of combo box, but you
can not add items to the list without using some code to
modify the lookup table.
 
excellent essay

My BoundColumn property is 0
My ColumnWidths property is null and void
My column count is 1
My Row source type is Value list
My row source is 000;0005;010;015;020;............180....
LimitToList refuses to set to No
you are saying that you want Access automatically to add
Click to expand...
a new record to your rowsource if the typed text is not
in the list.
Click to expand...

I just want to store a valuein that field that is not in
the value list, like 006

Even if I change Bound Column form zero to one
and if I change Column width to .5" form Null,
I still can't change LIMITtoLIST to NO

There is a very simple workaround: provide a NotInList event procedure
which takes the newly entered text and writes it to the
Click to expand...
rowsource table

Do you mean the value list in my case?
(My values will never change, I don't need a lookup table
to store them - I am just trying to provide for some one
to change a 005 to a 006 for the rare blue moon event.)

This I have never done before:
and then sets the
response to acDataErrAdded.
Click to expand...

If such code is still necessary, could you show it to me
more specifically, especially since it is adding a value
to my value list
 
My BoundColumn property is 0
My ColumnWidths property is null and void
My column count is 1
My Row source type is Value list
My row source is 000;0005;010;015;020;............180....
LimitToList refuses to set to No
I just want to store a valuein that field that is not in
the value list, like 006

Even if I change Bound Column form zero to one
and if I change Column width to .5" form Null,
I still can't change LIMITtoLIST to NO

There is a very simple workaround: provide a NotInList event procedure
which takes the newly entered text and writes it to the
Click to expand...
rowsource table

Does this mean the value list in my case?
(My values will never change, I don't need a lookup table
to store them - I am just trying to provide for some one
to change a 005 to a 006 for the rare blue moon event.)

This I have never done before:
and then sets the
response to acDataErrAdded.
Click to expand...

If such code is still necessary, could you show it to me
more specifically, especially since it is adding a value
to my value list


..
 
The bound column should be 1. A bound column of zero means
you want the List Index of the selected item instead of its
value and, of course, you can not exter an item that's not
in the list *because it won't have an index. Well,
technically the List Index of a not in list item is -1, but
this doesn't apply when the bound column is 0.

You say you still can't set Limit To List to No, even with
the bound column set to 1. The only way I can get that
situation is if the Column Width is 0, so I can't reproduce
what you're seeing.
 
Hi Mark

Sorry about the delay - I wanted to find time to test what I was going to
say to make sure I wasn't leading you up the garden path.

First, BoundColumn should be 1, not 0. Setting BoundColumn to 0 is a
special feature to return the ListIndex of teh combobox (instead of the
contents of one of the columns) as the value property - 0=row1, 1=row2, etc

Second, I didn't guess you were talking about a value list, so of course
there is no need to add a record to some underlying table. The NotInList
event is of no use to you here.

Third, I have tested this and it works:

Combo with these properties:
ControlSource: <numeric field in table>
RowSourceType: Value List
RowSource: 1;2
ColumnCount: 1
ColumnWidths: <null>
LimitToList: No

As expected, in the dropdown I get only the values 1 and 2, but I can quite
happily type in 3, and it is stored in my table.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand
 
thanks--I'll give it a try

-----Original Message-----
Hi Mark

Sorry about the delay - I wanted to find time to test what I was going to
say to make sure I wasn't leading you up the garden path.

First, BoundColumn should be 1, not 0. Setting BoundColumn to 0 is a
special feature to return the ListIndex of teh combobox (instead of the
contents of one of the columns) as the value property - 0=row1, 1=row2, etc

Second, I didn't guess you were talking about a value list, so of course
there is no need to add a record to some underlying table. The NotInList
event is of no use to you here.

Third, I have tested this and it works:

Combo with these properties:
ControlSource: <numeric field in table>
RowSourceType: Value List
RowSource: 1;2
ColumnCount: 1
ColumnWidths: <null>
LimitToList: No

As expected, in the dropdown I get only the values 1 and 2, but I can quite
happily type in 3, and it is stored in my table.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand



excellent essay

My BoundColumn property is 0
My ColumnWidths property is null and void
My column count is 1
My Row source type is Value list
My row source is 000;0005;010;015;020;............180....
LimitToList refuses to set to No
add
a new record to your rowsource if the typed text is not

I just want to store a valuein that field that is not in
the value list, like 006

Even if I change Bound Column form zero to one
and if I change Column width to .5" form Null,
I still can't change LIMITtoLIST to NO


rowsource table

Do you mean the value list in my case?
(My values will never change, I don't need a lookup table
to store them - I am just trying to provide for some one
to change a 005 to a 006 for the rare blue moon event.)

This I have never done before:


If such code is still necessary, could you show it to me
more specifically, especially since it is adding a value
to my value list
Click to expand...


.
Click to expand...
 
Back
Top