On form, text box is limited to 255 characters!

R

RatherBeeHome

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????!!!
 
D

Dirk Goldgar

RatherBeeHome said:
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????!!!
Click to expand...

"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?
 
R

RatherBeeHome

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?
 
D

Dirk Goldgar

RatherBeeHome said:
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?
Click to expand...

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.
 
R

RatherBeeHome

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!
 
D

Dirk Goldgar

RatherBeeHome said:
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!
Click to expand...

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,
Click to expand...
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 "
 
R

RatherBeeHome

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??
 
Top