Strip rtf tags from richtextbox control on an Access form

I am trying to use the richtextbox control in Access 2000 on a form in a bound field.  My problem is that I don't want to bind the rtf tags (ie  {\rtf1...) to the record.  I only want the text that is entered.  Here is what I've done:

1) built an access form
2) using the "more controls" button, I selected "Microsoft Richtextbox Control 6.0 (sp4)" and drew a box on the form
3) I named it richtextbox1 and bound it to my table field [comment], which has the memo property set
4) I used the command button and command button wizard to create a button that saves the record

When I type in form field [richtextbox1] and click save, the record correctly writes to the Comment field but with all the rtf tags. How can I get just the text after the text box is updated or upon save of the record?  The activeXcontrol properties for the richtextbox doesn't appear to have any property settings that applies to this.

Eventually I also use the table and records in a report and export it in the format of RTF.  But of course the rtf tags are also a problem here since they are part of the record.

Thanks in advance for your help.
Sue WhiteAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeff TennessenAssistant Vice PresidentCommented:
Actually, yes there is a property that gives you what you're looking for: Text. Unfortunately, it doesn't show up in the IntelliSense list of properties inside Access' VBA editor (Access is a pretty squirrelly client of some ActiveX controls, due to its non-standard implementation of the ISimpleFrame interface). Of course, you won't be able to bind the Text property directly to the field in your table as simply as you can the Value property. You'll probably have to use some code. Let me know if you need help with that.

Jeff
0
Sue WhiteAuthor Commented:
I do need help with the code.  It sound like you are suggesting that I not bind the richtextbox to the field but then add some code to the afterupdate property of the richtextbox that assigns the text to another bound richtextbox?
0
Jeff TennessenAssistant Vice PresidentCommented:
Not quite. Unfortunately, the RichTextBox control doesn't have an AfterUpdate event, but the workaround isn't too complicated. Add this code to the form's module:


Private mfSetVal As Boolean

Private Sub Form_Current()

  mfSetVal = True
  <richtextbox>.Text = Nz([Comment])
  mfSetVal = False

End Sub

Private Sub <richtextbox>_Change()

  If Not mfSetVal Then [Comment] = <richtextbox>.Text

End Sub


You'll need to change <richtextbox> to the name of your RichTextBox control. The mfSetCurVal flag prevents cascading updates when moving from record to record. Otherwise, it should be pretty self-explanatory. Let me know if you have any problems with it or any further questions!

Jeff
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Sue WhiteAuthor Commented:
Sorry to say you lost me.  You say "Add this code to the form's module" - if my form has a module, I don't know how to get to it.  The properties of the richtextbox control does have 5 event properties, one of which is "on updated".  I tried using this event but I get an error.

Here is the code I pasted in the on updated property of the richtextbox:

Private Sub RichTextBox1_Updated(Code As Integer)
Private mfSetVal As Boolean

Private Sub Form_Current()

  mfSetVal = True
  RichTextBox1.Text = Nz([Comment])
  mfSetVal = False

End Sub

Private Sub richtextbox1_Change()

  If Not mfSetVal Then [Comment] = RichTextBox1.Text

End Sub

Here is the error message I get when I open the form:
The expression On_current you entered as the event property setting has produced the following error:
Ambiguous name detected: form_current

Obviously I did this wrong.
0
Jeff TennessenAssistant Vice PresidentCommented:
You can't paste the code directly into the control's property sheet. I don't know that it would cause the error you describe, but it certainly wouldn't do you any good! ;-)

Go ahead and delete what you pasted there, then select Code from the View menu. The module should open. If you're using Access 97 or prior, it will open it as a child window within Access; if you're using Access 2000 or later, it will open it in a separate window called the IDE (Integrated Development Environment). The code I posted should go at the top of the module, directly under any Option statements that exist there (Option Compare Database and Option Explicit are the most common).

I realize that this can be confusing when you haven't dealt with it before. If you want, after you've opened your form's module, as described above, copy everything that's in there now and post it here. I will show you where to insert my code to make it work.

Jeff
0
Sue WhiteAuthor Commented:
Closer...  I get a different error message when I open the form.  "Runtime error.  You can't assign a value to this object."  I click debug and it highlights the code after the "then" statement in this line:
If Not mfSetVal Then [Comment] = RichTextBox1.Text

If I stop debugging and go to the form, I am able to type in [RichTextBox1] and the text appears in my send field [Comment].  However it only saves the first word I type.  In [RichTextBox1] I type "test" and then press enter and type "test2" - but only "test" is saved in the table - not both lines of text.

Thanks for the tip on how to get to that code.  Here is what it looks like:

Option Compare Database

Private mfSetVal As Boolean

Private Sub Form_Current()

  mfSetVal = True
  RichTextBox1.Text = Nz([Comment])
  mfSetVal = False

End Sub

Private Sub RichTextBox1_Change()

  If Not mfSetVal Then [Comment] = RichTextBox1.Text

End Sub

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command2_Click:
    Exit Sub

Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click
   
End Sub


BTW, I upped the points since this is more involved than you probably thought and I appreciate your help.


0
Jeff TennessenAssistant Vice PresidentCommented:
I think I know what the problem is with the "You can't assign a value to this object." The RichTextBox control's Text property has a default set in its property sheet. You should delete the value in this property (probably something like "ActiveXCtl0"), then you should no longer get this error.

The other problem is harder to figure out without having the actual app in front of me. One thing to try: make sure the MultiLine property of the RichTextBox is set to Yes.

Let me know if, after changing these properties, you still have trouble. BTW, the code you posted looks perfectly fine.

Jeff
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sue WhiteAuthor Commented:
Yes.  This solved all the issues.  Thank you very much.  One last question - what does "nz", as used here Nz([Comment]), mean.  How does it define the form field?  I've never seen it before.  Again thanks for everything.
0
Jeff TennessenAssistant Vice PresidentCommented:
You're very welcome!

Nz() is a built-in function that converts Null values into zero-length strings ("") -- thus the name Nz -- for "NulltoZerolength." The Text property of a RichTextBox control has to be a string value, so it can't be Null, but table fields (including Memo fields) can contain Null values. Using the Nz() fnction just prevents error messages if one of the records in your table has Null in the Comment.

One interesting thing: Nz() has an optional second parameter that you can use to convert a Null value to something *other* than a zero-length string. Say you were storing values from a Date field in a variable defined as Date. The field can contain a Null value but a Date variable can't. You could use Nz([DateField], #1/1/1900#) -- or something like that -- to automatically store 1/1/1900 in the variable if the field in the table is Null. Of course, when you use it in this way, the name of the function doesn't make as much sense... ;-)

Best,

Jeff
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.