How to Handle Memo Fields

[cooh23]

Hi All,

I have searched the forum but i could not find the answer I was looking for. My database keeps corrupting and it's only been happening recently when user's started populating their memo box. I have noticed that it doesn't get corrupted when they are typing their notes in the memo box. It starts to corrupt when users paste a few sentences into the memo box.

I know that memo fields are to be avoided but i couldn't think of any other way to handle notes.

How can I avoid corrupting the data when user's paste into the memo field?

Thank you,

 

[David Eagar]

I would investigate how to restrict which types of characters can be pasted into the memo field. I have found that control characters can sometimes find there way in and they cause all sorts of chaos

 

[cooh23]

I would investigate how to restrict which types of characters can be pasted into the memo field. I have found that control characters can sometimes find there way in and they cause all sorts of chaos

Hi David,

What do you mean by control characters? The users are only posting letters and numbers and sometimes wild cards.

Thank you,

 

[David Eagar]

I have found that stuff copied and pasted out of Word, Excel or Outlook can sometimes include a Ctrl character when someone has used this instead of Shift. Not often, but when it does its a disaster

There may well be other causes, but I am aware of this one

 

[cooh23]

I have found that stuff copied and pasted out of Word, Excel or Outlook can sometimes include a Ctrl character when someone has used this instead of Shift. Not often, but when it does its a disaster

There may well be other causes, but I am aware of this one

I see... do you know if moving the backend to a SQL Server will eliminate this issue?

 

[Mike375]

In memo fields I find some characters are changed

“middle of the pack”

In the memo field the " show as a bold thick vertical bar. But when copied and pasted back to Word (or this post) the " show again.

99% of Word.doc I produce are pasted back into a memo field although it is being done withing code but I don't imagine that would make any difference.

I have the following for the copy part. It then goes on to open a new record and Paste is done to the memo field for the new record.

docName.EditSelectAll
docName.EditCopy
docName.FilePrint
docName.FileClose (2)

I have never had any problems and have been doing it for several years and it is done by several people on different computers.

 

[David Eagar]

I see... do you know if moving the backend to a SQL Server will eliminate this issue?

Way out of my depth in this area

 

[cooh23]

I have the following for the copy part. It then goes on to open a new record and Paste is done to the memo field for the new record.

docName.EditSelectAll
docName.EditCopy
docName.FilePrint
docName.FileClose (2)

Hi Mike, I don't quite understand what you mean by the above statement. Could you please clarify?

I've observed the users work on the database and what they do is:
- Highlight the notes they want copied and then they hit ctrl+c
- go into the database and hit ctrl+v

sometimes it just takes the information without corrupting, but most of the time it corrupts the database and shows "#error" all across the record being changed.

THank you,

 

[cooh23]

Way out of my depth in this area

Ok...I will research further on this. Thank you.

 

[Mike375]

Ctrl + C works OK for me on Word. I just noticed if I directly type into the memo field the " " shows and is still there when I reopen the form, so paste does change something.

The code above is following code that opened a specific Word.doc and inserted Access field data to Bookmarks. The above code is using Word functions from Edit thus EditSelectAll and EditCopy

After it has done that it goes onto

DoCmd.OpenForm "LetterStoreRecord", acNormal, "", "", acEdit, acNormal
DoCmd.GoToRecord , "", acNewRec

That opened the form called LetterStoreRecord and at a new record

DoCmd.SelectObject acForm, "LetterStoreRecord", False
DoCmd.GoToControl "Letter"
DoCmd.DoMenuItem 0, 1, 3, 0, acMenuVer70 ' Form, Edit, Paste

That selected the form (to make it is the active form) and then when to the memo field called Letter and then did Paste

The stuff below is setting the values of the field for the new record (it is a Many table) with ID number, the persons surname and Now() and then closing the form.

DoCmd.DoMenuItem 0, 5, 4, 0, acMenuVer70 ' Form, Records, Save Record
DoCmd.OpenForm "PrintandClose", acNormal, "", "", acEdit, acNormal
Forms!PrintandClose.RecordSource = "GridJoinNamesSingle"
DoCmd.RunMacro "Macro11TimerSingle", , ""

Forms!LetterStoreRecord!Name = Forms![12ProspectT]![CL Surname]
Forms!LetterStoreRecord!NameNumber = Forms![12ProspectT]!NameNumber
Forms!LetterStoreRecord!Done = Now()

DoCmd.Close acForm, "LetterStoreRecord"

 

[Mike375]

I just thought of one difference which might mean something.....I only copy either the entire Word.doc or alternatively all the text from a specific Bookmark to the end of the Word.doc

Are you copying your text from Word

 

[cooh23]

Ctrl + C works OK for me on Word. I just noticed if I directly type into the memo field the " " shows and is still there when I reopen the form, so paste does change something.

The code above is following code that opened a specific Word.doc and inserted Access field data to Bookmarks. The above code is using Word functions from Edit thus EditSelectAll and EditCopy

After it has done that it goes onto

DoCmd.OpenForm "LetterStoreRecord", acNormal, "", "", acEdit, acNormal
DoCmd.GoToRecord , "", acNewRec

That opened the form called LetterStoreRecord and at a new record

