Formatting Memo Fields

[gblack]

This one is killing me!

I have investigated this thing way too long for what it's worth, but my OCD isn't letting me stop LOL.

Whenever a user pastes text with formatting into a memo field Access cannot decipher this and Marks the value with a funky question-mark surrounded by a rectangle. I looked this up and they said that all I had to do was change the plain text value to rich text and that would solve the problem... this is untrue. here's the article (I am only posting this so no one posts it as an actuall solution, because it isn't): http://office.microsoft.com/en-us/access-help/insert-or-add-a-rich-text-field-HA010014097.aspx

That said, I looked up a posisble solution to the issue would be to take out the non-printable ASCII characters beforehand... http://answers.microsoft.com/en-us/office/forum/office_2007-access/problems-trying-to-import-a-csv-file/e1e5e1b4-4ddf-48dd-83a5-e0bd0b21733a

This seemed logical. What I don't want to do is go in and change every memofield in my rather lengthy form... What I would like is to have the user click in the memo field they wish to populate then click a "paste text" button.

Once they to this I would like this button's OnClick sub to:

1) find and save the last text control that had focus
2) open an input box allowing users to paste their formatted text into
3) take the text from the inputbox and run a format cleansing function
4) Post this newly cleaned text back into the last text control that had focus
5) set the focus to said control.

I think I am almost there, but I am having issues.

This code seems to remove the non-printable ASCII:

Function StripReturns(varIn As Variant) As Variant

Dim lngX As Long
Dim varName As Variant
Dim varNewName As Variant
Dim varY As Variant


If Not IsNull(varIn) Then
    varName = varIn
    For lngX = 1 To Len(varName)
    varY = Mid(varName, lngX, 1)
    If (Asc(varY) > 31) Then
        varNewName = varNewName & varY
    Else
        varNewName = varNewName & " "
    End If
    Next lngX
    StripReturns = varNewName

End If

End Function

Here's where I am having the problem, this code isn't working:

Private Sub btnRemovePasteFormat_Click()
Dim strInput As String
Dim strPrevControl As String

strPrevControl = Screen.PreviousControl.Name

strInput = InputBox("Past Text")
Forms![00120_Agreement]!strPrevControl.ControlSource = StripReturns(strInput)

End Sub

If anyone has a better way, I am all ears!

Thanks in advance!
Gary

 

[boblarson]

When you say, "this code isn't working" what does that mean exactly. My first impression is that it won't work because you are using an Input Box to capture the paste and that will NOT work as an Input Box does not work with Rich Text. It is plain text only and so it would be converted to plain text immediately upon paste.

And I just ran a test and used a memo field with Rich Text selected as the type and a form with a text box set to rich text and every paste I've made of some Rich Text, either from Word or Internet Explorer, works fine without the squares.

Which service pack do you have for Office 2007 (or are you on 2010)?

 

[gblack]

Bob,

Thanks for investigating this matter! In hindsight I realized that I didn't explain my issue clearly. I kind of skipped one very large thing.

It isn't that the data looks bad in the form... (my mistake) it is that when I print it out from Reports as a .PDF the text values in the memo fields display all funky. There is a question mark encased by a rectangle between each bullet and the text... I wish I could show you.

That said, and in order to clean this up...

What I wanted to do in the code is to have the users paste code to an inputbox, have that pasted text be unformatted and then push unformatted text to any given text/memo field in the form (based upon the last text/memo field that had the focus)... if the inputbox wipes out formatting... then I guess I don't need that initial function (because all that was doing was clearing out all formatting).

But what I don't know how to do is to insert the text from the input box into the appropriate (i.e., last focused) text/memo field.

The process I envision is: The user clicks in the text box/memo field they want to paste their text to... then they click the button that pulls up the inputbox.... they paste their text into the inputbox and click "OK" then the unformatted text from the inputbox populates the textbox/memo field they had previously clicked in...

I was trying to use Screen.PreviousControl to denote the text/memo field that had the focus just prior to the user clicking the paste button.

Unfortunately I don't know enough about how to reference that control once the inputbox have been pasted into...

That is what I was looking for at least...

As far as service pack... I have no idea about Access. I am using MS Access 2007... Vista is my OS and it is SP2... I don't know if that helps you figure out anything about my issue... I apologize for not being clear, initially... I suppose leaving out the fact that the funky characters were coming from my report in PDF format and not the form itself, was no small thing...

Thanks again,
Gary

 

[boblarson]

Bob,

Thanks for investigating this matter! In hindsight I realized that I didn't explain my issue clearly. I kind of skipped one very large thing.

It isn't that the data looks bad in the form... (my mistake) it is that when I print it out from Reports as a .PDF the text values in the memo fields display all funky. There is a question mark encased by a rectangle between each bullet and the text... I wish I could show you.

No problem, I know about that issue but as you said, you didn't mention that was where you were seeing them.

But what I don't know how to do is to insert the text from the input box into the appropriate (i.e., last focused) text/memo field.

I was trying to use Screen.PreviousControl to denote the text/memo field that had the focus just prior to the user clicking the paste button.

Unfortunately I don't know enough about how to reference that control once the inputbox have been pasted into...

The correction to that would be:

Forms![00120_Agreement][B][COLOR=red].Controls(strPrevControl).Value[/COLOR][/B] = StripReturns(strInput)

As far as service pack... I have no idea about Access. I am using MS Access 2007...

See here for how to know.

 

[gblack]

Bob,

You rock! That worked exactly like it was supposed to... unfortunately it didn't turn out to be what I needed, because the inputbox isn't big enough. Blah!

That said, do you know of a better way to remove formatting from a cut and paste situation into a memo field.

use the same basic logic to have the user cut and paste into the memo field itself... then press a "unformat" button. I guess I'll try that.

Thanks so much for your help Sir!

 

[boblarson]

How about just creating a form with a text box on it and leave the format as plain text.

 

[gblack]

Well the form is extensive and is already in production. The memo fields are already set to plain text, but that doesn't prevent the cut and paste jobs from holding some formatting (I guess returns???)...

Meaning: I can still paste bullet points from word and the bullets show up... they just aren't intented... The problem is they are still formatted enough that the Report doesn't like it (at least in PDF).

However, after you unformat the cut and paste job entirely... you can then do a little "light" formatting with CTRL + Enter... That's about all it will take without yielding those dang question marks.

I ended up setting the button to unformat the text within the text or memo field like so (not Ideal, but it seems to work ok):

Private Sub btnRemovePasteFormat_Click()
Dim intAnswer As Integer
Dim strPrevControl, strPrevValue As String

strPrevControl = Screen.PreviousControl.Name
strPrevValue = Screen.PreviousControl

intAnswer = MsgBox("Are you sure you want to remove all the formatting in: " & vbNewLine _
                  & strPrevControl, vbYesNo)
                  
If intAnswer = vbYes Then
    Forms![00120_Agreement].Controls(strPrevControl).Value = StripReturns(strPrevValue)
End If

End Sub

Again thanks for all your help Bob!

 

[boblarson]

When I said to create a form, I meant to just create your own replacement for the InputBox. You could call the form as WindowMode:=acDialog and that would stop everything just as if you used an input box and then in the close command you could copy the text to the real form. (it was just an idea)