VB basic issue in Access - unable to edit long text

[JoeK2001]

The message say: Run-time error '2221': The the text is too long to be edited.

I had been successful in turning a drop-down box into an automatic updated
box from the utilization of the events and macros. However, when I select
some entries that had a long names. This error would show up. Therefore any
entires with more than 50 characters would trigger this error. However, the
entries with 50 or less characters worked perfectly. I am not sure if this is
the limitation of the string data type or something else.

The code for this function:

Dim sql4 As String

Me.cmbProject_Short_Name.SetFocus
sql4 = "SELECT [Program Name].[Project Short Name] FROM [Program Name] WHERE
((([Program Name].[Project Number])='" & Me.CmboProjectNumber & "') AND
(([Program Name].[Active Project])= Yes));"
Set rssql4 = CurrentDb.OpenRecordset(sql4)
If IsNull(rssql4(0)) Then
Me.cmbProject_Short_Name.Text = ""
Else
Me.cmbProject_Short_Name.Text = rssql4(0)

End If

*As of now:
The field size in the source box has been switch to 255 from 100. I am sure
the coding has something to do with this error. Could it be the data type
that is not allowing this long text? Should I convert it to something else?

 

[Albert D.Kallal]

I am sure

the coding has something to do with this error

Hum...perhaps...but it still might be your table settings...

First, lets try and clean up a few things that you have wrong here.

Don't use the .text property of control unless you ABSOLUTE have to. I have
a VERY complex application with about 27,000 lines of code and DID NOT ONCE
NEED to use the .text property. To clear this up, .text property is ONLY
valid when a control has the focus, a nd further you can't use all of the
other events correctly (after update, before update). So, simply use the
..value property. (in fact, the .value property is the default property of a
control). Unfortunately, the .text property is what was used in VB6..and in
ms-access you need to use the .value. (the .text is reserved for something
different in ms-access..and that is examine a control *while* it has the
focus to do keystroke processing...not general use of the actual value of
the control).

So, use

me.MyContorl.Value

Or, simply the default "value" property as:

me.MyControl

Doing the above will mean that you can grab, and /or set values of
controls...but not have to use code to "set" the focus to a control each
time you do this (here is just NO need to throw in all that extra code...and
make the whole coding process quite messy if you do).

By using a .text and setfocoes, you going have forms and code that does all
kinds of things that you don't want, nor need to do.

Another tip is to avoid spaces in the control names, and EVEN MORE impornt
to avoided spaces in the sql. Oracle, sql server, and just about every other
standard system in the industry does not allow spaces. Take the following:

select my customer from a table

Is the field name called "my customer", or is it called "my customer
from".....

Things become VERY hard to deal with when you have spaces. You can force
yourself to use [] brackets around these words...but it is not a industry
standard. You would do well you avoid spaces in field names and controls in
the future (it will save you MUCH pain). However, you have what you
have...so, lets move on...

Last, but not least:

What standard did you adopt for blank fields? Do you allow zero length data
such "" in a field, or do you assume it is going to be null.

Also, then the code fails..what line is it on?

So, the code we have now (using some of the above tips) is:

im sql4 As String

sql4 = "SELECT [Project Short Name] FROM [Program Name] " & _
"WHERE [Project Number] = '" & Me.CmboProjectNumber & "'" & _
" AND [Active Project] = Yes"

Set rssql4 = CurrentDb.OpenRecordset(sql4)
if rssql4.RecordCount = 0 then
Me.cmbProject_Short_Name = null
else
Me.cmbProject_Short_Name = rssql4(0)
End If

Note how the code starts to clean up when you throw out the need for the
..text and setting of the focus.

The field size in the source box has been switch to 255 from 100.

I would double check the table design, and make sure the above is the case.
Also, check the setting for 'allow zero length'? Do you allow this?
(I would rather not allow zero length).

Hence, which do you want:

Me.cmbProject_Short_Name = null
or
Me.cmbProject_Short_Name = ""

It is very possible that the control can't take a zero length value (the =
"")...so it errors out...

If you want (for some reason to torture yourself) to allow both nulls and
zero length..then you have to make a decision as to if you want to
have to ALWAYS check for both:

I prefer assuming that ALL blank fields are null. Otherwise, you create a
mess in which you have to test for both. So, I would as a rule simply NOT
allow zero length.

If you do..then your "where" clauses for sql to make reports on the
above will be:
eg:
where ProjectShortName = "" or ProjectShortName is null

So, you will have to constantly check for both......

When you have a table that is mixed with nulls, and zero length..simple
queries become a real mess.

You should adopt a setting of one, or the other for blank fields

eg: use "" (zero lenght), or null...but not both....

So, check the allow zero length setting in the table.

 

[JoeK2001]

Thanks for the detail explaination. However,

New Error Message: Error 3163

Run-time error '-2147352567 (80020009)':
The field is too small to accept the amount of data you attempted to add.
Try inserting or pasting less data.

I have followed your code:

*Went to the source table and made sure that the field "Project Short Name"
where my query draws from is does not accept zero values. I had also checked
the query and everything was fine with the query. Even the longest entry
fitted well in the query.

sql5 = "SELECT [Project Short Name] FROM [Program Name] " & _
"WHERE [Project Number] = '" & Me.CmboProjectNumber & "'" & _
" AND [Active Project] = Yes"

Set rssql5 = CurrentDb.OpenRecordset(sql5)
If rssql5.RecordCount = 0 Then
Me.cmbProject_Short_Name = Null
Else
Me.cmbProject_Short_Namedd = rssql5(0)
End If

*Again, I am able to pull those entries that contained less than 50
characters. However, now it tells me that the field is too small for my
selected entry.

