Memo field truncated from form to table

K

Krista

Hi,
This is a bit complicated to explain, but I will try to explain it as
clearly as possible. I have a form that has a list box and an unbound text
box. When a user clicks on the list box it populates all of the text boxes
on the form except one. One of the fields populated is the unique id called
Status_ID (it's autonumber). After this field is populated, one of the text
boxes is populated using DLookUp because it's a memo field called Comments.
Here is the code:

Me!Status_ID = Me!lst_status.Column(0)
Me!Project_ID_status = Me!lst_status.Column(1)
Me!Priority = Me!lst_status.Column(2)

Me.Comments = DLookup("Comments", "[Project Status]", "Status_ID=" &
[Status_ID])

The text box allows users to type more than 255 characters, but once it's
saved to the table it's truncated. Here is the code for adding the new entry
to the table Project Status:

DoCmd.SetWarnings (False)
DoCmd.RunSQL "INSERT INTO [Project Status](Project_ID, Comments) VALUES
(Project_ID, Comments);"
DoCmd.SetWarnings (True)
DoCmd.Requery "lst_status"
Me!Project_ID.Requery

There is no formatting and the form is tied to a select query using two
tables, Project Status and Project:

SELECT [Project Status].Status_ID, Project.Project_ID, [Project
Status].Comments,
FROM Project LEFT JOIN [Project Status] ON Project.Project_ID=[Project
Status].Project_ID;


If anyone has any idea why the memo field is truncated on the form and on
the table, I would REALLY appreciate the help.
Thanks,
krista
 
K

Krista

Hi Pieter,
I'm not exactly sure what you mean. Can you explain what you mean by using
a recordset and AppendChunck?

Thanks,
krista

Pieter Wijnen said:
Use a recordset & it's AppendChunck method to insert the memo.

Pieter


Krista said:
Hi,
This is a bit complicated to explain, but I will try to explain it as
clearly as possible. I have a form that has a list box and an unbound
text
box. When a user clicks on the list box it populates all of the text
boxes
on the form except one. One of the fields populated is the unique id
called
Status_ID (it's autonumber). After this field is populated, one of the
text
boxes is populated using DLookUp because it's a memo field called
Comments.
Here is the code:

Me!Status_ID = Me!lst_status.Column(0)
Me!Project_ID_status = Me!lst_status.Column(1)
Me!Priority = Me!lst_status.Column(2)

Me.Comments = DLookup("Comments", "[Project Status]", "Status_ID=" &
[Status_ID])

The text box allows users to type more than 255 characters, but once it's
saved to the table it's truncated. Here is the code for adding the new
entry
to the table Project Status:

DoCmd.SetWarnings (False)
DoCmd.RunSQL "INSERT INTO [Project Status](Project_ID, Comments) VALUES
(Project_ID, Comments);"
DoCmd.SetWarnings (True)
DoCmd.Requery "lst_status"
Me!Project_ID.Requery

There is no formatting and the form is tied to a select query using two
tables, Project Status and Project:

SELECT [Project Status].Status_ID, Project.Project_ID, [Project
Status].Comments,
FROM Project LEFT JOIN [Project Status] ON Project.Project_ID=[Project
Status].Project_ID;


If anyone has any idea why the memo field is truncated on the form and on
the table, I would REALLY appreciate the help.
Thanks,
krista
 
P

Pieter Wijnen

instead of
DoCmd.RunSQL "INSERT INTO [Project Status](Project_ID, Comments) VALUES
(Project_ID, Comments);"

Dim Rs As DAO.Recordset

Set Rs = Db.OpenRecordset("SELECT Project_ID, Comments FROM [PROJECT
STATUS]", DAO.dbOpenDynaset)
Rs.AddNew
Rs.fields("Project_ID").value = me.Project_ID.Value
Rs.Fields("Comments").AppendChunck Me.Comments.Value
Rs.Update
Rs.Close : Set Rs = Nothing

if You need more than 32K appended, lookup appendchunk in the VBA help

HtH

Pieter

Krista said:
Hi Pieter,
I'm not exactly sure what you mean. Can you explain what you mean by
using
a recordset and AppendChunck?

Thanks,
krista

Pieter Wijnen said:
Use a recordset & it's AppendChunck method to insert the memo.

Pieter


Krista said:
Hi,
This is a bit complicated to explain, but I will try to explain it as
clearly as possible. I have a form that has a list box and an unbound
text
box. When a user clicks on the list box it populates all of the text
boxes
on the form except one. One of the fields populated is the unique id
called
Status_ID (it's autonumber). After this field is populated, one of the
text
boxes is populated using DLookUp because it's a memo field called
Comments.
Here is the code:

Me!Status_ID = Me!lst_status.Column(0)
Me!Project_ID_status = Me!lst_status.Column(1)
Me!Priority = Me!lst_status.Column(2)

Me.Comments = DLookup("Comments", "[Project Status]", "Status_ID=" &
[Status_ID])

The text box allows users to type more than 255 characters, but once
it's
saved to the table it's truncated. Here is the code for adding the new
entry
to the table Project Status:

DoCmd.SetWarnings (False)
DoCmd.RunSQL "INSERT INTO [Project Status](Project_ID, Comments) VALUES
(Project_ID, Comments);"
DoCmd.SetWarnings (True)
DoCmd.Requery "lst_status"
Me!Project_ID.Requery

There is no formatting and the form is tied to a select query using two
tables, Project Status and Project:

SELECT [Project Status].Status_ID, Project.Project_ID, [Project
Status].Comments,
FROM Project LEFT JOIN [Project Status] ON Project.Project_ID=[Project
Status].Project_ID;


If anyone has any idea why the memo field is truncated on the form and
on
the table, I would REALLY appreciate the help.
Thanks,
krista
 
R

Rick Brandt

Krista said:
Hi,
This is a bit complicated to explain, but I will try to explain it as
clearly as possible. I have a form that has a list box and an
unbound text box. When a user clicks on the list box it populates
all of the text boxes on the form except one. One of the fields
populated is the unique id called Status_ID (it's autonumber). After
this field is populated, one of the text boxes is populated using
DLookUp because it's a memo field called Comments. Here is the code:

Me!Status_ID = Me!lst_status.Column(0)
Me!Project_ID_status = Me!lst_status.Column(1)
Me!Priority = Me!lst_status.Column(2)

Me.Comments = DLookup("Comments", "[Project Status]", "Status_ID=" &
[Status_ID])

The text box allows users to type more than 255 characters, but once
it's saved to the table it's truncated. Here is the code for adding
the new entry to the table Project Status:

DoCmd.SetWarnings (False)
DoCmd.RunSQL "INSERT INTO [Project Status](Project_ID, Comments)
VALUES (Project_ID, Comments);"

Look at this insert statement carefully. It makes no reference at all to your
form so I fail to see how the VALUES() clause contains anything at all.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top