Need a good search method for vBA.

[Patrick]

HI!!

I'm looking for a search engine that would cycle through
the contents of a specific column and move the form to
that location.
Example:
Looking For lastName="Doe", then if it finds a match the
form navigation is sent to that record, else does
nothing ,a warning to user is given.
It would be greate if it could work for the following
problems as well:
Last Name: O'Ray ' Should take into account the ' sign.
last Name: James ' should work regardless if its a 'J'
or 'j'. Not case sensitive.
And, if the user just needs to find the records starting
with the letter 'J' it should position to the first of
those records.
Right-bow, the code I have doesn't do a lot for me.
Here a copy of the code if it can help my situation.

Dim dbs As Database
Dim rsnew As Recordset
Dim idx As Integer
Dim cpt As Boolean
Dim reponse2, reponse3 As String
On Error Resume Next

reponse2 = strRep & "*"
reponse3 = UCase(strRep) & "*"

If IsNull(strRep) Or strRep = "" Then
' exit.

Else

Set dbs = CurrentDb
Set rsnew = dbs.OpenRecordset("SELECT * " _
& " FROM [t Ortho Data]")

With rsnew
.MoveFirst

While Not .EOF
If .Fields(lookUpName) Like reponse2 then
cpt = True
idx = .AbsolutePosition
GoTo skip
End If
.MoveNext
Wend

End With
skip:

If cpt Then
idx = idx + 1
cpt = False
DoCmd.GoToRecord acDataForm, "frmfurniture", acGoTo, idx
Else
MsgBox"No data found for " & strRep & ",try again!"
End If

reponse2 = ""
strRep = ""

End If
End Function

Any help would be appreciated,Thanks in advance,
PAtrick

 

[Dirk Goldgar]

HI!!

I'm looking for a search engine that would cycle through
the contents of a specific column and move the form to
that location.
Example:
Looking For lastName="Doe", then if it finds a match the
form navigation is sent to that record, else does
nothing ,a warning to user is given.
It would be greate if it could work for the following
problems as well:
Last Name: O'Ray ' Should take into account the ' sign.
last Name: James ' should work regardless if its a 'J'
or 'j'. Not case sensitive.
And, if the user just needs to find the records starting
with the letter 'J' it should position to the first of
those records.
Right-bow, the code I have doesn't do a lot for me.
Here a copy of the code if it can help my situation.

Dim dbs As Database
Dim rsnew As Recordset
Dim idx As Integer
Dim cpt As Boolean
Dim reponse2, reponse3 As String
On Error Resume Next

reponse2 = strRep & "*"
reponse3 = UCase(strRep) & "*"

If IsNull(strRep) Or strRep = "" Then
' exit.

Else

Set dbs = CurrentDb
Set rsnew = dbs.OpenRecordset("SELECT * " _
& " FROM [t Ortho Data]")

With rsnew
.MoveFirst

While Not .EOF
If .Fields(lookUpName) Like reponse2 then
cpt = True
idx = .AbsolutePosition
GoTo skip
End If
.MoveNext
Wend

End With
skip:

If cpt Then
idx = idx + 1
cpt = False
DoCmd.GoToRecord acDataForm, "frmfurniture", acGoTo, idx
Else
MsgBox"No data found for " & strRep & ",try again!"
End If

reponse2 = ""
strRep = ""

End If
End Function

Any help would be appreciated,Thanks in advance,
PAtrick

I take it you don't want to use the built-in Find feature, available
through the Edit -> Find... menu item or by clicking the Find
("binoculars") button on the toolbar.

If that's not what you want, you might use the combo box wizard to help
build a combo box to "find a record on my form". That's quite a handy
device, though not exactly what you asked for, and it won't help you
find repeated occurrences that match a partial name. Or you can roll
your own find function using a text box for entering the search string
and a command button to perform the find. For example, suppose you had
text box "txtFindWhat" and command button "cmdFind", and you were
looking for a match in the LastName field of the form. You might have
the following event procedures in your form's code module:

'----- start of code -----
Private Sub cmdFind_Click()

Static strFindWhat As String
Static strCriteria As String
Dim blnNewSearch As Boolean

If IsNull(Me.txtFindWhat) Then Exit Sub

If Me.txtFindWhat <> strFindWhat Then
strFindWhat = Me.txtFindWhat
blnNewSearch = True
End If

With Me.RecordsetClone

If blnNewSearch Then
strCriteria = _
"LastName Like """ & _
Replace(strFindWhat, """", """""", _
, , vbBinaryCompare) & _
"*"""
.FindFirst strCriteria
Else
.FindNext strCriteria
End If

If .NoMatch Then
MsgBox "No matching record found.", , "Not Found"
Else
Me.Bookmark = .Bookmark
End If

End With

End Sub


Private Sub txtFindWhat_AfterUpdate()

If Not IsNull(Me.txtFindWhat) Then
Me.cmdFind.SetFocus
cmdFind_Click
End If

End Sub
'----- end of code -----

 

[John Vinson]

HI!!

I'm looking for a search engine that would cycle through
the contents of a specific column and move the form to
that location.

Example:
Looking For lastName="Doe", then if it finds a match the
form navigation is sent to that record, else does
nothing ,a warning to user is given.
It would be greate if it could work for the following
problems as well:
Last Name: O'Ray ' Should take into account the ' sign.

Delimit with " and you won't have any problems with '. If you need to
search for strings which might contain either ' or " or both, it's a
bit more work.

last Name: James ' should work regardless if its a 'J'
or 'j'. Not case sensitive.

Access/JET is not case sensitive (and it's a bit of a pain to do case
sensitive searching).

And, if the user just needs to find the records starting
with the letter 'J' it should position to the first of
those records.

A Combo Box with the default autocomplete setting of True will do this
for you with no code at all.

Right-bow, the code I have doesn't do a lot for me.
Here a copy of the code if it can help my situation.

ummm... I'm confused. Access provides a wide variety of search tools
"out of the box". Why do you feel that it's necessary to write
extensive VBA code to do something that the user interface does
already, out of the box?

I'd suggest, if you want something a step easire, using an unbound
Combo Box on the Form with AfterUpdate code like

Private Sub cboFindName_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[lookupName] = """ & Me!cboFindName & """"
If rs.NoMatch Then
MsgBox "This name was not found"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

The Combo Box would be based on a query

SELECT DISTINCT lookupName FROM [t Ortho Data] ORDER BY lookupName;

 

[Patrick]

Thanks very much for your suggestions, I belive that I can
solve my litle problem now...