Repost - RunTime Error - Field too small

[Paul B.]

Please see below thread....


:

Thanks for the suggestions, tried them but no joy.

Any other ideas??
:

What happen when you try to insert data into the field directly, and not by
using Dlookup?

Is it a link table? if so, and you changed the field size, try and relink
the table.

In the Dlookup, add left 254 and check what happen then
Me.FieldName = Left(Dlookup(.....),254)

Try compact and repair to the MDB where the table is located in.
[/QUOTE][/QUOTE]
:

I am using a Dlookup statement to update a text box which is working fine
except for some of the entries. I get an error message (below) and I cannot
figure out what is wrong.

The table field is 255 characters (I know it's too big, but for now I still
working on it) and the forms text box is bound to the field in the table.

I have tested the different text formats, and it doesn't seem to care about
brackets, apostrophies, dashes or other symbols.

It seems to die if the field has more than 30 characters.

Any ideas would be appreciated...

If you are interested, this is the error:
---------------------------------------------------------------------------------------------

Run-Time error '-2147352567 (800200009)'

The field is too small to accept the amount of data you attempted to add.
Try inserting or pasting less data.

-----------------------------------------------------------------------------------------------

 

['69 Camaro]

Hi, Paul.

Any other ideas??

1.) Verify that the text box is bound to the field you think it is.

2.) If this is a linked table, don't use the Linked Table Manager or VBA
code to relink (or refresh the link to) the table after making changes to the
table. Delete the link, then create a new link to the table in the back end.
Otherwise, Jet won't store changes to the external database in the table
link information, such as a new database password or information on the
table's structure, including added and deleted fields, changed field data
types or sizes, and the table's statistics.

3.) If all else fails, post your DLookup( ) code and example data that
Access chokes on. You may have a logic error that someone else here can help
you locate.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Please see below thread....


:

Thanks for the suggestions, tried them but no joy.

Any other ideas??

:

What happen when you try to insert data into the field directly, and not by
using Dlookup?

Is it a link table? if so, and you changed the field size, try and relink
the table.

In the Dlookup, add left 254 and check what happen then
Me.FieldName = Left(Dlookup(.....),254)

Try compact and repair to the MDB where the table is located in.

[/QUOTE]
:

I am using a Dlookup statement to update a text box which is working fine
except for some of the entries. I get an error message (below) and I cannot
figure out what is wrong.

The table field is 255 characters (I know it's too big, but for now I still
working on it) and the forms text box is bound to the field in the table.

I have tested the different text formats, and it doesn't seem to care about
brackets, apostrophies, dashes or other symbols.

It seems to die if the field has more than 30 characters.

Any ideas would be appreciated...

If you are interested, this is the error:
---------------------------------------------------------------------------------------------

Run-Time error '-2147352567 (800200009)'

The field is too small to accept the amount of data you attempted to add.
Try inserting or pasting less data.
[/QUOTE]

 

[Paul B.]

See comments inserted below

Hi, Paul.


1.) Verify that the text box is bound to the field you think it is.

Done. It is.

2.) If this is a linked table, don't use the Linked Table Manager or VBA
code to relink (or refresh the link to) the table after making changes to the
table. Delete the link, then create a new link to the table in the back end.
Otherwise, Jet won't store changes to the external database in the table
link information, such as a new database password or information on the
table's structure, including added and deleted fields, changed field data
types or sizes, and the table's statistics.

Done, didn't help.

Rebuilt the table from scratch, didn't help either.

3.) If all else fails, post your DLookup( ) code and example data that
Access chokes on. You may have a logic error that someone else here can help
you locate.

HTH.
Gunny

Here's the select statement currently being used the populate the combo box.

SELECT [tblFacilities].[FacilityName] FROM tblFacilities ORDER BY
[FacilityName];


Here is the DLookup statement in the AfterUpdate event of the FacilityNumber
combo box.

[DestHospName] = DLookup("FacilityName", "tblFacilities", "Code=" &
Me.DestHospNumber)


So when you select a hospital number from the HospitalNumber combo box, as
long as the name is less than 30 characters, the FacilityName combo box
populates fine.

If the name is longer that 30 characters, I get the error message. I will
also get the error message by selecting a name longer than 30 characters
directly from the FaciltiyName combo box.


