warning and not allow certain words being entered (1 Viewer)

[rainbows]

i would like to be able to stop people entering certain words within a sentence in the text field MaterialPUR. and warning them if they try to.
the words for example are.

drw
drg
drawing
w room

see screenshot below as an example

thanks steve

 

[theDBguy]

Your options include using InStr() or regular expressions (RegEx).

 

[jdraw]

Seems this would include a function to Change the unacceptable string with a standard, acceptable term.

Change------acceptedTerm
drw .................... drawing
drg .................... drawing

If the sample shown is representative, what is the difference between Material and MaterialPUR? If these are standard terms wouldn't they be suited to standard, authoritative definitions and be selectable via Combobox? That way you get consistent terminology and spelling.
Not sure why people would be entering freeform text for these--but I know nothing of your environment.

 

[MajP]

This is works well IMO.
1. Allows you to make a table of disallowed words and replacement
2. Allows find and replace of the found words with the replacement word or you can edit the replacement word.
3. Alerts the user on before update to fix or continue.

Update demo in #8

 

[ebs17]

.Pattern = "\b" & SearchWord & "\b"

You can test for more than one word in one step:

.Pattern = "\b(drw|drg|drawing|w room)\b"

 

[Jason Lee Hayes]

This is works well IMO.
1. Allows you to make a table of disallowed words and replacement
2. Allows find and replace of the found words with the replacement word or you can edit the replacement word.
3. Alerts the user on before update to fix or continue.

Thanks for sharing this; i think it works well too... Simple yet effective.....

 

[MajP]

You can test for more than one word in one step:

Yeah, but not really of any value. If I searched all at once by looping the table and creating a large string, there is no way to do then do individual find and replace or make the message showing which of the words are found and which are not.

 

[MajP]

Here is an update. I had a bug. If you changed the word, it did not use the change. Also if you simply want to delete the word hit a space bar.

 

[ebs17]

there is no way to do then do individual find and replace or make the message showing which of the words are found and which are not.

Sub test_words()
     Const csExample = "This is a long long story. If you read it, you come to heaven."
     Static oRegEx As Object
     Dim oM As Object
     Dim i As Long
     Dim j As Long
     Dim sResult As String
 
     If oRegEx Is Nothing Then Set oRegEx = CreateObject("VBScript.RegExp")
     With oRegEx
        .Global = True
        .Pattern = "\b(is|long|story|heaven)\b"
        Set oM = .Execute(csExample)
        i = oM.Count
        If i > 0 Then
            For j = i - 1 To 0 Step -1
                ' FirstIndex is 0-based
                Debug.Print j, oM(j), oM(j).FirstIndex, oM(j).Length
            Next
        End If
     
        sResult = .Replace(csExample, "xxx")
        Debug.Print sResult
     End With
End Sub

If you know the content, position and length of a word, you can easily replace it with something else (start the loop from the end).

 

[MajP]

If this pont is an academic exercise to show that you can return a group of found words then that is great. But as an improvement to my solution, that would be making clean simple code overly complicated. You have to loop the bad words table and then concatenate the search words. Then you have to take the match collection and loop that. Determine which word was found and then find in the recordset the replace word. Not seeing any possible advantage.

 

[ebs17]

an academic exercise

No. It is practically feasible.

Regarding the concerns: I would of course not search in consecutive loops, but create a dictionary/collection and then solve it immediately with a simple run through the MatchCollection.

What is simple and clean code also lies in the consideration of each one. I certainly wouldn't act as awkwardly as you describe it. It is at least a debatable suggestion.

Simplicity: Whether everyone understands this in detail and can implement it is another question that I cannot answer.

//Edit:
In any case, I wouldn't create RegEx objects on the fly, but use one for everything.

 

[ebs17]

concatenate the search words

A simple and performant task when using concatenation methods like Getstring (ADODB.Recordset).
Normally you have a small collection of helpful libraries and solutions that you just have to access.

 

[MajP]

Not saying it cannot be done, just saying it seems to me you are putting it all together only to break it back apart. If it works for you then have at it. Just seemed to me like adding complexity for pretty simple approach. Now we need to bring in a library of code to do concatenation?

 

[ebs17]

Now we need to bring in a library of code to do concatenation?

No. I wonder about this question.
Do you know ADODB.Recordsets, also from your own application?

But the RegEx methods in query-ready (high-performance) form would be the content of such a small library - because in a non-small application, the probability increases that such methods will be used more than once in a meaningful and profitable way.