Words Not Allowed Inside A Textbox 3

[ballbearing]

Creating an Employee Review form in which supervisors enter comments. There are certain words that are not allowed to be used within the comments boxes. (discriminatory, racial, language..blah.blah) I can create the list in a table..no problem. Question is how can that box be tested for any words in the table's list(using code) once the user, say, moves to the next comment box or record?

 

[jrbarnett]

Hi,

In the BeforeUpdate event of the control, loop through each record of the table with a recordset object, then check for its existence in the text with InStr.
If you find any, set Cancel to true (so the record won't get saved) and display an appropriate message.

John

 

[MakeItSo]

I would do it in the AfterUpdate event, avoiding the need of Cancel.
Else John's right, doing it with a recordset and the FindFirst method for the forbidden word should perfectly do it. If "NoMatch=False", then you can display a msgbox, set the value of the control to vbnullstring and set the focus on that control.

 

[jrbarnett]

MakeItSo

If it is done in the AfterUpdate event, by then the record has been saved and the data is in the table, so you then have the trouble of making sure that the supervisors amend their comments to remove or rephrase offending words.

John

 

[MakeItSo]

hmmmh - Good point. ;-)

 

[PeteJohnston]

If you use Split to get the individual words you can then check whether they are in your table of words.

Private Sub txtField_LostFocus()

Dim rst As Recordset
Dim strSQL As String
Dim Words() As String, x As Integer

If Not IsNull(txtField) Then
  Words = Split(txtField)
  For x = LBound(Words) To UBound(Words)
      strSQL = strSQL & "', '" & Words(x)
  Next
  strSQL = Mid(strSQL, 4) & "')"
  strSQL = "SELECT * from tblWords WHERE Word IN (" & strSQL
  Set rst = CurrentDb.OpenRecordset(strSQL)
  If rst.RecordCount = 0 Then

' Do your error message stuff here

  End If
  rst.Close
End If

End Sub

I just created a dummy table called tblWords with columns ID and Word and it seems to work Okay.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[MakeItSo]

Pete: Nice approach, the split is important, else you'd skip all entries of combined words.
But:
a) If rst.RecordCount = 0 Then
' Do your error message stuff here
End If
I guess that should be If rst.Record > 0 (If offending words are present...

b) strSQL = strSQL & "', '" & Words(x)
Don't you have to delete the first 3 chars, else the first word would be ', 'offending word', '...
and thus add
strSQL=Right(strSQL,len(strSQL)-3)
before Set rst =
Or is this covered qith your MID function?

Andy


Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[PeteJohnston]

Andy

Sorry about the rst.RecordCount = 0 woopsie. I did a cut and paste from my little dummy app and I had a label on the form where I changed the caption from "All words are Okay" to "Some words not allowed" depending on the record count. Sure as fate, I had the Okay bit in the first part of the IF statement!

The Mid(String, Start, Length) function does indeed take care of that. The second parm tells it where to start and the third is the length you want to extract. If you leave the third parameter out it takes the rest of the string. It saves you doing the len(strSQL)-3 bit.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[MakeItSo]

Good to know. (Hope I remember to use Mid next time before I start nesting LEFTs and RIGHTs and LENs...) ;-)

 

[ballbearing]

Thanks for the response! I'm new here and I gotta ask...how did you all get so friggin' smart??? Everyone around me think that I'm a genius..but after coming in here I've realized I'm as dumb as a box of rocks when it comes to VBA stuff. Gawd....some day. Right?

Thanks again!

 

[MakeItSo]

Just every day a little more, and since I joined Tek-Tips: in a few months as much as in three years before!
BTW: I sometimes feel "as dumb as a box of rocks" too when I look at posts by the VIPs here. ;-)

 

[ballbearing]

Hmmmm..I seem to be overlooking something here.
1 of the catagories being entered is a field called "Cooperation". Ok..so my code lookes like this:

Private Sub Cooperation_LostFocus()
Dim rst As Recordset
Dim strSQL As String
Dim Words() As String, x As Integer

If Not IsNull(Cooperation) Then
Words = Split(Cooperation)
For x = LBound(Words) To UBound(Words)
strSQL = strSQL & "', '" & Words(x)
Next
strSQL = Mid(strSQL, 4) & "')"
strSQL = "SELECT * from tblWords WHERE Word IN (" & strSQL
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
MsgBox "Oooops!"
End If
rst.Close
End If

End Sub

It keeps hanging on this line:
Set rst = CurrentDb.OpenRecordset(strSQL)

Any thoughts?

 

[PeteJohnston]

When you says hanging, what do you mean exactly? Does it just stop there or does it give you an error message? If it does, what is the message? Have you created the table tblWords with a column called Word to hold the words which are not allowed? If you run it in debug and stop at the OpenRecordset line what value is in the strSQL field?

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[ballbearing]

I get a runtime error 13 Type Mismatch

then the highlighted line in code says "rst=Nothing" when I place the cursor over that line.

Gawd I love this Access stuff..too bad I don't know what I'm doing.

 

[ballbearing]

Oh yeah...by the way..I forgot to tell you that I have indeed created the table with the correct field(s). I doubled checked to be sure.

Gawd I love this Access stuff..too bad I don't know what I'm doing.

 

[ballbearing]

OK..update on the error message I'm getting:

Run-time error '3075':

Syntax error (missing operator) in query expression 'Word IN('Most',
cooperative','employee','He's', 'here','to','whatever','is','asked','of','him')'.

I'm kinda assuming there's a problem with the following line of code:
strSQL = "SELECT * from tblWords WHERE Word IN (" & strSQL

I've tried different things, but no luck so far. Anything giving me this much grief has GOT to be something simple but...





Gawd I love this Access stuff..too bad I don't know what I'm doing.

 

[jrbarnett]

It's one of two things:

1) The apostrophes inside words such as "he's" which is causing Access to think that there is an extra item afterwards.
You can get around this by using either double quote marks around the edge (eg "he's&quot or doubling up on internal apostrophes (eg 'he''s')

2) There is no apostrophe to start the word "cooperative" - this would have the same problems as no. 1 with the added solution of putting an extra apostrophe at the start.

John

 

[ballbearing]

Makes sense.

Next question:
Can I code this to ignore words with apostrophes and quotes?

It's going to look pretty funky printing these reports with the extra quote marks and stuff.


Randy

Gawd I love this Access stuff..too bad I don't know what I'm doing.

 

[jrbarnett]

The easiest way around it to avoid having the double quotes in your code is to replace the apostrophe ' with the right hand apostrophe ` (to the left of the number 1 on your keyboard).
You can either do this programmatically, looping through and using the Replace function from Access 2002 (or the version I wrote if you are using an earlier version, available from the Tek-tips VBA database from

http://www.jrbarnett.freeserve.co.uk)

and simply replace ' with `
or use the double quote technique in your line:

strSQL = strSQL & """, """ & Words(x)

John

 

[ballbearing]

I've spent boo-koo hours the past 2 days trying to get this 1 procedure to work. Got great advice on this site...searched all over the internet for other possible solutions. I've tried referencing just about every library Microsoft could dream up and even considered referencing the Library of Congress. I copy-pasted so many lines of code that I can barely read the "Ctrl","C"and "V" keys any longer. I've tried code that makes sense and code that didn't have a snowball's chance of working. I've gone to sleep thinking about it and awakened continuing on with the last thought I had had. Still..after all this, it still will not work. Beginning to see why you guys love this stuff.

I'm beaten.
My head hurts.
I'm moving on
...and I'll (more than likely) be back for my next problem.


Thanks to all that tried to help.



Gawd I love this Access stuff..too bad I don't know what I'm doing.