DoCmd.SelectObject acForm, "LetterStoreRecord", False
DoCmd.GoToControl "Letter"
DoCmd.DoMenuItem 0, 1, 3, 0, acMenuVer70 ' Form, Edit, Paste

That selected the form (to make it is the active form) and then when to the memo field called Letter and then did Paste

The stuff below is setting the values of the field for the new record (it is a Many table) with ID number, the persons surname and Now() and then closing the form.

DoCmd.DoMenuItem 0, 5, 4, 0, acMenuVer70 ' Form, Records, Save Record
DoCmd.OpenForm "PrintandClose", acNormal, "", "", acEdit, acNormal
Forms!PrintandClose.RecordSource = "GridJoinNamesSingle"
DoCmd.RunMacro "Macro11TimerSingle", , ""

Forms!LetterStoreRecord!Name = Forms![12ProspectT]![CL Surname]
Forms!LetterStoreRecord!NameNumber = Forms![12ProspectT]!NameNumber
Forms!LetterStoreRecord!Done = Now()

DoCmd.Close acForm, "LetterStoreRecord"

Hi Mike,

Here's how I have my form designed. I have a form and a subform(Datasheet format). In the subform, there is a field called "txtPendingNotes". THe user has to double click to open a form. Here's my code:

Private Sub txtPendingNotes_DblClick(Cancel As Integer)
Dim frmPendingNotesDetails As Form
Dim stDocName As String

Dim rs As Object, strCriteria As String


stDocName = "frmPendingNotesDetails"
strCriteria = "[ID] = " & Me![ID]
'strCriteria = "[ODFNumber]=" & "'" & Me![ODFNumber] & "'"
DoCmd.OpenForm stDocName, , , strCriteria

End Sub

and in the "frmPendingNotesDetails", i have a close button and the code is below:

Private Sub btnclosepending_Click()
On Error GoTo Err_btnclosepending_Click


    DoCmd.Close

Exit_btnclosepending_Click:
    Exit Sub

Err_btnclosepending_Click:
    MsgBox Err.Description
    Resume Exit_btnclosepending_Click
    
End Sub

Do you think your code would work in my process?

Thank you

 

[Mike375]

This the first part I have to open the Wod.doc

Const MSTB_MSWORD = 300&

Application.Run "utility.util_StartMSToolbarApp", MSTB_MSWORD

Dim docName As Object
Set docName = CreateObject("Word.Basic")

docName.FileOpen "c:\Letters\0AMPMike.doc"

With this part

docName.EditSelectAll
docName.EditCopy
docName.FilePrint
docName.FileClose (2)

You may want to change it to FileClose. The (2) is to close the Word.doc without saving.

I think it should work.

 

[cooh23]

This the first part I have to open the Wod.doc

Const MSTB_MSWORD = 300&

Application.Run "utility.util_StartMSToolbarApp", MSTB_MSWORD

Dim docName As Object
Set docName = CreateObject("Word.Basic")

docName.FileOpen "c:\Letters\0AMPMike.doc"

With this part

docName.EditSelectAll
docName.EditCopy
docName.FilePrint
docName.FileClose (2)

You may want to change it to FileClose. The (2) is to close the Word.doc without saving.

I think it should work.

Thank you. I was playing around with your code and attached this part

DoCmd.GoToControl "txtPendingNotes"
DoCmd.DoMenuItem 0, 1, 3, 0, acMenuVer70 ' Form, Edit, Paste

It is pasting what i had copied without pressing ctrl+v.
The only thing that i have to figure out is ..
what if i want to make any changes or add an additional note in the same memo field. I tried copying another line of sentence and all it did was overwrite the data and not add to the existing data. But it works.

Thank you

 

[Mike375]

I have a large macro that includes sever SendKey actions and that is for going to the end of the text in a memo field. Actually, it drops down 2 lines and inserts date time and then puts the text.

What I use it for is to display file notes. Each file note is a record in a Many table but this puts then together in a memo field. However, if you Access 2007 is probably won't work.

I just made 3 dummy file notes and this is how they appear in the memo field

26/04/2008 3:08:30 PM The quick brown fox jumped over the lazy dog

26/04/2008 3:08:53 PM Consider the situation whereby a sickness or accident results in disfigurement or anything else that could cause a loss of custom but the ability to carry out duties is not changed.

26/04/2008 3:09:38 PM Now is the time for all good men to come to the aid of the party

 

[cooh23]

I have a large macro that includes sever SendKey actions and that is for going to the end of the text in a memo field. Actually, it drops down 2 lines and inserts date time and then puts the text.

What I use it for is to display file notes. Each file note is a record in a Many table but this puts then together in a memo field. However, if you Access 2007 is probably won't work.

I just made 3 dummy file notes and this is how they appear in the memo field

26/04/2008 3:08:30 PM The quick brown fox jumped over the lazy dog

26/04/2008 3:08:53 PM Consider the situation whereby a sickness or accident results in disfigurement or anything else that could cause a loss of custom but the ability to carry out duties is not changed.

26/04/2008 3:09:38 PM Now is the time for all good men to come to the aid of the party

Thank you for your help. i decided to move my backend to a sql server. I am now running MS SQL Server Express Edition.