Memo field - Data is truncated on Form save

[Halliarse]

Hi

I'm using MS Access 2003 and I have a table which has a field called NT_Memo and as its name suggest, has a Data Type of Memo. My understanding is that under 2003 you can enter 65536 characters into a Memo field, however, if I enter more than 255 characters, the data is truncated when I save the data on the form.

The code that saves the form data to the table is as follows:

If Form_frmNotesVAE.NotesType = 2 then
with rsNotes
.AddNew
![SiteID] = Form_frmNotesVAE.SiteId
![NT_Date] = Form_frmNotesVAE.NT_Date
![NT_User] = Form_frmNotesVAE.NT_User
![NT_Memo] = Form_frmNotesVAE.NT_Memo
.Update
End With
DoCmd.Close acForm, acSaveNo
End If

I've researched this a lot and there seems to be no definitive fix...can anyone please assist and this is becoming more than a little frustrating.

Many thanks in advance

Steve

 

[MajP]

Is there any formatting on the textbox or the field?

 

[Halliarse]

Hi

I've looked again at this and it isn't truncating when it saves the data, but when it displays the data afterwards!

The only formatting is as follows:

Decimal Places: Auto....that's a puzzler!!
Can Grow: Yes
Can Shrink: No
Filter Lookup: Database Default

Thanks

Steve

 

[MajP]

I've looked again at this and it isn't truncating when it saves the data, but when it displays the data afterwards

Are you saying that if you look at the table the memo is untruncated, but in the form view it shows truncated?
If that is the case how are you loading the form? It appears to be unbound. There are lots of queries that will truncated. So if you are reading from a query and then populating that may do it. See here:

http://allenbrowne.com/ser-63.html

 

[dhookom]

Where are you "displaying the data afterwards"? Is this in a form or report or what? Is it truncating in a query or control?

Duane
Hook'D on Access
MS Access MVP

 

[Halliarse]

The saved data on the table is not truncated but when viewed on a form, via an unbound text box, it becomes truncated

 

[MajP]

So how do you load the unbound textbox?

 

[dhookom]

How does the data get into the unbound text box and what are the significant format properties of text box?

Duane
Hook'D on Access
MS Access MVP

 

[Halliarse]

The available records are initially select using the statement below and loaded into a form:

Me.NotesListing.RowSource = "SELECT tblNotes.SiteID, tblNotes.NT_ID, tblNotes.NT_Date, tblNotes.NT_User, tblNotes.NT_Memo FROM tblNotes WHERE [tblNotes.SiteID] = " & Me.OpenArgs & " ORDER BY tblNotes.NT_Date DESC;"

When you select a specific note to view, a new form is loaded using the following code:

Public Function cmdNotesView()

If IsNull(Form_frmNotes.NotesListing) Then
MsgBox ("Please select note to view")
Else
DoCmd.OpenForm "frmNotesVAE", , , , , , Form_frmCustomers.SiteID
Form_frmNotesVAE.NotesType = 1
Form_frmNotesVAE.SiteID = Form_frmNotes.NotesListing.Column(0)
Form_frmNotesVAE.NT_ID = Form_frmNotes.NotesListing.Column(1)
Form_frmNotesVAE.NT_Date = Form_frmNotes.NotesListing.Column(2)
Form_frmNotesVAE.NT_User = Form_frmNotes.NotesListing.Column(3)
Form_frmNotesVAE.NT_Memo = Form_frmNotes.NotesListing.Column(4)
Form_frmNotesVAE.cmdOK.Visible = False
Form_frmNotesVAE.cmdCancel.SetFocus
Form_frmNotesVAE.Caption = "View Notes"
End If


End Function

What do you determine as the significant format properties of text box?

 

[dhookom]

The row source of a combo or list box will only return 255 characters or less. You will need to use some code or a subform or some other method to pull the full value from the table.

Duane
Hook'D on Access
MS Access MVP

 

[Halliarse]

Thanks....can you advise further of point me in the direction of a link, possibly?

Thanks

 

[dhookom]

You can use the concatenate function found at faq701-4233. Your line of code then might be:

Form_frmNotesVAE.NT_Memo = Concatenate("SELECT NT_Memo FROM tblNotes WHERE SiteID = " & Me.OpenArgs )

In addition, please attempt to use TGML. It's fairly quick and easy to select a block of text and click the Code button above the text box window.

Duane
Hook'D on Access
MS Access MVP

 

[Halliarse]

I have managed to resolve this using a simple DLookup which selects my data using the key generated from the row source!