Limit to list property

[wolfm]

I have a form that uses a field with a combo box. Users may select a value from the drop down list. I also want to allow values not found on the list to be entered.
When I go to set the field LimitToList property to "no", I get the following error message:

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

My properties for this field:
Bound Column: 1
Column Count: 2
Column Heads: No
Column widths: 0”;1.9896”
List Rows: 16
List Width: 1.9896”
I have tried adjusting settings many ways, and nothing works.



I hope I provided enough information. Any help would be greatly appreciated!

[June7]

This is because the combobox is not saving the value of the column that is displayed to user. If you want to allow any text input then BoundColumn must be the text descriptor column, not the hidden ID column. This means saving the text descriptor, not the ID.

If you want to save the ID and allow adding new items to the RowSource, set LimitToList as Yes and use the NotInList event to create new record in the lookup source table.

[pbaldy]

If you were to enter a value not in the list, what would be saved, since you're entering the second column, not the bound column? Nothing, which is why you get the message. What do you want saved?

[wolfm]

The source table contains only 2 fields; first for ID, and second for Description. I want the description to be stored, not the ID number. When I change Bound Column to 2, it still does not work.

This is because the combobox is not saving the value of the column that is displayed to user. If you want to allow any text input then BoundColumn must be the text descriptor column, not the hidden ID column. This means saving the text descriptor, not the ID.

If you want to save the ID and allow adding new items to the RowSource, set LimitToList as Yes and use the NotInList event to create new record in the lookup source table.

I want description saved, which is the second column in the source table.

If you were to enter a value not in the list, what would be saved, since you're entering the second column, not the bound column? Nothing, which is why you get the message. What do you want saved?

[June7]

Then the combobox properties should be:

RowSource: SELECT Description FROM tablename ORDER BY Description;
BoundColumn: 1
ColumnCount: 1
ColumnWidths: 2.0"
LimitToList: No

[wolfm]

I tried that and could not get it to work. It is storing the record ID number, not the item description.
Anyway, I started over by deleting and re-establishing the field. The combo box now works again, and shows the proper field data. When I check back at my tables, I see it is storing data correctly. So, everything working fine.
But, I really want to allow entry of text not contained in the combo box. When I change the LimitToList to "No", I still get the error.
I could attach my revised database on Tuesday when I get back to work on Tuesday, if that would help. Thanks for your patience all!

[June7]

How can it store the ID if the ID is not included in the RowSource? If anything, should have generated a 'wrong data type' error and not saved anything.

Are you setting lookups in tables? Advise not to do that. http://access.mvps.org/access/lookupfields.htm

Yes, can attach db. Follow instructions at bottom of my post.

[wolfm]

OK. Attached is copy of database. Everything seems to be working, because when I enter parts for an order in Form[Data Entry Form], and then go into Table[Shows], the parts I entered are reflected under that show.

So now, what I want to add the ability to be able to enter items not found in the value list. (Description in Table[Products]).
I checked my tables and I don't think I'm using lookups in them. Thank you.
New Forecasting - Copy.zip

How can it store the ID if the ID is not included in the RowSource? If anything, should have generated a 'wrong data type' error and not saved anything.

Are you setting lookups in tables? Advise not to do that. http://access.mvps.org/access/lookupfields.htm

Yes, can attach db. Follow instructions at bottom of my post.

[June7]

You do have lookups built in the table. Open Orders table in design view. Select Equipment Items field. This field is set as Number type which means you must save a number value, not descriptive text. Click the Lookup tab. It is set for a listbox with a RowSource that has an alias. By alias I mean the table shows the descriptive text but is saving the ID. With this arrangement must use the second option described in post 2.

Also, the subform container SourceObject is the Orders table, not the Orders form. Except for Data Macros, cannot have code behind tables.

BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[wolfm]

It does seem to be saving the descriptive text. But if not, how do I change the field attributes to lookup and store the decriptive text? I do not care about the ID. I have tried many combinations.

You do have lookups built in the table. Open Orders table in design view. Select Equipment Items field. This field is set as Number type which means you must save a number value, not descriptive text. Click the Lookup tab. It is set for a listbox with a RowSource that has an alias. By alias I mean the table shows the descriptive text but is saving the ID. With this arrangement must use the second option described in post 2.

Also, the subform container SourceObject is the Orders table, not the Orders form. Except for Data Macros, cannot have code behind tables.

BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[June7]

CRUD, I just noticed that Equipment Items in Orders is multi-value field. I NEVER use multi-value field. Most experienced developers don't. This opens up a whole new dimension to this issue. If you want to allow multiple products for each Order then really should have a related OrderDetails table. The nature of multi-value fields is that Access actually does this for you but hides the details table. Should have a thorough understanding of multi-value field before committing to this arrangement.

Most experienced developers would also advocate saving the numeric ID instead of the long text descriptor but if you prefer the text, so be it.

[wolfm]

Lets try again.zipLots of complicated answers, but, what I want to do seems very simple. I want to create an order, select a product from the combo box, and enter a quantity.
I also want the user to be able to enter text not found in the combo box. And, that's it! Seems to be very, very simple. I have made so many changes now, my data is corrupt.
So, starting over, attached is my database.
If I open Data Entry Form, I woulod like to click and select the description, not the ID. I also want capability to type text not found in the list.

[June7]

It is simple with proper table setup.

So:

1. delete the relationship between Orders table and Products_1 in the relationship builder so you can do step 2

2. In Orders table
change EquipmentNew field to TEXT data type
change Lookup DisplayControl to Text Box

3. EquipmentItems combobox
ControlSource: EquipmentNew
RowSource: SELECT Equipment FROM Products ORDER BY Equipment;
ColumnCount: 1
BoundColumn: 1
ColumnWidths: 2"
LimitToList: No

[wolfm]

June7, you are awesome! It worked. Thank you so much!