Using FindRecord & FindNext in VB modules

[nrms]

Hi,

I have a form that displays a recordset of data from a table. I have a
textbox for users to enter a search string & a button labelled "Find" to do
the search. The OnClick event procedure is defined as:

Private Sub FindButton_Click()
On Error GoTo Err_FindButton_Click

Screen.PreviousControl.SetFocus

If Me!FindButton.Caption = "Find" Then
DoCmd.FindRecord "e", acAnywhere, , acSearchAll, False, acAll
Me!FindButton.Caption = "Next"
Else
DoCmd.FindNext
End If

Exit_FindButton_Click:
Exit Sub

Err_FindButton_Click:
MsgBox Err.Description
Resume Exit_FindButton_Click

End Sub

(Note I am currently testing with a fixed search string "e")

For some reason I cannot fathom, this procedure only partially works - it
finds the first occurrence of "e" and changes the button caption OK; but then
when I press the button a second time on the form, it should be executing a
FindNext method, but all that happens is the first occurrence of the "e" is
highlighted again.

What am I over-looking here?

Can anyone point me in the direction of examples of how to do this kind of
task?

Thanks
Nigel

 

[Douglas J. Steele]

Have you checked to ensure that it's actually going to the Else part of the
code?

 

[nrms]

Yes, it is definitely executing the Else part of the function.

However, I now have more info. If I get rid of the acAll bit of the search
(in other words to only search the current field not all fields in the
recordset); then the FindNext bit works just fine. So I think the problem
lies in the Screen.previouscontrol.setfocus bit; but I can't work out how to
alter this.

Nigel

 

[Douglas J. Steele]

Your SetFocus statement sets focus to the start of the field. Try using the
text box's SelStart property to put focus at the end of the control on
subsequent searches:

Private Sub FindButton_Click()
On Error GoTo Err_FindButton_Click

Dim ctlPrevious As Control

Set ctlPrevious = Screen.PreviousControl
ctlPrevious.SetFocus

If Me!FindButton.Caption = "Find" Then
DoCmd.FindRecord "e", acAnywhere, , acSearchAll, False, acAll
Me!FindButton.Caption = "Next"
Else
ctlPrevious.SelStart = Len(Nz(ctlPrevious, ""))
Screen.PreviousControl.SetFocus
DoCmd.FindNext
End If

Exit_FindButton_Click:
Exit Sub

Err_FindButton_Click:
MsgBox Err.Description
Resume Exit_FindButton_Click

End Sub

The statement

ctlPrevious.SelStart = Len(Nz(ctlPrevious, ""))

will ensure that the cursor is at the end of the text box with focus, since
presumably you've already found the text in that control. If you want to be
able to continue searching in the same text box to find subsequent
occurrences, you'll need to adjust how you calculate what to use for
SelStart.

 

[nrms]

Regretably your code seems to show the same behaviour as mine did. The best I
have got so far is using:

Private Sub FindButton_Click()
On Error GoTo Err_FindButton_Click

Screen.PreviousControl.SetFocus

If Me!FindButton.Caption = "Find" Then
DoCmd.FindRecord "e", acAnywhere, , , , acAll, False
Me!FindButton.Caption = "Next"
Else
DoCmd.GoToRecord Record:=acNext
DoCmd.FindNext
End If

Exit_FindButton_Click:
Exit Sub

Err_FindButton_Click:
MsgBox Err.Description
Resume Exit_FindButton_Click

End Sub

That is, putting the DoCmd.GotoRecord line into the FindNext branch.
Unfortunately although the selection now moves through the dataset on the
form, the logic misses some matches by jumping the record.

In anycase, I have now realised the situation will get more complicated when
I add a textbox to the form to get a user search string into the FindRecord
method.

I am going to have to rethink & do some more research. Thanks for trying to
help,

Nigel

 

[Malcolm Cook]

Nigel,

I understand you are trying to use Screen.PreviousControl.SetFocus to ensure that the subsequent FindNext starts searching where the
previous search left off.

The problem seems to be, as Douglas Steele pointed out, that your method does not set the focus past the previous matching text in
the control, so it is found again.

I think your implementation of his suggested fix however fails because the value of the .selstart property is lost when your use
clicks on the Findbutton.

So, you must either (1) remember the control and it's.selstart in global (or static) vars, or (2) not use a command button (instead
use a command bar or macro).

In any case, you are still faced with knowing whether to change the caption to "Next" and when to change it back to "Find". I think
this will require yiour code "knowing" if the FindRecord and FindNext succeeded or not. If you can do this, then at the same time,
you probably can set your global/static and take option (1).

I would take option (2).

Cheers,

Malcolm Cook

 

[nrms]

Malcolm

Thanks for giving this problem long consideration. As I eluded to in my last
msg, I realised that I was going to have even worse problems once I add a
txtbox for users to enter a search string; so I re-thought the problem, and
came up with a solution which works well. My code behind the search form is:

Private Sub Form_Open(Cancel As Integer)
Me!SearchText.SetFocus
End Sub

Private Sub SearchText_GotFocus()
Me!SearchText = Null
Me!FindButton.Caption = "Find"
End Sub

Private Sub SearchText_AfterUpdate()
FindButton_Click
End Sub

Private Sub FindButton_Click()
On Error GoTo Err_FindButton_Click

If IsNull(Me!SearchText) Then GoTo Exit_FindButton_Click
If Me!FindButton.Caption = "Find" Then
Me!SearchBlock.SetFocus
DoCmd.FindRecord Me!SearchText, acAnywhere
Me!FindButton.Caption = "Next"
Else
Screen.PreviousControl.SetFocus
DoCmd.FindNext
End If

Exit_FindButton_Click:
Exit Sub

Err_FindButton_Click:
MsgBox Err.Description
Resume Exit_FindButton_Click

End Sub

Private Sub ExitButton_Click()
DoCmd.Close acForm, "Search_Form"
End Sub
---------------------------------
When the form opens the focus is on the SearchText field; user enters data
and either presses return or clicks the "Find" box. Seachblock is the control
which displays a 1-line string for each record in the data-table I am
searching. Focus is started on the Searchblock for the first find.
Subsequent presses of the (now labelled) "Next" button continues the search
down the table, using screen.previouscontrol to ensure focus moves back to
the SearchBlock. Clicking back on the SearchText control at any time, clears
the search for a new search and rests the button label back to "Find".

Seems to work just fine so far.

Cheers
Nigel