Large Text Fields - how to handle (1 Viewer)

[chilluk7]

I have an Access 2007 ADP linked direct to SQL Server 2005 db.

On my form a I have a large text box bound to a SQL text column.

When my text box was formatted as plain text, when a user clicks in to edit existing text it seems to jump around a bit and lead to truncation of characters, or inadvertent input where what the use could see didn't seem to be what was actually in the column in the table - like a few hidden formatting characters were present in the data but not showing up on the form.

So I changed the text box to Rich Text and that issue seemed to go away, but now obviously Access is adding a large amount of HTML markup - divs etc, changing ampersands - I don't want all this extra!

Any better ideas for handling large amounts of text in a field that might contain line breaks or tabs etc, but won't jump around and corrupt the data?

Many Thanks

 

[chilluk7]

I think I have narrowed it down a little.

This long text data is imported from various suppliers by way of various spreadsheets and other text files. It seems to contain in some instances some pre formatting of the data such as tabs and line breaks. I would also guess that some use different line breaks i.e. chars 13 & 10 whereas some just use char 10.

So I think when a user clicks into the field it is having a hard time with some of this hidden content and so their cursor can land slap bang in the middle of a word (although it doesn't look on screen like it has) they continue to type and then corrupt the data. From the users point of view the text seems to jump, and when they have edited and click back into the field they often have whole chunks missing, or a sentence in the middle of another word for example.

Is there anyway to basically have it so when they are editing the text it's basically treated as plainly as if they had opened the data in a raw text editor (i.e. notepad) rather than Access putting it's own spin on what's in the data, and messing up the display?

 

[CJ_London]

I think it will depend whether the codes you mention are richtext codes or not.

You can remove richtext codes by using the plaintext function in a query or vba, probably as part of your import routine e.g.

myplaintextfield=plaintext(myrichtextfield)

any none richtext codes should then be in plain sight in a plaintext field. You can no doubt eliminate or replace with the equivalent vb characters (if appropriate) by using the replace function.