value not valid for field

[dana]

i have a lookup column within a table. format is set as
text, and field size is 255. i get an error that
says "value is not valid for field." i think the problem
is in the sql for the lookup, which is selecting text and
then supplier ID, a number:
SELECT [Originators].[Supplier ID],[Originators].
[Orignator] FROM [Originators]

i think i need to select both originators AND supplier
ID, b/c ID is the primary key...
please help!
thanks

 

[John Vinson]

i have a lookup column within a table.

That's the source of your problem right there. You got suckered by a
Microsoft misfeature.

What is actually IN your table is the numeric SupplierID. What you
*see* is the Originators field. Access is concealing the actual value.

If you want to insert data into this field, you must store the numeric
ID, not the name.

I'd suggest using a Form with combo box, rather than using Microsoft's
misleading, misdesigned so-called Lookup feature. You can have a Combo
Box using the SELECT clause you posted (you might want to sort by
Originators so they appear alphabetically); use the Supplier ID as the
bound column, and set its width to 0 in the ColumnWidths property of
the combo. The combo box wizard will do this for you automatically.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps