Problem with combo box

[Wavona]

Good morning,

I have a table in which I have created a combo box.
When I try to select a value from the combo box in the table, I get the annoying error message "The text you have entered isn't an item in this list", even though the value IS an item in the list!! (The combo box lists job titles). This works fine in my form.

I think this is happening because, when Access created the combo box it set the data type to "number" instead of "text", so it's probably expecting me to enter the job title ID, instead of the job title itself.

Could someone tell me whether there is a simple way of fixing this problem please?

Thanks again for your help,
Anna :banghead:

 

[Mr. B]

First, Anna, welcome to the forums.

Next, I must tell you that although Access will certainly allow you to create a combo box in a table, it is not recommended by most Access Developers, including me. Tables should be used to store data. Forms should be used to provide the user interface to the data in your tables. You should be creating the combo box on a form.

It is quite normal for combo boxes to be used to allow for the selection of a value and the link to that data would be the numeric value from an autoNumber type field that is used as a primary key to the table.

With all of this said, you can use the combo box and use it to select the text value instead of the number value. First, the field you are attempting to populate must also be a text type field. To fix the issue with the combo box, with your table in design view, click on the Lookup tab. You should see the Bound Column property set to 1 and the Column Count set to 2 or more. If the text value you want to select and use for you field is in the second column, change the Bound Column property to 2. Save your changes and give it a try. Your combo box should how select the text values.

 

[Wavona]

Thanks for the help and advice Mr B. I am totally self-taught on Access, so I don't know what I am doing most of the time!

It's working now, so thanks again.

Best wishes,
Anna

 

[Wavona]

My problems with the combo box have just multiplied

I created my database using Access 2007. I was then upgraded to Access 2010, and that seems to have played havoc with each look up I have created in all of my tables. For instance, I have a table listing teachers and their status, e.g. active, on leave etc. Before the upgrade, I could just start typing "a" for active in the relevant field of the table, and the value would come up automatically, i.e. "active". Now I keep getting the error message I got before "The text you have entered isn't an item on the list". I tried Mr B's advice, and changed the bound column, but now this shows the value ID, rather than the value name show up, e.g. 1 for Active instead of Active.

It's driving me bonkers and I am spending hours on something I created weeks and weeks ago, which worked fine up to now, and now all of my work seems ruined.

Please someone help me!!!

Thanks,
Anna :banghead:

 

[Mr. B]

Anna,

I have to assume that there is a numeric value and then a text value being provided by the rowsource of your combo box. If this is true and you have changed the bound column to be the second field then you need to click on the Format tab of the Properties sheet and find the Column Widths property. Type a 0 to hide the first column.

If I am understanding your issue correctly that should fix the problem.

 

[Wavona]

Paul,
Thanks for the help once again. I can see why that would work normally, but something has happened to the database and it has affected all kinds of things.

I have decided to take up your advice about combo boxes in tables, however, so I have created a new, blank database and I am starting from scratch again, importing each table and removing the combo boxes or partitioning the tables if necessary. It's a long process, but I think it's worth it, and the database seems to be working again.

I'm sure I'll be back again for more advice though!

Thanks again for taking the trouble to help me.

Best wishes,
Anna