On form, text box is limited to 255 characters!

[Guest]

Help! I have a simple form with text boxes that are bound to a table. The
fields in the table are "memo" and should allow a large amount of data to be
displayed. For some reason, the text boxes are concatenating my data to 255
characters! What's going on????!!!

 

[steve]

sorry

you cant do this with microsoft access 2003.

you must upgrade to microsoft access 2004:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html


HTH

Steve

 

[Dirk Goldgar]

sorry

you cant do this with microsoft access 2003.

you must upgrade to microsoft access 2004:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html


HTH

Steve

All readers, please note: the quoted reply, and others like it in other
threads, are spam. There is no "Access 2004", and the web page referred
to has nothing to do with the question originally asked.

 

[Dirk Goldgar]

sorry

you cant do this with microsoft access 2003.

you must upgrade to microsoft access 2004:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html


HTH

Steve

All readers, please note: the quoted reply, and others like it in other
threads, are spam. There is no "Access 2004", and the web page referred
to has nothing to do with the question originally asked.

 

[ruralguy via AccessMonster.com]

See if MVP Allen Browne has some answers for you.
Truncation of Memo fields http://allenbrowne.com/ser-63.html

 

[Dirk Goldgar]

Help! I have a simple form with text boxes that are bound to a
table. The fields in the table are "memo" and should allow a large
amount of data to be displayed. For some reason, the text boxes are
concatenating my data to 255 characters! What's going on????!!!

"Concatenating"? Do you mean "truncating"?

Assuming that's what you mean, do you have any Format property applied
to the text box, or to the field in the table? That will cause the memo
field to be truncated.

If that's not it, are you sure the form is bound directly to the table,
and not to a query of the table? If it's bound to the query, does the
query use grouping, or the DISTINCT keyword?

 

[Guest]

Yes, I am using the DISTINCT keyword to pull only distinct records but I'm
not doing this in a query through Access. I am using VBA code with the DoCMD
keyword to run the SQL statement. Is there a way around this?

 

[Dirk Goldgar]

Yes, I am using the DISTINCT keyword to pull only distinct records
but I'm not doing this in a query through Access. I am using VBA
code with the DoCMD keyword to run the SQL statement. Is there a way
around this?

I think I'd better see the code you're using. I'm not sure how you are
using DoCmd and displaying the results on a form. If it's a bound form,
the data it displays should be determined by its RecordSource property,
not by any OpenQuery or RunSQL method you may call.

Please open the form in design view, open the property sheet of the
form, go to the Data tab of the property sheet, and copy what's in the
Record Source property, and paste it into your reply to this message.
If that is the name of a stored query, please copy the SQL view of that
query and paste that into your reply.

If it turns out that it's a query -- whether a stored query or an inline
SQL statement -- that uses the DISTINCT keyword, that's probably the
reason your fields are being truncated. You'll need to find a way to
get the data you want without using the DISTINCT keyword. It may be
that you are using it unnecessarily, but I can't say yet.

 

[Guest]

Thank you for your reply!!! I hope to get this matter resolved soon as it is
a HUGE thorn in my side right now!!


The form that displays the truncated data is opened from another form. Here
is the Record Source for the form with truncated data:

Record Source: tbl_Data_temp

When the button is clicked to open the form, the following VBA code is run:

*******************************************************
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * from tbl_Data_temp;"
DoCmd.SetWarnings True

stSelect = "SELECT DISTINCT Import.ID, Import.Description, Import.Example"
stInsert = "INSERT INTO tbl_Data_temp "
stFrom = "FROM Import "
stwhere = "Where Import.ID LIKE "
stwhere = stwhere & "'*"
stwhere = stwhere & Me!ID
stwhere = stwhere & "*';"

stSQL = stInsert & stSelect & stFrom & stwhere

DoCmd.SetWarnings False
DoCmd.RunSQL stSQL
DoCmd.SetWarnings True

DoCmd.OpenForm "frm_data", acNormal

End Sub
***************************************************
I hope this helps!

 

[Dirk Goldgar]

Thank you for your reply!!! I hope to get this matter resolved soon
as it is a HUGE thorn in my side right now!!


