searching field by field one record at a time

[eric]

Hello,

On my Access 2000 form, there are 20 fixed-length text
fields coming from one table. In the traditional search,
I would code it something like this:

strCriteria = "[TextField1] like '*" & [UserInputItem]
& "*'"
Set rst = Me.Recordset.Clone
Me.RecordsetClone.FindFirst strCriteria
If rst.NoMatch Then
MsgBox "No matching entries"
rst.Bookmark = varBookmark
Else
MsgBox "found it!"
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

This will only search TextField1, record for record, until
it finds the UserInputItem or eof is reached.

I would like it to loop the search column from TextField1
to TextField20 on record1 first before moving to the next
record until eof or UserInputItem found. To top things
off, I also need a FindNext feature.

Any idea how this can be done? Any input is greatly
appreciate!

eric

 

[Ken Snell]

Dim intField As Integer

Me.RecordSetClone.MoveFirst
For intField = 0 To Me.RecordSetClone.Fields.Count - 1
If InStr(Me.RecordSetClone.Fields(intField), [UserInputItem]) > 0 Then
MsgBox "found it!"
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
End If
Next intField
MsgBox "No matching entries"
Me.RecordSetClone.Bookmark = varBookmark

 

[eric]

Ken,
Thanks so much for your help! It works good ... I just
have to add the clause where it searches all the records,
rather than just record1.

eric

 

[Eric]

Ken,

A very important item that I left out in my original
posting is that upon finding the search string within a
particular textfield, I want to set focus on that
textfield control.

The statement Me.RecordsetClone.Bookmark will only show me
the record that contains the search result. It doesn't do
much good since the record consist of 20 textfields and
the control textfield contain the search item is not "set-
focused".

I tried messing w/ rst(intField +1) to get to the right
control number inorder to set focus. That didn't work.

Another thing is the findNext feature. Upon finding the
first instance of the search item, I need to be able find
the next occurrence.

I'll try the MS Access newsgroup on dejanews to see if I
can dig up anything.

Any ideas? thanks

-----Original Message-----
Oops. .. .. I see that I left out the "MoveNext" step.

Here is the corrected

 

[Ken Snell]

If you've named your textbox controls with the same name as the fields in
the recordset, then setting the focus is easy (see the one extra line I've
added):

If InStr(Me.RecordSetClone.Fields(intField), [UserInputItem]) > 0
Then
MsgBox "found it!"
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.Controls(Me.RecordsetClone.Fields(intField).Name).SetFocus
Exit Sub

If your textbox controls have different naming structure, such as "txt" in
front of the field name, then this extra line would work:

Me.Controls("txt" &
Me.RecordsetClone.Fields(intField).Name).SetFocus

With regard to wanting to "pick" up from where you're leaving off, then that
is a bit more difficult, but I would think you could do that if you change
the code to something like this (I deleted the MoveFirst line so that the
code will run from the current record to the end):

Dim intField As Integer
Do While Me.RecordsetClone.EOF = False
For intField = 0 To Me.RecordSetClone.Fields.Count - 1
If InStr(Me.RecordSetClone.Fields(intField), [UserInputItem]) > 0
Then
MsgBox "found it!"
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
End If
Next intField
Me.RecordsetClone.MoveNext
Loop
MsgBox "No matching entries"
Me.RecordSetClone.Bookmark = varBookmark


If you wanted to start on the next record, then this:

Dim intField As Integer
Me.RecordsetClone.MoveNext
Do While Me.RecordsetClone.EOF = False
For intField = 0 To Me.RecordSetClone.Fields.Count - 1
If InStr(Me.RecordSetClone.Fields(intField), [UserInputItem]) > 0
Then
MsgBox "found it!"
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
End If
Next intField
Me.RecordsetClone.MoveNext
Loop
MsgBox "No matching entries"
Me.RecordSetClone.Bookmark = varBookmark

And if you wanted to loop to the end and go back to the beginning and
continue from there, something like this:

Dim intField As Integer, varCurrBookmark As Variant
varCurrBookmark = Me.Recordset.Bookmark
Me.RecordsetClone.MoveNext
Do While Me.RecordsetClone.Bookmark <> varCurrBookmark
For intField = 0 To Me.RecordSetClone.Fields.Count - 1
If InStr(Me.RecordSetClone.Fields(intField), [UserInputItem]) > 0
Then
MsgBox "found it!"
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
End If
Next intField
Me.RecordsetClone.MoveNext
If Me.RecordsetClone.EOF = True Then Me.RecordsetClone.MoveFirst
Loop
MsgBox "No matching entries"
Me.RecordSetClone.Bookmark = varBookmark

 

[eric]

Ken,

Sorry to keep bothering you on this. The following proc
was coded to loop a search to the end and back to the
beginning.

However, I keep getting a compile error on "<>" - type
mismatch. I can't figure out while. Any insight?

______________________________________________________
Private Sub CmdSearch_Click()
Dim rst As Recordset
Dim varBookmark As Variant
Dim intField As Integer

If IsNull(Me.txtSearch) Then
MsgBox "No search item entered!", , ""
Exit Sub
End If

