Keydown event confusion

  • Thread starter Thread starter Roland Alden
  • Start date Start date
R

Roland Alden

I have a search box on a form and a handler for the keydown event, such that
when keydown sees KeyCode = 13 I do a search for a record matching what is
in the search text box.

Now I notice several things going wrong :)

Keydown seems to get called elsewhere on the form, not just when the search
box has the focus. In particular, when I enter a record number in the record
selector box and hit enter I get into my keydown handler. This seems wrong
but if not is there a best way to make sure the focus is in the search box
before I proceed to do a search and conflict with the desire on the part of
the user to go to a particular record number?

Secondly, I have this rather inelegant bit of code doing the search

Me.Recordset.find s
If (Me.Recordset.EOF) Then
Me.Recordset.MoveFirst
Me.Recordset.find s
End If

The problem is that if the search is going to hit record 5 but the current
record is > 5 then the user actually sees a bit of screen flashing as the
cursor goes to EOF, MoveFirst goes to record 1 and the search is done again.
Is there a way to avoid this? What I'd really like to see is a Find method
that was smart enough to wrap around automatically.
 
When you press Enter, the control's AfterUpdate event fires. It would be
much easier just to use that event than to use KeyDown and try to trap
Enter.

If you must use KeyDown, make sure you are using the KeyDown event for the
control, not that of the form. In fact turn off the form's KeyPreview
property if you don't need it.

To find the record regardless of where it is, search in the form's
RecordsetClone. This kind of thing:

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID] = " & Me.cboMoveTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
 
Thanks for the tips. AfterUpdate works but with the recordset clone I get a
Method or data member not found on rs.FindFirst.

I suspect it makes a difference that this is an ADP project?
 
Yes, it does. The RecordsetClone in an ADP will be an ADO recordset, so you
will need to use the Find method instead of the FindFirst that DAO uses.
 
Yes, it does. The RecordsetClone in an ADP will be an ADO recordset, so
you
will need to use the Find method instead of the FindFirst that DAO uses.
Click to expand...

And if I'm not mistaken Find only runs forward to EOF? Thus the little dance
to check for EOF and restart the search after a MoveFirst is necessary?

This is the code I have now:

Me.Recordset.MoveNext ' skip the record the user is seeing
Me.Recordset.find s

If (Me.Recordset.EOF) Then
Me.Recordset.MoveFirst
Me.Recordset.find s
End If

I don't seem to notice any difference in behavior if I use a recordsetclone
instead of recordset.

Should I?
 
Roland Alden said:
And if I'm not mistaken Find only runs forward to EOF? Thus the
little dance to check for EOF and restart the search after a
MoveFirst is necessary?

This is the code I have now:

Me.Recordset.MoveNext ' skip the record the user is seeing
Me.Recordset.find s

If (Me.Recordset.EOF) Then
Me.Recordset.MoveFirst
Me.Recordset.find s
End If

I don't seem to notice any difference in behavior if I use a
recordsetclone instead of recordset.

Should I?
Click to expand...

Try this:

With Me.RecordsetClone
.MoveFirst
.Find s
If .EOF Then
MsgBox "Not found!"
Else
Me.Bookmark = .Bookmark
End If
End With
 
Dirk Goldgar said:
Try this:

With Me.RecordsetClone
.MoveFirst
.Find s
If .EOF Then
MsgBox "Not found!"
Else
Me.Bookmark = .Bookmark
End If
End With
Click to expand...

The problem with the above (aside from the fact that you don't say why
recordsetclone is better than recordset :) is:

By doing the MoveFirst first you always just find the first match. If you
are just looking for a unique key then this is fine (since there is only one
anyway). However, in my case it really is a general search on strings for
which there can be multiple matches. Therefore I want to allow the user to
search to a next hit and then if that's still not the right one search again
forward from that point and keep going until all records have been visited
at least once.
 
Roland said:
The problem with the above (aside from the fact that you don't say why
recordsetclone is better than recordset :) is:

By doing the MoveFirst first you always just find the first match. If you
are just looking for a unique key then this is fine (since there is only one
anyway). However, in my case it really is a general search on strings for
which there can be multiple matches. Therefore I want to allow the user to
search to a next hit and then if that's still not the right one search again
forward from that point and keep going until all records have been visited
at least once.
Click to expand...


Just replace the .MoveFirst with:

.BookMark = Me.Bookmark

And replace the MsgBox with .MoveFirst and another .Find
sequence.

The reason for using RecordsetClone is that there have been
several reports of anomalous behavior under some(?)
circumstances using Recordset.

I'm pretty sure Dirk will verify that when he has a chance
to get back to you.
 
Roland Alden said:
The problem with the above (aside from the fact that you don't say why
recordsetclone is better than recordset :) is:

By doing the MoveFirst first you always just find the first match. If
you are just looking for a unique key then this is fine (since there
is only one anyway). However, in my case it really is a general
search on strings for which there can be multiple matches. Therefore
I want to allow the user to search to a next hit and then if that's
still not the right one search again forward from that point and keep
going until all records have been visited at least once.
Click to expand...

I chose Me.RecordsetClone rather than Me.Recordset so that I could
reposition the recordset to the first record without having affecting
which record was displayed on the form. It's also a convenient way to
ensure that if no record is found to match your criteria, the form's
current record doesn't move. Usually, in my experience, that's what
people want.

I didn't understand that you would be searching for multiple matching
records. In that case, you probably need separate "Find First" and
"Find Next" buttons -- or maybe even "Find First", "Find Next", and
"Find Previous" buttons or something like them -- to be used in
conjunction with a function something like this:

'----- start of "air code" #1 -----
Private Function FindOnForm(sCriteria As String, iWhich As Integer)

' We define iWhich as having these possible values:
' 0 = find first
' 1 = find next
' 2 = find previous

With Me.RecordsetClone

If .RecordCount = 0 Then Exit Function

Select Case iWhich
Case 0
.MoveFirst
.Find sCriteria
Case 1
.Bookmark = Me.Bookmark
If Not .EOF Then
.Find sCriteria, 1
End If
Case 2
.Bookmark = Me.Bookmark
If Not .BOF Then
.Find sCriteria, 1, adSearchBackward
End If
End Select

If .EOF Or .BOF Then
MsgBox "Not found"
Else
Me.Bookmark = .Bookmark
End If

End With

End Function
'----- end of "air code" #1 -----

On the other hand, if you wanr to have only one search function that
always searches forward from where you are, but loops around to the
start of the recordset if necessary, then maybe something like this
would do:

'----- start of "air code" #2 -----
Private Function FindOnForm(sCriteria As String)

Dim fFound As Boolean

With Me.RecordsetClone

If .RecordCount = 0 Then Exit Function

.Bookmark = Me.Bookmark

.Find sCriteria, 1

If .EOF Then
.MoveFirst
.Find sCriteria
End If

If .EOF Then
fFound = False
ElseIf .Bookmark = Me.Bookmark Then
fFound = False
Else
fFound = True
End If

If fFound Then
Me.Bookmark = .Bookmark
Else
MsgBox "Not found"
End If

End With

End Function
'----- end of "air code" #2 -----

Those are completely untested, but they may give you some ideas.
 
Back
Top