set focus error on SelLength method

B

Bob Richardson

What I'm trying to do is use a text box to filter my input, to make searches
easier. When the user types the first letter (e.g. "J") only records with a
last name starting with "J" appear. He next types "O" after the "J" and only
records starting with "JO" appear.

I've placed the following in my KeyUp event for the text box (txtLast)

If Me!txtLast = "" Then
Exit Sub
Else
Qry = "[LastName] like " & "'" & txtLast.Text & "*'"
Me.Filter = Qry
Me.FilterOn = True
txtLast.SelLength = 0
txtLast.SelStart = Len(Me!txtLast)
End If

This approach works perfectly UNTIL a combination of letters is typed that
results in no records appearing (e.g. "JOZ").

If I don't use the SelLength and SelStart methods, I can't type more than
one letter in my search box (txtLast) unless I use the mouse to set the
entry point at the end.

What the fix to this approach, or the better way to achieve my desired
result?
 
A

Allen Browne

Bob, there are several issues with this approach.

You may find the Change event works better than KeyUp, as it includes the
newly entered character (esp. useful if it was Backspace).

If the search form is read-only, or AllowAddtions is set to No, you are
likely to run into serious issues when there is no match. The form cannot go
to a new record, and so the Detail section goes blank. Even if your search
box is in a different section of the form (e.g. Form Header), it is likely
to have display and focus errors at this point. Details:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

If the new record is available, you don't have those problems, so the
approach should work. However, it is very heavy on data access, which makes
it an undesirable interface if the data is being shared on a network. Access
databases do have a way of growing beyond what you envisioned originally, so
I would discourage you taking this approach in general.

A very simple alternative is to use a combo box so the user can select the
person they want, and you can go to that record in the combo's AfterUpdate
event. There is no re-reading and filtering of the data, so it is much more
efficient. There is a combo wizard that does this for you, or you can use
the code in:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

The combo approach is useful up to a few thousand records. If you have tens
of thousands of records, you can delay loading the combo until the user has
typed 3 or 4 characters. Although this does cause a data access at this
point, you are probably drawing only a few hundred records, and it does not
occur after every keystroke. Details on that approach in:
Combos with Tens of Thousands of Records
at:
http://allenbrowne.com/ser-32.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bob Richardson said:
What I'm trying to do is use a text box to filter my input, to make searches
easier. When the user types the first letter (e.g. "J") only records with a
last name starting with "J" appear. He next types "O" after the "J" and only
records starting with "JO" appear.

I've placed the following in my KeyUp event for the text box (txtLast)

If Me!txtLast = "" Then
Exit Sub
Else
Qry = "[LastName] like " & "'" & txtLast.Text & "*'"
Me.Filter = Qry
Me.FilterOn = True
txtLast.SelLength = 0
txtLast.SelStart = Len(Me!txtLast)
End If

This approach works perfectly UNTIL a combination of letters is typed that
results in no records appearing (e.g. "JOZ").

If I don't use the SelLength and SelStart methods, I can't type more than
one letter in my search box (txtLast) unless I use the mouse to set the
entry point at the end.

What the fix to this approach, or the better way to achieve my desired
result?
Click to expand...
 
B

Bob Richardson

Thanks Allen. I took your idea and went with the Combo box approach. I was
shying away from this approach because my right wrist has been hurting, and
thus I prefer typing over mousing. But it wasn't worth fighting the problems
that occurred when no records appeared.


Allen Browne said:
Bob, there are several issues with this approach.

You may find the Change event works better than KeyUp, as it includes the
newly entered character (esp. useful if it was Backspace).

If the search form is read-only, or AllowAddtions is set to No, you are
likely to run into serious issues when there is no match. The form cannot go
to a new record, and so the Detail section goes blank. Even if your search
box is in a different section of the form (e.g. Form Header), it is likely
to have display and focus errors at this point. Details:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

If the new record is available, you don't have those problems, so the
approach should work. However, it is very heavy on data access, which makes
it an undesirable interface if the data is being shared on a network. Access
databases do have a way of growing beyond what you envisioned originally, so
I would discourage you taking this approach in general.

A very simple alternative is to use a combo box so the user can select the
person they want, and you can go to that record in the combo's AfterUpdate
event. There is no re-reading and filtering of the data, so it is much more
efficient. There is a combo wizard that does this for you, or you can use
the code in:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

The combo approach is useful up to a few thousand records. If you have tens
of thousands of records, you can delay loading the combo until the user has
typed 3 or 4 characters. Although this does cause a data access at this
point, you are probably drawing only a few hundred records, and it does not
occur after every keystroke. Details on that approach in:
Combos with Tens of Thousands of Records
at:
http://allenbrowne.com/ser-32.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bob Richardson said:
What I'm trying to do is use a text box to filter my input, to make searches
easier. When the user types the first letter (e.g. "J") only records
Click to expand...
with
a
last name starting with "J" appear. He next types "O" after the "J" and only
records starting with "JO" appear.

I've placed the following in my KeyUp event for the text box (txtLast)

If Me!txtLast = "" Then
Exit Sub
Else
Qry = "[LastName] like " & "'" & txtLast.Text & "*'"
Me.Filter = Qry
Me.FilterOn = True
txtLast.SelLength = 0
txtLast.SelStart = Len(Me!txtLast)
End If

This approach works perfectly UNTIL a combination of letters is typed that
results in no records appearing (e.g. "JOZ").

If I don't use the SelLength and SelStart methods, I can't type more than
one letter in my search box (txtLast) unless I use the mouse to set the
entry point at the end.

What the fix to this approach, or the better way to achieve my desired
result?
Click to expand...
Click to expand...
 
A

Allen Browne

Bob, I prefer typing over mousing as well.

You are aware that you can type into a combo (without dropping it down), and
have it auto-complete?

If you do need to drop the combo without going for the mouse, Alt+Down
should do it.
 
Top