Set rst = Me.RecordsetClone
rst.MoveNext
varBookmark = rst.Bookmark
Do While rst.Bookmark <> varBookmark
For intField = 0 To rst.Fields.Count - 1
If InStr(rst.Fields(intField), [txtSearch]) > 0 Then
Me.Bookmark = rst.Bookmark
Me.Controls("Ctl" & rst.Fields
(intField).Name).SetFocus
Exit Sub
End If
Next intField
rst.MoveNext
If rst.EOF = True Then
rst.MoveFirst
End If
Loop
MsgBox "No matching entries"
rst.Bookmark = varBookmark

End Sub

 

[Ken Snell]

Are you sure you want to set varBookmark after you do the first MoveNext? I
think you want it before the MoveNext so that you capture the current
location, not the next location.

I am not fully familiar with bookmarks, so I'm guessing that we'll need to
capture the "new" bookmark and compare it to the previous bookmark using
variant variables. So, I've changed your code to this (using varNewBookmark
to capture it):

Private Sub CmdSearch_Click()
Dim rst As Recordset
Dim varBookmark As Variant, varNewBookmark As Variant
Dim intField As Integer

If IsNull(Me.txtSearch) Then
MsgBox "No search item entered!", , ""
Exit Sub
End If

Set rst = Me.RecordsetClone
varBookmark = rst.Bookmark
rst.MoveNext
Do
varNewBookmark = rst.Bookmark
If varBookmark = varNewBookmark Then Exit Do
For intField = 0 To rst.Fields.Count - 1
If InStr(rst.Fields(intField), [txtSearch]) > 0 Then
Me.Bookmark = rst.Bookmark
Me.Controls("Ctl" & rst.Fields(intField).Name).SetFocus
Exit Sub
End If
Next intField
rst.MoveNext
If rst.EOF = True Then
rst.MoveFirst
End If
Loop
MsgBox "No matching entries"
rst.Bookmark = varBookmark
Set rst = Nothing
End Sub

Because this is new to me, too, please be sure to let me know if this does
work for you. Then I'll know that I've correctly learned something new!
Thanks.

 

[Ken Snell]

OK - I think I have an answer.

Change your code to this so that it uses the StrComp function to compare the
bookmarks:

Private Sub CmdSearch_Click()
Dim rst As Recordset
Dim varBookmark As Variant
Dim intField As Integer

If IsNull(Me.txtSearch) Then
MsgBox "No search item entered!", , ""
Exit Sub
End If

Set rst = Me.RecordsetClone
varBookmark = rst.Bookmark
rst.MoveNext
Do While StrComp(varBookmark, rst.Bookmark, 0) <> 0
For intField = 0 To rst.Fields.Count - 1
If InStr(rst.Fields(intField), [txtSearch]) > 0 Then
Me.Bookmark = rst.Bookmark
Me.Controls("Ctl" & rst.Fields(intField).Name).SetFocus
Exit Sub
End If
Next intField
rst.MoveNext
If rst.EOF = True Then
rst.MoveFirst
End If
Loop
MsgBox "No matching entries"
rst.Bookmark = varBookmark
Set rst = Nothing
End Sub

 

[eric]

The runtime error is fixed. It finds the first record
flawlessly. Findnext is not quite working yet.

To simplify things, I am thinking of breaking the module
into 2 -- find first and findnext, creating separate
buttons for each module. However, I don't know if I will
run into problems passing around bookmarks between the two
recordsets from the two modules. Will this work? Any
insight? thnx

 

[Ken Snell]

Post the code that is working so far. Let me see what you've got in place.

 

[Eric]

I think we're almost there. The code cycles through the
fields and records now searching for the string item.
However, it starts the search with field 1 of record 2,
not record 1. Could this be because of the first
rst.movenext statement?

I need it to reset the bookmark upon entering a new search
string and restart the search.

You're a true Access guru, hat off to you!

 

[Ken Snell]

Yes, it's because of the first "movenext" step.

But I don't think you want to start with record 1 if you're already on
record 1, and you want to do the "find next" step; that will just find the
same field that you already have found in the "find first" code? And you'd
never get to record 2?

Then, again, I may be misunderstanding what you want. Your last sentence "I
need it to reset the bookmark upon entering a new search string and restart
the search. " suggests that I am. In this situation, you would need to call
the "move first" code; you could decide which to call if you store the
search string in a global variable and then compare the current search
string to the previous one in the variable; if they match, do the "find
next" steps; otherwise, do the "find first" steps.

 

[eric]

But I don't think you want to start with record 1 if
you're already on record 1, and you want to do the "find
next" step; that will just find the same field that you
already have found in the "find first" code? And you'd
never get to record 2?

But remember, my search is a little different from
traditional searches. Contrary to searching one field
record by record, I am scanning field1 through field20
before moving to the next record until eof. If multiple
instances of my search item occur in different fields of
record 1, I wouldn't catch them because of rst.movenext.

Then, again, I may be misunderstanding what you want.

This is a mistake on my part, I wasn't thinking of the
findfirst code at the time I was submitting my reply. I
think I had just enough of bookmarks for one day

 

[eric]

