Problem reading text box dynamically

[Guest]

I have a very simple master form wrapped around a subform. I have a text
field on the main form, As the user enters text here I want this to
progressively filter the subform records on a "contains" basis.

To do this I have the text field (named "UserFilter") linked to a change
handler event roughly like-

Private Sub UserFilter_Change()
Dim BaseFilter As String
Dim UserFilter As String
BaseFilter = "OrgType.OrgTypeAbbrev='SHA'"

UF = UserFilter.Text

If (UF = "") Then
Filter = BaseFilter
Else
Filter = BaseFilter & " AND Org.Name LIKE '*" & UF & "*'"
End If

End Sub


Problem:

Getting the text value from the textbox seems the leave the text selected.
This means the next character typed replaces the first one. I have a
one-character only filter!

I have tried various hacks to do with setting the selection length to zero,
but they tend to have other nasty side effects for the user. Sureley there's
a simple way to read the changing value from a text field without screwing up
the user's interaction with that field?

 

[Rob Oldfield]

It's certainly feasible to do what you're attempting here. I think your
problem is that you're applying the filter string to 'Filter' which will be
the filter for the main form rather than the subform. You'd either need to
build the filter property of the subform, or alternatively do much the same
but apply the change to the sql of the subform record source.

 

[Guest]

This particular question isn't about the "database" behaviour of access, but
basically just VBA interacting with a text control.

In essence (as I read the docs):

* To read the value of a text control you have to select it (make it the
current control).

* Select the control and you automatically (unavoidably?) place a text
selection
around the text value of the control.

* Once you've done this, the next character typed will replace the highlighted
(selected) text.

Follow this logic and you'll see that any text box being read in a tight
loop (linked to the Changed event) will only ever allow the user to type one
character, unless the user manually re-places the cursor after every
character typed, not exactly a great User Interface...

I have to believe others have met this problem and worked around it, maybe
with some form of "wait a few milliseconds for another character" trick, but
my VBA isn't that good yet.

 

[Randy Harris]

This particular question isn't about the "database" behaviour of access, but
basically just VBA interacting with a text control.

In essence (as I read the docs):

* To read the value of a text control you have to select it (make it the
current control).

* Select the control and you automatically (unavoidably?) place a text
selection
around the text value of the control.

* Once you've done this, the next character typed will replace the highlighted
(selected) text.

Follow this logic and you'll see that any text box being read in a tight
loop (linked to the Changed event) will only ever allow the user to type one
character, unless the user manually re-places the cursor after every
character typed, not exactly a great User Interface...

I have to believe others have met this problem and worked around it, maybe
with some form of "wait a few milliseconds for another character" trick, but
my VBA isn't that good yet.

I think what you want is this. Use the Change event of the text box
control. Inside that Event Handler, the .Text property will contain the
current content of the text box. This can be done without causing any
selection within the text box.

HTH!

 

[Guest]

I think what you want is this. Use the Change event of the text box
control. Inside that Event Handler, the .Text property will contain the
current content of the text box. This can be done without causing any
selection within the text box.

That's exactly what I thought I was doing, as per the code shown. However
for me it seemed to be selecting the text value. I need to check the code
again to make sure I am not selecting the control somewhere else. I do need
to set the value of the text property in at least one place (form
startup/open), and to do this from outside the control's own event handlers I
do need to explicitly select it.


Well, my initial searching around using the VBA object browser seems to
indicate that subforms don't HAVE filters, presumably the record set is "fed"
to the subform by the top-level form.

 

[Dirk Goldgar]

(see comments inline)

This particular question isn't about the "database" behaviour of
access, but basically just VBA interacting with a text control.

In essence (as I read the docs):

* To read the value of a text control you have to select it (make it
the current control).

Not exactly. The Value property of the control is available at any
time, regardless of whether the control has the focus or not.
*However*, to read the uncommitted Text property of the control while it
is being modified by the user, which is what you're trying to do here,
the control must have the focus.

For what you're doing, getting the changed text of the control on a
keystroke-by-keystroke basis, you do need the Text property. But you're
doing this during the control's Change event, and in that event the
control has the focus, so you don't have to do anything special to give
the control the focus.

* Select the control and you automatically (unavoidably?) place a text
selection
around the text value of the control.

* Once you've done this, the next character typed will replace the
highlighted (selected) text.

Your code in the Change event is not "selecting the control" -- it
already has the focus. If you did need to send the focus to the
control, you could change the selection behavior of the control (along
with all other text boxes) by setting the "Behavior Entering Field"
option on the Keyboard tab of the Tools -> Options... dialog. But I'm
fairly certain that's irrelevant to what's going on here.

Follow this logic and you'll see that any text box being read in a
tight
loop (linked to the Changed event) will only ever allow the user to
type one character, unless the user manually re-places the cursor
after every character typed, not exactly a great User Interface...

No, that's not true. I've done this many times, and never had the
problem you report.

I believe this is where your problem lies:

The above code appears to be setting the Filter property of the main
form. If the main form's FilterOn property is currently True, then each
time you change the form's Filter property, you are forcing Access to
requery the form. That causes the form to behave as if you'd just moved
to a new record -- because you have, as far as Access is concerned --
and just entered the text box for the first time. So you get the
selection behavior that is set for entry to a new control. Unless
you've changed that option I mentioned, that behavior is to select the
text in the control.

I note, by the way, that you said you want to filter the subform, not
the main form:

Your code is not only causing the problem you're seeing, it's also not
filtering the subform. If you change it to filter the subform, you
won't see the misbehavior either. I'd expect something like this to
work:

'----- start of revised code -----
Private Sub UserFilter_Change()

