Memo field truncated from form to table

[Guest]

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

 

[Pieter Wijnen]

Use a recordset & it's AppendChunck method to insert the memo.

Pieter

 

[Guest]

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

Thanks,
krista

Use a recordset & it's AppendChunck method to insert the memo.

Pieter

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

 

[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

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

Thanks,
krista

Use a recordset & it's AppendChunck method to insert the memo.

Pieter

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

 

[Rick Brandt]

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.