What do you think could be wrong? I could not find a field size in the query
design view. I could not find field size anywhere on the form's design view.
I did not find anything about it on the dropdown box's properties dialog box.

Thanks again!

I am sure
the coding has something to do with this error

Hum...perhaps...but it still might be your table settings...

First, lets try and clean up a few things that you have wrong here.

Don't use the .text property of control unless you ABSOLUTE have to. I have
a VERY complex application with about 27,000 lines of code and DID NOT ONCE
NEED to use the .text property. To clear this up, .text property is ONLY
valid when a control has the focus, a nd further you can't use all of the
other events correctly (after update, before update). So, simply use the
..value property. (in fact, the .value property is the default property of a
control). Unfortunately, the .text property is what was used in VB6..and in
ms-access you need to use the .value. (the .text is reserved for something
different in ms-access..and that is examine a control *while* it has the
focus to do keystroke processing...not general use of the actual value of
the control).

So, use

me.MyContorl.Value

Or, simply the default "value" property as:

me.MyControl

Doing the above will mean that you can grab, and /or set values of
controls...but not have to use code to "set" the focus to a control each
time you do this (here is just NO need to throw in all that extra code...and
make the whole coding process quite messy if you do).

By using a .text and setfocoes, you going have forms and code that does all
kinds of things that you don't want, nor need to do.

Another tip is to avoid spaces in the control names, and EVEN MORE impornt
to avoided spaces in the sql. Oracle, sql server, and just about every other
standard system in the industry does not allow spaces. Take the following:

select my customer from a table

Is the field name called "my customer", or is it called "my customer
from".....

Things become VERY hard to deal with when you have spaces. You can force
yourself to use [] brackets around these words...but it is not a industry
standard. You would do well you avoid spaces in field names and controls in
the future (it will save you MUCH pain). However, you have what you
have...so, lets move on...

Last, but not least:

What standard did you adopt for blank fields? Do you allow zero length data
such "" in a field, or do you assume it is going to be null.

Also, then the code fails..what line is it on?

So, the code we have now (using some of the above tips) is:

im sql4 As String

sql4 = "SELECT [Project Short Name] FROM [Program Name] " & _
"WHERE [Project Number] = '" & Me.CmboProjectNumber & "'" & _
" AND [Active Project] = Yes"

Set rssql4 = CurrentDb.OpenRecordset(sql4)
if rssql4.RecordCount = 0 then
Me.cmbProject_Short_Name = null
else
Me.cmbProject_Short_Name = rssql4(0)
End If

Note how the code starts to clean up when you throw out the need for the
..text and setting of the focus.

The field size in the source box has been switch to 255 from 100.

I would double check the table design, and make sure the above is the case.
Also, check the setting for 'allow zero length'? Do you allow this?
(I would rather not allow zero length).

Hence, which do you want:

Me.cmbProject_Short_Name = null
or
Me.cmbProject_Short_Name = ""

It is very possible that the control can't take a zero length value (the =
"")...so it errors out...

If you want (for some reason to torture yourself) to allow both nulls and
zero length..then you have to make a decision as to if you want to
have to ALWAYS check for both:

I prefer assuming that ALL blank fields are null. Otherwise, you create a
mess in which you have to test for both. So, I would as a rule simply NOT
allow zero length.

If you do..then your "where" clauses for sql to make reports on the
above will be:
eg:
where ProjectShortName = "" or ProjectShortName is null

So, you will have to constantly check for both......

When you have a table that is mixed with nulls, and zero length..simple
queries become a real mess.

You should adopt a setting of one, or the other for blank fields

eg: use "" (zero lenght), or null...but not both....

So, check the allow zero length setting in the table.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Albert D.Kallal]

Time to start debugging this code....

However, now it tells me that the field is too small for my

selected entry.

You mean it does the above for all?

Set rssql5 = CurrentDb.OpenRecordset(sql5)
If rssql5.RecordCount = 0 Then
Me.cmbProject_Short_Name = Null
Else

msgbox "value to place is " & rssql5(0)

' put the above in your code....what does it show?

Fruther, check the contorl souce of your control:

Me.cmbProject_Short_Name = "abc"

msgbox "value is now " & me.cmbProject_short_name

what happens when you try the above?

You sure cmbProject_short_name is a text field?...you sure it is not some
lookup table thing?

 

[Guest]

I'm not sure what you mean by this:

The field size in the source box has been switch to 255 from 100.

Note that there are a few obscure places
(obscure = "I can't remember") where100 means
50 characters (because of unicode).

(david)

The message say: Run-time error '2221': The the text is too long to be edited.

I had been successful in turning a drop-down box into an automatic updated
box from the utilization of the events and macros. However, when I select
some entries that had a long names. This error would show up. Therefore any
entires with more than 50 characters would trigger this error. However, the
entries with 50 or less characters worked perfectly. I am not sure if this is
the limitation of the string data type or something else.

The code for this function:

Dim sql4 As String

Me.cmbProject_Short_Name.SetFocus
sql4 = "SELECT [Program Name].[Project Short Name] FROM [Program Name] WHERE
((([Program Name].[Project Number])='" & Me.CmboProjectNumber & "') AND
(([Program Name].[Active Project])= Yes));"
Set rssql4 = CurrentDb.OpenRecordset(sql4)
If IsNull(rssql4(0)) Then
Me.cmbProject_Short_Name.Text = ""
Else
Me.cmbProject_Short_Name.Text = rssql4(0)

End If

*As of now:
The field size in the source box has been switch to 255 from 100. I am sure
the coding has something to do with this error. Could it be the data type
that is not allowing this long text? Should I convert it to something else?