The text you entered isnt an item in the list

[Guest]

Hello I have a combo box that has employee names in it for accident s at
work. Sometimes we get a third party or outside contractor who gets hurt and
he needs to be entered into the database. I wnat the combo box to be abe to
be editable to where it wont give me a "The text you entered isnt an item in
the list" error. How is this done?

Thanks!!!!!

 

[Guest]

Here are a couple of resources for you to check out:

Adding values to lookup tables
http://allenbrowne.com/ser-27.html

Add item to combo box using OnNotinList event
http://www.mvps.org/access/forms/frm0015.htm


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

thanks for the info but im lost and new to access. I just went into the combo
box properties and tried to set the "Limit to list" to "No" and a box comes
up and says

"The first visible colum, which is determined by the ColumnWidths property,
isnt equal to the bound column.
Adjust the ColumnWidths property first, and then set the LimitToList
Property."

I then went and set both the Column width and bound column to 1 and then it
let me set the Limit to list to NO. I then tries it and it works but now not
only is my employees names in the box but now the "ID" number is there as
well. What am I doing wrong?

 

[John W. Vinson]

Hello I have a combo box that has employee names in it for accident s at
work. Sometimes we get a third party or outside contractor who gets hurt and
he needs to be entered into the database. I wnat the combo box to be abe to
be editable to where it wont give me a "The text you entered isnt an item in
the list" error. How is this done?

Thanks!!!!!

Depends on how the combo box is set up. What is its RowSource? Is it a
Query, storing an EmployeeID? or is it actually storing the employee's
name?

Please post the SQL string of the Rowsource, the Control Source (and
its datatype), and any other information that would help.

John W. Vinson [MVP]

 

[Guest]

It is storing an employees name. The way I have it set up is that a table
with all my employee names. I am calling up that table in the combo box and
storing that info in another table. The problem is that I cant input anything
in the combo box that isnt in the table. I tried to use the limit to list and
it still isnt working.

 

[Guest]

Set the Limit to List property to Yes, as indicated in Allen Browne's article
(the first link I provided).

Also, it would be helpful if you can answer the question that John Vinson
just asked:
"What is its RowSource?". Open the form in design view. Click on View >
Properties to display the Properties dialog, if it is not already displayed.
Select the combo box in question. Select the Data tab in the Properties
dialog. Copy the RowSource by selecting it and entering <Ctrl C>. Paste it
into a reply with <Ctrl V>.

Also, on the Data tab, what is the Bound Column? On the Format tab, what
are the Column Count and Column Width properties showing?



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Here is the row source...

SELECT tbl_EmployeeInformation.ID, tbl_EmployeeInformation.Name FROM
tbl_EmployeeInformation;

 

[John W. Vinson]

It is storing an employees name. The way I have it set up is that a table
with all my employee names. I am calling up that table in the combo box and
storing that info in another table. The problem is that I cant input anything
in the combo box that isnt in the table. I tried to use the limit to list and
it still isnt working.

If you are storing the *NUMERIC* EmployeeID in the table field
indicating the victim of the accident, then you must be able to look
up a numeric value (from the Employees table) to store into that
field.

I'd suggest you change the design of your table to store, not the
employeeID, but a Text name. CHnage the datatype of this field!

You can then delete this combo box and create a new one, based on a
query selecting *the person's name* from the Employee table:

SELECT Employees.LastName & ", " & Employees.FirstName FROM Employees
ORDER BY LastName, FirstName;

Use this query as the row source of your combo box, and set its Limit
to List property to No. Then you can store a text string name (*not* a
numeric ID) into the table, and still be able to type in a name which
wasn't in the list.

John W. Vinson [MVP]

 

[Guest]

Employee is set to Text in the tbl. I had also tried to make a new combo box
and I did select "Name" only not ID but it still shows up in the row source..

 

[Rick Brandt]

Employee is set to Text in the tbl. I had also tried to make a new combo box
and I did select "Name" only not ID but it still shows up in the row source..

The way you have it now (show the name, but store the ID) is the correct way to
do it. It just has the downside that you cannot set Limit-To-List to No.

When you are storing the same value you are showing you can deviate from the
list because you will simply store whatever the user enters. If your setup is
one where you store an ID while showing text in the list there is no way to
deviate from the list because there would be no source for the numeric value
that needs to be stored.

In a setup like that the best thing to do is to have a procedure that pops up a
small form that allows the user to add a new record to the RowSource table of
the ComboBox. Since they will be able to add to what is in the list then they
no longer have a need to deviate from the choices in the list.

I generally implement this by using the double-click event of the ComboBox. My
users know that if an item is not in the list they can add it by
double-clicking.

 

[Guest]

