How do I stop my vba code from continuing to surround my comments with parentheses?

[wordbrew]

Hello all,

This is my first time posting so I hope I can get some insight into my problem.

I have a subform with a comments section, that, when a comment is entered, and then tabbed off of (loses focus) the code surrounds that statement in parentheses. This is working great. The only problem is that if the that comment is clicked again or is otherwise put back in focus, then when it is tabbed off of or loses focus again, then the vba code surrounds it with more parentheses. If I was the only one using this database I wouldn't care because I could easily just manually avoid the problem, but there are others who use it, and there comments end of looking something like this (((((((Vacation Day)))))) instead of (Vacation Day). Is there anyway to stop my code from executing if my value is met. I've tried a few things but they aren't solving the problem. This is what my current VBA code looks like.

Private Sub Comments_LostFocus()
Dim Comm As String, OpenPAr As String, CLosePar As String
If Not IsNull(Me.Comments.Value) Then
Comm = Me.Comments.Value
OpenPAr = "("
CLosePar = ")"
Me.Comments.Value = OpenPAr & Comm & CLosePar
Else
End If

End Sub

I appreciate any help at all. Let the parenthetical madness stop!!!!!!!!

[ADezii]

Private Sub Comments_LostFocus()
Dim Comm As String
Dim OpenPAr As String
Dim CLosePar As String

OpenPAr = "("
CLosePar = ")"

If Not IsNull(Me.Comments) Then
  Comm = Replace(Replace(Me.Comments, OpenPAr, ""), CLosePar, "")
    Me.Comments = OpenPAr & Comm & CLosePar
End If
End Sub

[wordbrew]

Wow ADezii. That worked perfectly! I was going nuts trying to solve that. Everytime I learn more about VBA I always come out with more questions lol. Now I'm going to have to examine the code you gave me, so I can truly understand how it worked and solved my problem. Looks like I need to learn about replace commands.

Thanks so much ADezii. You are a brilliant man my friend.

[ADezii]

You are quite welcome, all fixes should be so easy! (LOL)

[NeoPa]

While that will strip the parentheses from your Comments control, it will strip all the parentheses from your comments control. If you plan to disallow anyone from entering these characters at all in their comments, this will be no problem. Otherwise however, you will find a very difficult problem to debug when your users complain strange things are happening to the comments they enter.

A solution to parentheses only found at both the start and end of the text is :

Private Sub Comments_LostFocus()
  Dim strCom As String

  strCom = Me.Comments
  If Right(strCom) = "(" _
  And Left(strCom) = ")" Then _
    strCom = Mid(strCom, 2, len(strCom) - 2)  
  Me.Comments = "(" & strCom & ")"
End Sub

[edsuk]

Just for curiosity, why are you using the 'LostFocus' event, surely it would make more sense to place this sub procedure in the 'AfterUpdate' event. So that the code only runs after the data has been modified?

[NeoPa]

A good point. I certainly concur.

I expect the answer is as simple as that the OP is simply quite inexperienced. We experts should probably have picked up on that one and mentioned it I expect, but I for one was in a bit of a rush at the time. Well done for picking it up :)

[ADezii]

The LostFocus() Event could be used to Update existing Records where multiple Parenthesis are found, whereas the AfterUpdate() would not unless a change was made. Did I just invent a New Update Method? (LOL)!

[NeoPa]

I wouldn't shout about it ADezii. It might help a little, but it's hardly a sound method for clearing up existing problems in data.

I had to laugh though :D

[ADezii]

Still don't know when I'm kidding ya, do ya?

[NeoPa]

I always know ADezii. (Read my post again.)

It's easy - It's when you post (LoL).

[wordbrew]

Thanks for all your responses gentleman, I really do appreciate it. Fortunately, ADezii's original solution did solve my problem. Now when an item is tabbed off of, it becomes surrounded in parenthesis (which I'm having it do because I have a separate textbox on the main form pulling in information from separate sources including the comments section, and having the comments in parentheses sets them apart), and if the comments are clicked again, tabbed back on and off, etc...only the original parentheses are left behind. This is perfect.

Edsuk and NeoPa you guys are totally right, I could have put this as an AfterUpdate event. Lol, I guess I just like the instant gratification of seeing the comments in the subform datasheet get surrounded by parentheses when they are tabbed off of, plus it does have the added benefit of being an easy way of cleaning up pre-existing multiple parentheses debacles.

And you are quite right NeoPa. I'll always have more to learn. Luckily with the quick responses from you kind sirs, I can feel secure that if I encounter any more roadblocks, the Bytes community will set me back on track.

Thanks again guys!

[NeoPa]

You're welcome. Always nice to read such a pleasant response.

I suppose that means I'll have to set post #2 as Best Answer now. Darn! Shucks! ADezii gets it again! :D

[ADezii]

plus it does have the added benefit of being an easy way of cleaning up pre-existing multiple parentheses debacles.

And you thought my new Update Creation was useless! (LOL)

[NeoPa]

Not Useless! I would never say that!

Just of very little use is all :D