Macro to format data in MS Access Form

[DrPete]

Hello
I've just joined, and this is my first question!
I have an 2003 MS Access Form named frmCurrentBills with a text field in the form txtboxBackground.
The default font for the data in that field is Arial 10 (set everywhere as far as I can see), but often after a cut and paste to that field from an external source the font ends up being the external source font, which results in me having to manually reformat.
I want to a macro or event that will go to that txtboxBackground, select the data, and change the data to Arial 10.

DoCmd.GoToControl "txtBoxBackground" will take me there, but I can’t get passed this.
Any help would be much appreciated.

[jimatqsi]

Check the "text format" property on that textbox. I believe if you change it to "Plain Text" you will not have that problem.

Jim

[DrPete]

Thanks, Jim
But I need the box to be rich text because the boss likes bullet lists..

[NeoPa]

In the AfterUpdate() Event Procedure of the control you would set the .FontName & .FontSize properties back to the desired values :

Private Sub txtBoxBackground_AfterUpdate()
    With Me.txtBoxBackground
        If .FontName <> "Arial" Then .FontName = "Arial"
        If .FontSize <> 10 Then .FontSize = 10
    End With
End Sub

It shouldn't be necessary to set the focus back to the control in order to do this.

NB. This is air-code so please test it out first.

[DrPete]

Thanks, NeoPa
I created an event procedure for the text box (AfterUpdate), I entered your code exactly as you stated, saved it and all the rest. Back in the form, I then changed the data font and size, and then moved elsewhere in the form, pressed other buttons etc, but the text did not change to Arial 10. Nothing seemed to happen that I could see.

[NeoPa]

Can you confirm the code is actually running?

The property needs to be set to "EventProcedure" and the security settings must allow any code to run.

[DrPete]

I have other EventProcedure and Macros connected to other boxes and buttons, and they all run okay.
I think the code is running, but just not doing its job. But how can I tell?

[NeoPa]

DrPete:
I think the code is running, but just not doing its job. But how can I tell?

Either add a breakpoint to the first line (Debugging in VBA), or add a new line in the procedure with just :

Stop

In either case if it doesn't break then it isn't running. If it does stop then just hit F5 to continue normally.

[DrPete]

Thanks, NeoPa
Yes, the code is running, but for some reason won't change the font name and size.

[NeoPa]

Very curious. Are you sure your database is an MDB (2003 format)? RTF was only introduced in 2007 (ACCDB). I can't imagine how formatted text can be stored or shown in a TextBox that's MDB.

[DrPete]

Sorry, my mistake. It is 2010 version. Does this make a difference?

[jforbes]

If you have set your TextBox to use RichText you will run into a lot of interesting little challenges like this. If you can talk your user or Boss into using Plain Text instead you will probably save yourself a lot of headaches in the future.
The biggest drawback I've seen is that the HTML Tags can get in the way of a SQL Where clause and records that you would think would be included in a Recordset aren't because of the hidden tags. The second and equally disturbing drawback is the amazing amount of extra characters that can end up in the field.

But if you can't talk your Boss out of the Rich Text setting, then using a Regex to strip out Tags might work for you. There are lots of ways to go about manipulating HTML, but it gets pretty crazy pretty fast. Regex is comparatively simple,https://en.wikipedia.org/wiki/Regular_expression, but even though it is simpler, it can be a nightmare of it's own.

I use this function. If your up for it, you'll want to put it in a VBA Code Module:

Public Function replaceWithRegex(ByVal sTemp As String, ByRef sPattern As String, ByRef sReplaceWith As String) As String
    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True
    RegEx.Pattern = sPattern
    replaceWithRegex = RegEx.Replace(sTemp, sReplaceWith)
End Function

Then you can call it like so:

Private Sub Command2_Click()
    Me.Text0.Value = replaceWithRegex(Me.Text0.Value, "<\/*font([\s\S]*?)>", "")
End Sub
 

When the Command2 button is clicked, it will send the contents of the TextBox named Text0 to replaceWithRegex() along with a Regex pattern that defines an HTML <Font> tag to replaced by an empty string.

I played around with this a little, 'cause it looked fun, and when I copy text from a web page that has text of varying sizes into a TextBox, the text sizes vary as they do on the original web page. When the button is clicked, the font size becomes uniform and that of the TextBox's Font Size Property.

Hopefully this will help you. And if you want to explore some Regex patterns on your own, this site is really helpful: http://www.regexr.com/

[DrPete]

Thank you. I will give it a try as soon as I can get exclusive control of the database. Cheers!

[NeoPa]

DrPete:
Sorry, my mistake. It is 2010 version. Does this make a difference?