The form that displays the truncated data is opened from another
form. Here is the Record Source for the form with truncated data:

Record Source: tbl_Data_temp

When the button is clicked to open the form, the following VBA code
is run:

*******************************************************
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * from tbl_Data_temp;"
DoCmd.SetWarnings True

stSelect = "SELECT DISTINCT Import.ID, Import.Description,
Import.Example" stInsert = "INSERT INTO tbl_Data_temp "
stFrom = "FROM Import "
stwhere = "Where Import.ID LIKE "
stwhere = stwhere & "'*"
stwhere = stwhere & Me!ID
stwhere = stwhere & "*';"

stSQL = stInsert & stSelect & stFrom & stwhere

DoCmd.SetWarnings False
DoCmd.RunSQL stSQL
DoCmd.SetWarnings True

DoCmd.OpenForm "frm_data", acNormal

End Sub
***************************************************
I hope this helps!

I see. It doesn't matter that the fields in tbl_Data_temp are Memo
fields, because every time you open the form, you are loading the table
with data that has already been truncated. It's this statement that is
at fault:

stSelect = "SELECT DISTINCT Import.ID, Import.Description,

Import.Example"

Do you *need* to have that "DISTINCT" keyword in there? Do you have
multiple records in the table Import with the identical values for the
fields ID, Description, and Example, such that you only want your form
to show one of them? If not, then just change the above statement to
this:

stSelect = "SELECT Import.ID, Import.Description, Import.Example "

 

[Guest]

Yes, I need the DISTINCT statement in there because of multiple, duplicate
values. Otherwise, I get multiple records.

Maybe there is another, easier way to do this. This is what I am TRYING to
do and maybe you know of an easier way.

I have one table called "tbl_data". I have open a form with a particular ID
displayed on it. The form is NOT based on "tbl_data". I want to click a
button from that form that displays only distinct records from "tbl_data"
where the ID that is current on the form is LIKE the id in "tbl_data". I
need asterisks in my SQL statement because the ID is a comma delimited text
box in "tbl_data".

Is there a way to do this using Access Queries instead of SQL statements??

 

[Dirk Goldgar]

Yes, I need the DISTINCT statement in there because of multiple,
duplicate values. Otherwise, I get multiple records.

Maybe there is another, easier way to do this. This is what I am
TRYING to do and maybe you know of an easier way.

I have one table called "tbl_data". I have open a form with a
particular ID displayed on it. The form is NOT based on "tbl_data".
I want to click a button from that form that displays only distinct
records from "tbl_data" where the ID that is current on the form is
LIKE the id in "tbl_data". I need asterisks in my SQL statement
because the ID is a comma delimited text box in "tbl_data".

Is there a way to do this using Access Queries instead of SQL
statements??

There's no real distinction to be made between Access Queries and SQL
statements in this regard. A stored query in Access is essentially a
SQL statement with some extra properties associated with it.

Does tbl_data have a primary key field, or any unique index?

 

[Guest]

No, there is not a unique identifier in tbl_data. The ID field is comma
delimited that has multiple ID's from another table, i.e., ID field has
"4267, 8749, 2849", etc., which all of these ID's ARE the primary key in the
table that the form is based from.

 

[Dirk Goldgar]

No, there is not a unique identifier in tbl_data. The ID field is
comma delimited that has multiple ID's from another table, i.e., ID
field has "4267, 8749, 2849", etc., which all of these ID's ARE the
primary key in the table that the form is based from.

Ugh. Then I believe you're going to need to use VBA code to work around
this faulty design. You might try this:

'----- start of code -----
' NOTE: Requires reference to the DAO object library.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim CurrID As String
Dim CurrDescription As String
Dim CurrExample As String

Set db = CurrentDb
db.Execute "DELETE * from tbl_Data_temp;", dbFailOnError

stSelect = "SELECT Import.ID, Import.Description, Import.Example "
stInsert = "INSERT INTO tbl_Data_temp "
stFrom = "FROM Import "
stwhere = "Where Import.ID LIKE "
stwhere = stwhere & "'*"
stwhere = stwhere & Me!ID
stwhere = stwhere & "*';"