I posted this email on a high thread, in case you didn't
see it, I'm posting it as a reply again.

But I don't think you want to start with record 1 if

you're already on record 1, and you want to do the "find
next" step; that will just find the same field that you
already have found in the "find first" code? And you'd
never get to record 2?

But remember, my search is a little different from
traditional searches. Contrary to searching one field
record by record, I am scanning field1 through field20
before moving to the next record until eof. If multiple
instances of my search item occur in different fields of
record 1, I wouldn't catch them because of rst.movenext.

Then, again, I may be misunderstanding what you want.

This is a mistake on my part, I wasn't thinking of the
findfirst code at the time I was submitting my reply. I
think I had just enough of bookmarks for one day

 

[Ken Snell]

Ah. That means that somehow you'd need to know the order of the fields,
store the field that was the one in which the search string was found, and
then "pick up" from there in the next search. This is a bit trickier,
because the code we've worked with so far isn't "remembering" the field in
which the string was found, although it is setting the focus to it. However,
when you click on the button to run the search again, that focus is "lost"
and we either need to get the "name of the control that previously had the
focus" or we need to store the field name/number somewhere on the form or in
a global variable so that it could be used again.

OK - thinking from the top of my head here.....

Declare a global variable in the form's Declarations section:

Private intLastField As Integer


Change your "find first" code to this (note the new line after the Dim
lines, and the new line after the SetFocus step):

Dim rst As Recordset
Dim varBookmark As Variant
Dim intField As Integer

intLastField = -1

If IsNull(Me.txtSearch) Then
MsgBox "No search item entered!", , ""
Exit Sub
End If

Set rst = Me.RecordsetClone
varBookmark = rst.Bookmark
rst.MoveFirst
Do While Not rst.EOF
For intField = 0 To rst.Fields.Count - 1
If InStr(rst.Fields(intField), [txtSearch]) > 0 Then
Me.Bookmark = rst.Bookmark
Me.Controls("Ctl" & rst.Fields
(intField).Name).SetFocus
intLastField = intField
Exit Sub
End If
Next intField
rst.MoveNext
Loop
MsgBox "No matching entries"
rst.Bookmark = varBookmark
Set rst = Nothing


Change your "find next" code to this (many new lines after setting rst to
the recordsetclone and after the setfocus line later on!):

Dim rst As Recordset
Dim varBookmark As Variant
Dim intField As Integer

If IsNull(Me.txtSearch) Then
MsgBox "No search item entered!", , ""
Exit Sub
End If

Set rst = Me.RecordsetClone

If intLastField <> -1 Then
For intField = intLastField + 1 To rst.Fields.Count - 1
If InStr(rst.Fields(intField), [txtSearch]) > 0 Then
Me.Bookmark = rst.Bookmark
Me.Controls("Ctl" & rst.Fields(intField).Name).SetFocus
intLastField = intField
Exit Sub
End If
Next intField

varBookmark = rst.Bookmark
rst.MoveNext
If rst.EOF = True Then rst.MoveFirst
Do While StrComp(varBookmark, rst.Bookmark, 0) <> 0
For intField = 0 To rst.Fields.Count - 1
If InStr(rst.Fields(intField), [txtSearch]) > 0 Then
Me.Bookmark = rst.Bookmark
Me.Controls("Ctl" & rst.Fields(intField).Name).SetFocus
intLastField = intField
Exit Sub
End If
Next intField
rst.MoveNext
If rst.EOF = True Then
rst.MoveFirst
End If
Loop
MsgBox "No matching entries"
rst.Bookmark = varBookmark
Set rst = Nothing

 

[Eric]

That did it! Perfect! Thanks again for your hard work!

Eric

 

[Ken Snell]

Glad to hear it's working. I just noted that I omitted an "End If" from my
posted code; did you fix it? Here are corrected "find next" code steps:

Dim rst As Recordset
Dim varBookmark As Variant
Dim intField As Integer

If IsNull(Me.txtSearch) Then
MsgBox "No search item entered!", , ""
Exit Sub
End If

Set rst = Me.RecordsetClone

If intLastField <> -1 Then
For intField = intLastField + 1 To rst.Fields.Count - 1
If InStr(rst.Fields(intField), [txtSearch]) > 0 Then
Me.Bookmark = rst.Bookmark
Me.Controls("Ctl" & rst.Fields(intField).Name).SetFocus
intLastField = intField
Exit Sub
End If
Next intField
End If

varBookmark = rst.Bookmark
rst.MoveNext
If rst.EOF = True Then rst.MoveFirst
Do While StrComp(varBookmark, rst.Bookmark, 0) <> 0
For intField = 0 To rst.Fields.Count - 1
If InStr(rst.Fields(intField), [txtSearch]) > 0 Then
Me.Bookmark = rst.Bookmark
Me.Controls("Ctl" & rst.Fields(intField).Name).SetFocus
intLastField = intField
Exit Sub
End If
Next intField
rst.MoveNext
If rst.EOF = True Then
rst.MoveFirst
End If
Loop
MsgBox "No matching entries"
rst.Bookmark = varBookmark
Set rst = Nothing