Only in as much as it's hard to work when you don't know the facts as you have to try to form a picture in your mind of what's what. A bit like that picture painters paint of bricks that always appear to lead on to the next one in sequence, yet go around in a loop. Each connection looks fine, but none of it actually makes sense. If one were to ask me a technical question about it, such as which is the highest, then I'd be sunk. There is nothing there to work with logically. That's why it's so important to present the information carefully and accurately when asking questions.

On this issue I'm afraid I have to admit that I've reached the end of my road. I've never used them (RTF TextBoxes) myself, except for trying things out for people such as yourself. I think I've summed up my whole understanding of the subject in my previous posts. Sorry.

[DrPete]

Neopa,
Thank you for your help. Trying something that does not work is not a fail, but the elimination of one or more issues or possible solutions. Knowing what will not work helps you narrow down what will or may work. You have been a great help. Thank you. I haven't tried RegEx just yet, as I needed some clear time and sole user access to the database. I hope it works. Cheers

[DrPete]

jforbes - you da man! It works, works works! Thank you a million. You made me look good to the boss, who thinks I am the best thing since sliced bread!
But one more question - I want any italics or bold formatting to be not italics and not bold after pasting into my text box. Can that be done?

[jforbes]

Glad to hear it.

Here are examples of Bold, Italics and a few other Regex patterns. The one at the end should remove them all. You'll need to play around with it to make sure that it is working for you.

' Bold
Me.Text0.Value = replaceWithRegex(Me.Text0.Value, "<\/*b([\s\S]*?)>", "")
' Strong
Me.Text0.Value = replaceWithRegex(Me.Text0.Value, "<\/*strong([\s\S]*?)>", "")
' Italic
Me.Text0.Value = replaceWithRegex(Me.Text0.Value, "<\/*(i[\s\S]*?)>", "")
' Emphasized
Me.Text0.Value = replaceWithRegex(Me.Text0.Value, "<\/*(em[\s\S]*?)>", "")
' Small
Me.Text0.Value = replaceWithRegex(Me.Text0.Value, "<\/*(small[\s\S]*?)>", "")
' Marked/Highlighted
Me.Text0.Value = replaceWithRegex(Me.Text0.Value, "<\/*(mark[\s\S]*?)>", "")
' Deleted
Me.Text0.Value = replaceWithRegex(Me.Text0.Value, "<\/*(del[\s\S]*?)>", "")
' Inserted
Me.Text0.Value = replaceWithRegex(Me.Text0.Value, "<\/*(ins[\s\S]*?)>", "")
' Subscript
Me.Text0.Value = replaceWithRegex(Me.Text0.Value, "<\/*(sub[\s\S]*?)>", "")
' Superscript
Me.Text0.Value = replaceWithRegex(Me.Text0.Value, "<\/*(sup[\s\S]*?)>", "")
 
' Everything
Me.Text0.Value = replaceWithRegex(Me.Text0.Value, "<\/*b([\s\S]*?)>|<\/*strong([\s\S]*?)>|<\/*(i[\s\S]*?)>|<\/*(em[\s\S]*?)>|<\/*(small[\s\S]*?)>|<\/*(mark[\s\S]*?)>|<\/*(del[\s\S]*?)>|<\/*(ins[\s\S]*?)>|<\/*(sub[\s\S]*?)>|<\/*(sub[\s\S]*?)>|<\/*(sup[\s\S]*?)>", "")

[DrPete]

jforbes
Brilliant advice again. One thing I have noticed is that if a text box is blank and without any data having been entered into it, and then I press the command button to format the text box, I get an VBA error message stating "invalid use of Null. I have tried inserting an If Me.Text0.value <> Null Then... (regex code) type expression, but that seems to render the code impotent. Any clues here?

[jforbes]

Doing something like the following would probably be a better way of calling the function as it would only call it if there was something to work on and it would address Nulls.

Private Sub Command2_Click()
    Dim sText As String
    sText = Nz(Me.Text0.Value, "")
    if Len(sText) > 0 Then Me.Text0.Value = replaceWithRegex(sText , "<\/*font([\s\S]*?)>", "")
End Sub

[NeoPa]

Hi JForbes.

I want to thank you for this code too. I use Replace() in my code a great deal. So much even, that I have my own MultiReplace() function that handles multiple pairs of find and replace parameters. I also use RegEx in my text editor to do some very powerful data (and code) preparatory work. What I didn't have until now was procedure to handle replacing with RegEx in code.

Now I do!! Excellent.

Public Function RegExReplace(varString As Variant _
                           , strFrom As String _
                           , strTo As String) As String
    If varString > "" Then
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = strFrom
            RegExReplace = .Replace(CStr(varString), strTo)
        End With
    End If
End Function

PS. This handles a varString parameter of a control that has nothing (Null) in it.

[jforbes]

Glad to hear it NeoPa, and I like the addition of checking for a blank string.

[NeoPa]

: - )