[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Strip rtf tags from richtextbox control on an Access form

Posted on 2004-11-17
9
Medium Priority
?
415 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:Sue White
  • 5
  • 4
9 Comments
 
LVL 8

Expert Comment

by:Jeff Tennessen
ID: 12607674
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
 

Author Comment

by:Sue White
ID: 12607735
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
 
LVL 8

Expert Comment

by:Jeff Tennessen
ID: 12608343
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

by:Sue White
ID: 12609776
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
 
LVL 8

Expert Comment

by:Jeff Tennessen
ID: 12609892
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
 

Author Comment

by:Sue White
ID: 12610157
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
 
LVL 8

Accepted Solution

by:
Jeff Tennessen earned 2000 total points
ID: 12610293
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
 

Author Comment

by:Sue White
ID: 12610385
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
 
LVL 8

Expert Comment

by:Jeff Tennessen
ID: 12610486
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question