"The Value You Entered isn't valid for this field"

[Rich D]

I have a very straightforward Invoices Form. Invoice No., Date, Vendor Name,
Job Name Phase, Amount. when I open form view and attempt to scroll thru 1
record at a time, I get the above message. Not every time...I don't see any
consistency as to when it does it or not. It says I entered text in a
numeric field or entered a number larger than the FieldSize setting permits.
I don't see a fieldSize setting in the properties. I'm using a combo list
box and it's pulling up more than one item at a time(that is, 3 columns of
information for each) and the columnWidths property has the 1st entry as 0",
which seems wierd. I changed it to 3" and then I get the ID number in my
Vendor Name box instead of his name. I'm perplexed.

 

[Ken Sheridan]

Rich:

I can't really explain why you are getting the error if you are merely
scrolling through records and not entering or editing any data, but maybe I
can clarify the other points. If you are using the keyboard rather than the
mouse to navigate through the records it could be that it is somehow
inadvertently attempting to edit data in a field as you do so.

The FieldSize property is a property of a field in the underlying table, not
of the control on the form. It determines the maximum number of characters
allowed in the case of a text field, or in the case of a number field the
numeric data type e.g. byte, integer, long integer, single precision floating
point, double precision floating point etc. You'll find details of what
these different data types mean in Help.

The zero setting for the first dimension of the combo box's ColumnWidths
property hides the first column of the control's RowSource property, which is
an SQL statement (a query) which returns rows from a referenced table,
Vendors in your case. This is very frequently done when the control is bound
to a foreign key ID field with arbitrary numeric values (normally long
integer data type referencing the primary key, often an autonumber long
integer data type, of the referenced table) but you want to see the text
field(s) from the referenced table. So in your case you select a vendor by
name but the value of the combo box, and hence of the foreign key field its
bound to, is the hidden numeric ID value which corresponds to the selected
name. The value of the combo box is determined by its BoundColumn property,
which will be 1, i.e. its value id of the hidden first column. The value
shown when you make a selection is the first visible column from the
RowSource, so when you changed the first dimension of the ColumnWidths
property to a non-zero value that column became the one displayed in the
control after a selection is made.

Ken Sheridan
Stafford, England

 

[Lyndon]

This is a weird Access thing... when you link a RecordSet to a ListBox
or ComboBox it creates an index for each record so when you call
ListBox.Value it can return the index to the record you selected. This
is why the index was showing rather than the persons name, because it's
the first field in the record, automatically created by Access.

To turn this off, set your Bound Column setting of your List/Combo Box
to zero (0) - it was probably defaulted to one (1). This forces Access
to use the first column in your List/Combo Box for the first real field
in your RecordSet, not for the automatically generated index value. It
will fix the error above at the same time, because Access won't be
trying to put a person's name into the index field (which obviously
won't fit because of size and data type differences).

 

[Ken Sheridan]

I think you'll find that you've got things the wrong way round. If a combo
box or list box control's BoundColumn property is set to zero it returns the
ListIndex property as the control's Value. If the BoundColumn property is
greater than zero the Value of the control is that of the column in its
RowSource which corresponds to that value, 1 for the first column, 2 for the
second and so on. In Rich's case by amending the ColumnWidths property so
that the first dimension was a non-zero value he'd made the first, previously
hidden column visible, and caused the value of that column to show in the
text box part of the control when an item was selected, not the ListIndex
property of the control. The column in his case would almost certainly have
been an arbitrarily numeric primary key of the Vendors table, very likely an
autonumber. The Value of the control remains the same, however, as this is
determined by the BoundColumn property regardless of the ColumnWidths
property's settings.

The association of the ListIndex property with a BoundColumn value of zero
is, incidentally, the source of the undeserved accusation sometimes levelled
at the MS Access development team that they were inconsistent in making the
Column property zero-based and the BoundColumn property one-based. In fact
both are in effect zero-based but rarely would zero be used as the value for
the BoundColumn property, it being more usual to return the same value simply
by examining the ListIndex property.

The ListIndex property has nothing to do with the control's RowSource being
a recordset. It returns a value regardless of the RowSourceType property,
even when this is a call-back function filling the control's list
programmatically, though the latter is not used so much these days as Access
now supports the AddItem method.

I doubt that Rich's problem is connected with the combo box at all in fact.
It sounds to me more like something resulting from an attempt to update the
form's underlying recordset.

Ken Sheridan
Stafford, England

 

[aaron.kempf]

Ken

this is one of those thigns that was different in Access vs VB6 right??

-Aaron