Data entry: "...value isn't valid for this field."

[Kevin Sprinkel]

Our company estimates public building projects. We have
begun an Access database to capture quoted costs of
various equipment. The data entry is done in two phases,
on the forms Items_Entry and Items_Update, both based on
queries joining the Manufacturers and Items tables. The
table structures are:

Mfr
---
Key AutoNumber
MfrName
Address
...
...

Items
-----
Key
ID Text Equipment ID on the drawings
JobNum
Mfr LI Lookup to Mfr; stores the Key
Desc
Part # Text
Unit Price $
Per Long Integer Lookup to UnitType
Date

The last three fields are enabled in the form
Items_Update. The problem: regardless of whether I skip
to the next record, or enter what should be valid data in
any or all of the fields, Access displays the
message: "The value you entered isn't valid for this
field."

I've checked to ensure that the data types for all related
fields are the same and what I intended. Thanks in
advance for any assistance; it's puzzling.

 

[Ed Robichaud]

Most likely you have set some table validation(s) for those fields and not
specified any specific error message and/or allowed for nulls. This would
make the generic error appear when no data is entered or data entered is out
of range. Look at the table in design mode and edit or remove the
validation criteria. Press F1 while on the validation line to read the
related Access help.
-Ed

 

[Kevin Sprinkel]

Thank you for responding to my question. None of the
fields, however, have any field validation specified. One
gets its values from a lookup table, but I've since
experimented with disabling fields--no matter what
combination are disabled, I receive the same error message.

Do you have any other suggestions?

 

[John Vinson]

One gets its values from a lookup table

That's very likely the problem. Lookup fields are VERY misleading! If
you try to store the looked-up value in the table (which *looks* like
it ought to work) you will indeed get this message, because despite
apperances, that value does not exist in your table; what DOES exist
is the numeric ID.

This is just one reason I discourage the use of the Lookup Wizard. It
provides a very minor benefit at the cost of a very large amount of
confusion.

 

[John Vinson]

Thank you for your response. Unfortunately, this does not
seem to be the problem. I eliminated the Lookup
definition in the Table definition, and changed the
control in the form, with the same results. In any case,
I had been attempting to save the index into the field--
the target field is a Long Integer type.

I'd appreciate any other thoughts you have, and, moreover,
what you think is the best strategy for limiting a field's
value to a list.

Well, a Combo Box (in addition to establishing a relationship between
the field and the lookup table and enforcing relational integrity)
works just fine.

Please post the following properties of the combo box:

- RowSource (post the SQL)
- Control Source
- Bound Column
- Column Count
- Column Widths

 

[John Vinson]

In THIS particular case, I want the lookup table to
provide only data entry convenience and allow the user to
enter any arbitrary unit. Control properties:

In that case your Control Source must be a Text field; you have no ID
to look up. Change the table definition of the Form's recordsource so
that the field is Text, big enough for any value the user will be
selecting or typing in.

Your table currently has a Long Integer. You're trying to insert a
text string, picked from the list or typed in by the user, into this
long integer field. This text string is in fact not valid for this
field!

 

[Kevin Sprinkel]

I only wish it were that easy, John. Actually, both the
detail field and the lookup field are both defined as text
of the same length.

 

[John Vinson]

I only wish it were that easy, John. Actually, both the
detail field and the lookup field are both defined as text
of the same length.

To clarify - the field Per in table Items is a Text field, and its
Lookup tab in table design says Textbox? And when you open the Items
table in datasheet view, you see text Unit Code values (not numbers)
in the Per field?

 

[John Vinson]

The Per field in Items is an Text Field, length 15. It is
not a text box, however, it is a combo box. Its Lookup
tab properties are:

I'm *pretty* sure that is the problem. The field that you see is in
the lookup table - even though it is IDENTICAL to the table field.

Try changing this from Combo to Textbox and see if that helps.

 

[John Vinson]

Thanks for your patience and persistence. Your comments
led me to the solution.

Whew!!!

Glad you got it worked out... and provided an object lesson in the
problems which the blasted Lookup Wizard can create.