Thanks in advance....

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.



:

I am using a Dlookup statement to update a text box which is working fine
except for some of the entries. I get an error message (below) and I cannot
figure out what is wrong.

The table field is 255 characters (I know it's too big, but for now I still
working on it) and the forms text box is bound to the field in the table.

I have tested the different text formats, and it doesn't seem to care about
brackets, apostrophies, dashes or other symbols.

It seems to die if the field has more than 30 characters.

Any ideas would be appreciated...

If you are interested, this is the error:
---------------------------------------------------------------------------------------------

Run-Time error '-2147352567 (800200009)'

The field is too small to accept the amount of data you attempted to add.
Try inserting or pasting less data.

[/QUOTE]

 

['69 Camaro]

Hi, Paul.

You probably have a logic error, because the value being assigned is not the
value you think it is. Without knowing your table structure, query
structure, and form structure (control names, which controls are bound or
unbound, what types of controls, and which field names these controls are
bound to), I had to make a lot of assumptions to try to recreate your
situation. For example, Me.DestHospNumber tells me you're using the default
value of a text box, unless you're using the first visible column of a combo
box -- which might be showing you a different value than the bound column of
this combo box if there are multiple columns -- unless, of course, you're
using the bound form's property because you renamed the control, or well, . .
.. unless you're using the bound form's property without a corresponding bound
control. By the process of elimination of all the other possibilities of all
the other bound fields and bound/unbound controls on your form, I'll figure
out what you have, what you need, and whether or not the two match.

In other words, it would take a lot less time for you to read my mind to
find the solution than for me to read yours.

Your code refers to a form property named DestHospName. It may be a text
box or a bound field on your form, but your syntax is using the bound form's
property. Whatever table the form is bound to needs to be opened in Design
View and this field looked at more closely. Specifically, click on the
"Lookup" tab on the Field Properties. Do the same for the FacilityName and
Code fields in the tblFacilities table. If the DestHospNumber is the name of
a bound text box or the bound form's property, then do the same in whatever
table this field resides.

Which of these fields doesn't have "Text Box" as the Display Control on the
Lookup tab? They should all use the default text box. If not, then you've
fallen victim to the deceptive ease of the Lookup Field, and you need to fix
it. Please see the following Web page for more information:

http://www.mvps.org/access/lookupfields.htm

If you're not using lookup fields, then take a closer look at your combo
boxes. Do they use multiple columns? If so, which column is the bound
column? Which column is the displayed column? Do these match? If not,
you'll need to use a different column name in your DLookup statement, or else
use Column syntax to assign the displayed column value, instead of the
default bound column value, when referencing the combo box values in code.
For example, cboSearch.Column(1) refers to the value in the second column of
the cboSearch combo box. (Column numbering starts at 0.)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

See comments inserted below

Hi, Paul.


1.) Verify that the text box is bound to the field you think it is.

Done. It is.

2.) If this is a linked table, don't use the Linked Table Manager or VBA
code to relink (or refresh the link to) the table after making changes to the
table. Delete the link, then create a new link to the table in the back end.
Otherwise, Jet won't store changes to the external database in the table
link information, such as a new database password or information on the
table's structure, including added and deleted fields, changed field data
types or sizes, and the table's statistics.

Done, didn't help.

Rebuilt the table from scratch, didn't help either.

3.) If all else fails, post your DLookup( ) code and example data that
Access chokes on. You may have a logic error that someone else here can help
you locate.

HTH.
Gunny

Here's the select statement currently being used the populate the combo box.

SELECT [tblFacilities].[FacilityName] FROM tblFacilities ORDER BY
[FacilityName];


Here is the DLookup statement in the AfterUpdate event of the FacilityNumber
combo box.

[DestHospName] = DLookup("FacilityName", "tblFacilities", "Code=" &
Me.DestHospNumber)


So when you select a hospital number from the HospitalNumber combo box, as
long as the name is less than 30 characters, the FacilityName combo box
populates fine.

If the name is longer that 30 characters, I get the error message. I will
also get the error message by selecting a name longer than 30 characters
directly from the FaciltiyName combo box.


Thanks in advance....

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.