Findrecord using a input box

[rjoshi2]

I am trying to use a message box to find a record. I want it to look at all the text in the form to see if it can find a match for one word or one phrase. Here is my code. I think I could do something like this could work. My guess is that when I get this working (if at all) it will be very slow mad it will only find the exact match (not *word or phrase* like I want). Any suggestion on how to get this to work or a better way of doing this would be appreciated.

Thank You,
rjoshi2

Here is my code so far.
Private Sub GFind_Click()

Me.AllowEdits = False

Dim lStartPosition As Long
Dim textFieldName As String ' may need to be different datatype
Dim counter As Integer

For counter = 1 To 10 Step 1
If counter = 1 Then
textFieldName = Me.ID
ElseIf counter = 2 Then
textFieldName = Me.Date_Received
ElseIf counter = 3 Then
textFieldName = Me.Requisition_Number
ElseIf counter = 4 Then
textFieldName = Me.Date_Sent_To_Reviewer
ElseIf counter = 5 Then
textFieldName = Me.Date_Returned_from__Reviewer
ElseIf counter = 6 Then
textFieldName = Me.Date_of_Document
ElseIf counter = 7 Then
textFieldName = Me.Sent_To
ElseIf counter = 8 Then
textFieldName = Me.Fiscal_Year
ElseIf counter = 9 Then
textFieldName = Me.Date_Approved
ElseIf counter = 10 Then
textFieldName = Me.Delete_Requisition
End If

' now a global variable
' Dim sfind As String

Me.textFieldName.SetFocus

lStartPosition = Me.Recordset.AbsolutePosition
sfind = InputBox("Enter search criteria", "Find", sfind)
'sdind should be *sfind*
If Len(sfind & "&quot = 0 Then
Exit Sub
End If
If (sfind = textFieldName) Then
DoCmd.FindRecord sfind, FindFirst:=False
Else
DoCmd.FindRecord sfind
End If
If (sfind <> textFieldName) Then
MsgBox &quot;No record found&quot;, vbOKOnly + vbInformation, &quot;Not found&quot;
Exit Sub
End If

If (Me.Recordset.AbsolutePosition = lStartPosition) Then
MsgBox &quot;Current record is the last to exist with that search criteria&quot;, vbOKOnly + vbInformation, &quot;Last Record&quot;
End If

Next counter
End Sub

 

[SgtJarrow]

rjoshi2,

You have cross posted this in the Forms forum as thread702-434904.

Please post each topic in one forum at a time. Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com