You answered just one of my questions out of several. So, I'm not sure how I
can help you any further, other than perhaps an old sample that I have posted
here will be useful for you:

http://www.seattleaccess.org/downloads.htm
Scroll towards the bottom of the list, where you should see:

Not In List - Detailed Instructions by Tom Wickerath,

Download (16 kb)




Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Rick that would be fine but I dont want that name stored in my
tbl_employeeInformation because it would come up in different applications I
am using in this database. Is there any other way?

 

[John W. Vinson]

Employee is set to Text in the tbl.

Well, I'm sure it's Text in the Employee table. What is the datatype
of the field in which you're trying to STORE the value? Is it a Lookup
Field? If so, you may *see* a name but what's actually stored in the
table is the numeric ID.

I had also tried to make a new combo box
and I did select "Name" only not ID but it still shows up in the row source..

Don't use the Combo Box Wizard, if this is what you want to do.

It's apparent that what you're storing in the table is a numeric
EmployeeID. That's fine, if you only want to store employees into this
field.

But you don't.

You want to store the names of people who are not employees.

The solution is EITHER to add these people to your Employees table (as
suggested elsethread), OR to change the table so that you have a free
text field to store the name of the person involved in the accident -
without *any* control over what gets typed into that field. You may
need this relaxation of control if (say) you ever have to record the
name of a visitor, or a family member, or even a tresspasser or the
name of someone who was walking by on the sidewalk when an accident
involved them.

My suggestion would be to use a text field (so you can put anybody's
name into it), and use the combo box - NOT using the combo wizard, but
using a query like that I suggested - so that you can store employee
names without retyping them, but still allow other names to be
entered. Others have other suggestions; you'll need to make a decision
which technique you want to use.

John W. Vinson [MVP]

 

[Rick Brandt]

Rick that would be fine but I dont want that name stored in my
tbl_employeeInformation because it would come up in different
applications I am using in this database. Is there any other way?

When you store an ID while displaying another value from the lookup table you
are creating a link between the record you are saving and a record in the lookup
table. You are in affect saying "all the values in this field are related to
records in this lookup table".

That is either a true statement or it is not. In other words if the "deviant"
record is allowed here then it should be allowed everywhere. Otherwise the
relationship that is implied between the two tables by the ComboBox does not
really exist and if that is the case then you shoudl just store the text and not
the ID value. THEN you could turn Limit-To-List off and what you want would
work.

 

[Guest]

It doest matter what I do at this point I cant figure out why its storing my
names as the ID and not the name! I have even had my brother who has built
several databases look at it and he says everything looks right? This section
of the database is exported into mine from an old one that someone built.
The only thing I see is that there is filters in the properties which I dont
really know what those are? Thanks!!

 

[Guest]

What is the Bound column property? Based on your previous reply to me
(2/11/2007 2:23 PM PST), where you gave me the RowSource as:

SELECT tbl_EmployeeInformation.ID, tbl_EmployeeInformation.Name FROM
tbl_EmployeeInformation;

I'm guessing that the Bound Column is 1. If you want to actually store the
name instead of the ID, then you will need to set the Bound Column to 2 with
the above RowSource.

Alternatively, change your RowSource so that tbl_EmployeeInformation.Name is
the first field selected, as in:

SELECT tbl_EmployeeInformation.Name FROM tbl_EmployeeInformation
ORDER BY tbl_EmployeeInformation.Name;

You will also need to make sure that that field specified as the Control
Source (Date tab) is a Text field with enough characters permitted for the
Name data to be inserted. This will allow you to freely type in names that
you do not want to enter into your tbl_EmployeeInformation table.

However, that said, you should be aware of some possible pitfalls in such a
strategy. Anytime you allow users to type in data versus selecting from a
list, you have the potential for misspellings. This can lead to inaccurate
results if you later query your data for a particular spelling that you
believe is correct. This is discussed in the first screen of the Table
Analyzer wizard in Access 2000/2002/2003. Click on Tools > Analyze > Table
and read the examples given on the first screen.

Also, I recommend that you consider a different field name instead of "Name"
in the tbl_EmployeeInformation table. The reason is that Name is considered a
reserved word. You should avoid naming anything in Access with a reserved
word. Here is a handy list of reserved words for your reference:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html


Allen has a very nice (free) utility that you can download to test your
database for the use of reserved words in field and table names (I don't
believe this version catches the use of reserved words for the names of
controls on forms and reports).


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

I worked on it all day at work and I found a solution! I just created a Query
from my tbl_EmployeeInformation and Had it store in the table I wanted. I
then made that combo box go to that Query and store in the desired place. I
also now can edit that perticular combo box and it stores the the names
insted of ID. Im figuring its not the right way to go but it works!