stSQL = stInsert & stSelect & stFrom & stwhere

db.Execute stSQL, dbFailOnError

Set rs = db.OpenRecordset( _
"SELECT * FROM tbl_Data_temp " & _
"ORDER BY ID, [Description], Example")

With rs
Do Until .EOF
If !ID = CurrID _
And !Description = CurrDescription _
And !Example = CurrExample _
Then
.Delete
Else
CurrID = !ID
CurrDescription = !Description
CurrExample = !Example
End If
.MoveNext
Loop
.Close
End With

Set rs = Nothing
Set db = Nothing

DoCmd.OpenForm "frm_data", acNormal
'----- end of code -----

That may work to remove duplicate records after the recordset is loaded.
I'm not 100% sure, though, because I'm not sure the SQL ORDER BY clause
is going to compare fields based on more than the first 255 characters.

 

[Guest]

Thank you for your replies. It helps just to know that this is indeed a
faulty design of the program.

I tried what you suggested and I got an "Invalid use of Null" run-time error
at the Else statement, like the following:

Else
CurrID = !ID
CurrDescription = !Description
CurrExample = !Example
End If

When I hover over the ID field during Debug, the field's value is =Null.
What's going on?

No, there is not a unique identifier in tbl_data. The ID field is
comma delimited that has multiple ID's from another table, i.e., ID
field has "4267, 8749, 2849", etc., which all of these ID's ARE the
primary key in the table that the form is based from.

Ugh. Then I believe you're going to need to use VBA code to work around
this faulty design. You might try this:

'----- start of code -----
' NOTE: Requires reference to the DAO object library.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim CurrID As String
Dim CurrDescription As String
Dim CurrExample As String

Set db = CurrentDb
db.Execute "DELETE * from tbl_Data_temp;", dbFailOnError

stSelect = "SELECT Import.ID, Import.Description, Import.Example "
stInsert = "INSERT INTO tbl_Data_temp "
stFrom = "FROM Import "
stwhere = "Where Import.ID LIKE "
stwhere = stwhere & "'*"
stwhere = stwhere & Me!ID
stwhere = stwhere & "*';"

stSQL = stInsert & stSelect & stFrom & stwhere

db.Execute stSQL, dbFailOnError

Set rs = db.OpenRecordset( _
"SELECT * FROM tbl_Data_temp " & _
"ORDER BY ID, [Description], Example")

With rs
Do Until .EOF
If !ID = CurrID _
And !Description = CurrDescription _
And !Example = CurrExample _
Then
.Delete
Else
CurrID = !ID
CurrDescription = !Description
CurrExample = !Example
End If
.MoveNext
Loop
.Close
End With

Set rs = Nothing
Set db = Nothing

DoCmd.OpenForm "frm_data", acNormal
'----- end of code -----

That may work to remove duplicate records after the recordset is loaded.
I'm not 100% sure, though, because I'm not sure the SQL ORDER BY clause
is going to compare fields based on more than the first 255 characters.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thank you for your replies. It helps just to know that this is
indeed a faulty design of the program.

I hope you understand that my remark about that was directed at the data
design that is combining multiple ID values in one field.

I tried what you suggested and I got an "Invalid use of Null"
run-time error at the Else statement, like the following:

Else
CurrID = !ID
CurrDescription = !Description
CurrExample = !Example
End If

When I hover over the ID field during Debug, the field's value is
=Null. What's going on?

It means there's a record in tbl_Data_temp that has Null in the ID
field, and I would not have expected that, since you load the table
initially with an append query that should only be pulling records where
the ID field contains the value that is in the ID field on your form
(Me.ID). Was that form field blank when you ran the code?

 

[Guest]

It worked!!! THANK YOU!!!

I hope you understand that my remark about that was directed at the data
design that is combining multiple ID values in one field.


It means there's a record in tbl_Data_temp that has Null in the ID
field, and I would not have expected that, since you load the table
initially with an append query that should only be pulling records where
the ID field contains the value that is in the ID field on your form
(Me.ID). Was that form field blank when you ran the code?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)