Check a table for correct keywords used in a description

[doocrazy]

I am using Access 97. I am a beginning VBA programer. I have a table that contains 2 fields. 1) Drawing Number 2) Description of that number We are wanting to standardize the description so that certain parts are not described in more than on way. I am looking for the simplest way to do this. Want I want is for this procedure to check the description field in this table and make sure that all the words used match a table containing all the keywords available. After it finds one that does not match i would like it to show the error in the table so it can be changed and then continue looking for more errors.

Any help would be greatly appreciated.

 

[Aivars]

Hi, doocrazy!

You can try to use function what I wrote.

Example for your table fields:
Me.txtBoxForKeyWords -> text box for key words phrase entering;
cmdSelect -> commandbutton for selection running

Sub cmdSelect_Click()
On Error GoTo Err_cmdSelect_Click
Dim strKeyWords As String
Dim strSelectCriteria As String
Dim strSQL As String
Dim strWhere As String
Dim rst As Recordset
Dim blnIsFound As Boolean

'Get entered phrase
strKeyWords = Me.txtBoxForKeyWords
'Transform entered key words phrase
strSelectCriteria = CriteriaOfKeyWords(strKeyWords, "Description", False)
If strSelectCriteria = "" Then 'Wrong key words phrase
GoTo Exit_cmdSelect_Click
End If
'Compose SQL text
strSQL = "Select DrawingNumber, Description From MyTable "
strWhere = "Where " & strSelectCriteria
strSQL = strSQL & strWhere & ";"

'Check for found records
Set rst = CurrentDb.OpenRecordset(strSQL)
blnIsFound = Not rst.EOF
rst.Close
Set rst = Nothing

Exit_cmdSelect_Click:
If Not blnIsFound Then 'Not found any record
MsgBox "There are not records with your search criterias."
Me.SubForm.Form.FilterOn = False
Else
'Set filter of subform to found key words
Me.SubForm.Form.Filter = strSelectCriteria
Me.SubForm.Form.FilterOn = True
End If
Exit Sub

Err_cmdSelect_Click:
MsgBox "Error No " & Err.Number & vbLf & Err.Description
Resume Exit_cmdSelect_Click

End Sub

'-------------------------
'Function

Public Function CriteriaOfKeyWords(strPhrase As String, _
strFieldName As String, _
Optional ExactPhrase As Boolean = False) As String
'This function transforms Key Words Phrase
'to phrase with asterisks and field name
'as well as criteria's parameter "Like"
'Optional ExactPhrase -> True if it's needed to find whole phrase
'If is omited ExactPhrase then all words is found
'strFieldName -> Field name in what it's needed words to find
Dim strCriteria As String
Dim n As Integer

'Remove spaces from start and from end of key words phrase
strPhrase = Trim(strPhrase)
If ExactPhrase = True Then 'Whole phrase
CriteriaOfKeyWords = strFieldName & " Like '*" & strPhrase & "*'"
Else
n = InStr(1, strPhrase, " ", vbTextCompare)
If n = 0 Then
strCriteria = strCriteria & strPhrase & "*'"
GoTo FinishOfCompose
End If
Do
'Look for spaces in phrase
'and change its to asterisk
'add field name and param "Like"
n = InStr(1, strPhrase, " ", vbTextCompare)
If n > 0 Then
If strCriteria <> &quot;&quot; Then
strCriteria = strCriteria & &quot; Or &quot; & strFieldName & &quot; Like '*&quot;
End If
strCriteria = strCriteria & Left(strPhrase, n - 1) & &quot;*'&quot;
strPhrase = Trim(Mid(strPhrase, n))
Else
If strPhrase <> &quot;&quot; Then
If strCriteria <> &quot;&quot; Then
strCriteria = strCriteria & &quot; Or &quot; & strFieldName & &quot; Like '*&quot;
End If
strCriteria = strCriteria & strPhrase & &quot;*'&quot;
End If
Exit Do
End If
Loop
FinishOfCompose:
If strCriteria <> &quot;&quot; Then
strCriteria = strFieldName & &quot; Like '*&quot; & strCriteria ' & &quot;*'&quot;
End If
CriteriaOfKeyWords = strCriteria
End If

End Function
'-------------------------
?CriteriaOfKeyWords(&quot;Find key words&quot;,&quot;Description&quot
..... Description Like '*Find*' Or Description Like '*key*' Or Description Like '*words*'

?CriteriaOfKeyWords(&quot;Find key words&quot;,&quot;Description&quot;,True)
..... Description Like '*Find key words*'

Aivars

 

[doocrazy]

Thanks.

That seems o.k. now where do i put all this information at?

 

[Aivars]

Creta textbox on form and simply change your desctiption manually or by VBA codes, or by update query.

Aivars