Dim BaseFilter As String
Dim UserFilter As String
Dim UF As String

BaseFilter = "OrgType.OrgTypeAbbrev='SHA'"

UF = Me!UserFilter.Text

With Me!YourSubformControlName.Form
If UF = "" Then
.Filter = BaseFilter
Else
.Filter = BaseFilter & " AND Org.Name LIKE '*" & UF & "*'"
End If
If Not.FilterOn Then .FilterOn = True
End With

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

Note: in the above, "YourSubformControlName" should be replaced by the
name of the subform control on the main form; that is, the control that
is displaying the subform. That name may or may not be the same as the
name of the form object it is displaying.

 

[Guest]

Dirk,
Thanks for the very helpful reply. At last some insight into this
bizarre text selection problem.

I believe this is where your problem lies:

I had done some more testing, and determined that, as you say, it's not
reading the text box that causes the problem, but the following few lines.
However I had no idea why this might be causing a problem, you have provided
some rationale here.

My 2 problems are:

1) The wacky text selection behaviour. I have a workaround that forces the
selection in the text box to be zero-length and at the end of the
string, it
sort-of works, unless the user wants to move the cursor within the
string, when
it gets very confusing.

I think some form of "wait to see if the user's finished typing" delay
would be
ideal, but i'm not sure how to achieve that.

2) If the user manages to enter a filter (say "Z") that results in no matched
records (an empty recordset), the form blows up and fails to repaint. I
haven't
found a way to trap this or recover from the failure yet.

In a traditional "code+SQL" app i'd do two queries. One to count the
length of
the recordset, and then only run the second to retrieve the records if
the count
was non-zero. Not sure if that's possible in Access.

The above code appears to be setting the Filter property of the main
form. If the main form's FilterOn property is currently True, then each
time you change the form's Filter property, you are forcing Access to
requery the form. That causes the form to behave as if you'd just moved
to a new record -- because you have, as far as Access is concerned --
and just entered the text box for the first time. So you get the
selection behavior that is set for entry to a new control. Unless
you've changed that option I mentioned, that behavior is to select the
text in the control.

I had no idea this was the behaviour. Thanks for the info, I would have
struggled with this for a long time.

I note, by the way, that you said you want to filter the subform, not
the main form:

Here's what I want to do (and what is basically working)-

* The outer form contains a text (filter) box and Forward/Back record
navigation
buttons.

* When The user opens the form, the first record is displayed in detail in the
subform. The record navigation tools show "Record 1 of 28".

* Entering a short filter string (e.g. "lon") reduces the number of matching
records. The nav area shows (say) "Record 1 of 5".

The above all works rather nicely. I was not sure "filter the subform" was
meaningful, because looking at the object attributes of the it doesn't
appear the subform has it's own recordset, the recordset is "delivered" to
the subform by the enclosing form. However your code below says otherwise.
I'll go try it and see what happens ;-)

 

[Guest]

I think I must be feeling very dim after the holiday.

My subform is called "Survey Detail" ("Survey_Detail" in VBA), but although
I can see it in the object browser as an attribute of the main form,
accessing
it in the script is proving tricky. In the change handler of the text box is
"Me" set to the control itself, or the main form?. The form of code suggested
isn't working for me-

Me!Survey_Detail.Form

need to work out what i'm missing here.

Regards: Colin

 

[Guest]

Hmm, well I tried the incantation-

With Me.Survey_Detail.Form

This seems to work but- The behaviour is exactly the same as before. I.e.
the text box ends up with all the text highlighted, and a bad filter strings
bombs the script. I wondered if I had somehow just ended up linking my script
to the same (master) form again, but when the script blows up the error
message different-

You entered an expression that has an invalid reference to the property
Form/Report

Not sure if i'm making progress here.

Regards: Colin

 

[Rob Oldfield]

Just to check...open up the main form in design view and select the subform.
View its properties. The name of the *actual* form that is the basis for
that subform will be shown as the source object on the Data tab, while the
name of the subform control (which is just another control on the main form)
will be shown on the Other tab as Name. They may, or may not, be the same.

The one that you need is the second of those.

 

[Guest]

OK, I think I have finally sorted this out. Thanks to Rob,Randy, and Dirk
for getting me on the right track. What i've found-

I have these tables-

OrgType->Org->Survey_Detail

Although the Org->Survey_Detail relationship COULD be one to many, at the
moment I only have one survey per org, so it's effectively one to one.

1) The main form filters at the Org level, and has Up/Down record nav buttons.
I DO need to filter the records on the main (outer) form. I want to
select an
org then show the (only) survey for that Org in the survey subform.

I tried filtering on the subform, but this gives the wrong result.
Instead of
filtering a list of 28 Orgs down to (say) 5, this leaves me to scroll
through all
28 Org records, but with only 5 subforms showing any data!

2) This does leave me with the problem of the text box continually
re-highlighting. I have decided to go with the workaround of using VBA
to force
the selection to the end of the text string, and zero length. It's not
perfect but
most of the time it works OK.

3) To fix the problem of the form blowing up if the filter is too
restrictive and no
records are retrieved, I discovered the incredibly useful "DCount"
function.
(Once I realised DCount uses real table names with spaces, not "VBA-ed"
names with underscores) This lets me pre-run the where clause, if the
record
count is zero I can warn the user and go back to the previous filter
string, thus
avoiding the form blowing up. It costs some performance to run almost the
same query twice for every keystroke, but my data is tiny so it's not a
problem
in this case.

So, tricky stuff for a newbie like me, but very instructive. Thanks again to
the experts for your help, I'd still be struggling otherwise.

Regards: Colin