Why won't my OnChange event work?

[Seth Schrock]

I have a textbox that I'm using as a search field: txtAddress. I have a query that is based on that field using the Like keyword so that I can have wildcards. What I would like to happen is that as I type each character into txtAddress, the subform (that is based on the query) requeries. Here is what I currently have:

Private Sub txtAddress_Change()
If Me.Dirty = True Then Me.Dirty = False
Me.sfrmAddressSearchResults.Requery

End Sub

It seems like it is always one change behind. At first I didn't have the 2nd line and nothing happened. That is when I figured out that the value of the field hadn't changed for the query to see it yet, so I had it save and then requery. Now it will change, but if I start to enter 11, and then backspace and then type 80, then it still shows the addresses that starts with 11. If I manually run the query after making the change to txtAddress and then leave the field, the query runs exactly as expected so I know it is in my OnChange event that I'm messing up.

[NeoPa]

Me refers to the form rather than the particular field Seth.

.Text & .Value properties should, between them, provide what you're after. I'm unsure which is the more useful at this stage, but testing will determine that.

[Seth Schrock]

I thought that doing the Me.dirty = false saved the record. I'm not familiar with the .Text or .Value (I assume you meant .Value not .Vaue) properties, so do I do Me.Text.Dirty = False or Me.txtAddress.T ext.dirty = False?

[TheSmileyCoder]

I presume as you mention txtAddress is used as a search field that it is unbound. As such I don't think it should matter saving the record.

When you reference a control whether by code or query, you will usually reference the VALUE of the control.

Forms!frm_Example!txtAddress.Value

Now the default property of a textbox is the value, therefore it is possible to write:

Forms!frm_Example!txtAddress

and it will be equivalent to the line written before.

HOWEVER (There had to be one) the OnChange event is special. The OnChange event is fired whenever you make a change to the TEXT in the textbox. This is different from the BeforeUpdate which fires before the VALUE of the textbox is changed. As you type the TEXT is changed, when you hit enter (or tab or click away) the VALUE is changed. So while you are still typing the value of the control remains unchanged. So if you want something to work with the OnChange event you need to reference the .TEXT property of the control:

Forms!frm_Example!txtAddress.TEXT
'or
Me.txtAddress.Text

A caveat is that I believe that you can only use the .TEXT if the control has focus. What I would do would look something like this:

Private Sub txtAddress_Change()
  dim strFilter as String
  strFilter="AddressFieldName like '*" & me.txtAddress.TEXT & "*'"
  Me.sfrmAddressSearchResults.Form.Filter=strFilter
  If not me.sfrmAddressSearchResults.Form.Filteron then
    Me.sfrmAddressSearchResults.Form.FilterOn=True
  End If     
End Sub

[NeoPa]

Seth:
I'm not familiar with the .Text or .Value (I assume you meant .Value not .Vaue) properties, ...

Absolutely. My bad. I've fixed the typos now.

Seth:
I thought that doing the Me.dirty = false saved the record, so do I do Me.Text.Dirty = False or Me.txtAddress.T ext.dirty = False?

No. That was the track I was trying to lead you away from.

I would have hoped that you would have looked at the .Text property after my last post. If you were to, you would discover that it reflects the typing immediately (Within the _Change() event procedure), and can be used as a filtering value.

[Seth Schrock]

I have read the TextBox.Text property from MSDN, but I'm still not positive that I know where you are going, but I do have a guess (Smiley's post helped). So instead of having the query have txtAddress as it criteria, I should use filters on the form via VBA. This would allow me to use the Me.txtAddress.t ext property as the filter criteria in the OnChange event just like Smiley has posted.

[NeoPa]

Sometimes it's easier to try something out rather than looking it up in Help or the web Seth. That's what I did. I knocked up a very quick procedure on an existing TextBox control on one of my forms and noticed the results :

Private Sub XXX_Change()
    Debug.Print Me.XXX.Value, Me.XXX.Text
End Sub

I typed the characters "ABcd" into the control ([XXX]) and saw (in the Immediate Pane) :

Null    A
Null    AB
Null    ABc
Null    ABcd

From there it's a short step to the workable solution you're talking about. Very similar to Smiley's suggestion as you've noticed. Your original post indicated to me you didn't need the rest of it explained as you already had that part understood well enough (That was my reading of it at least).

PS. BTW, your original approach of saving the record would have been relatively pointless, except in that it probably forced the control to consider itself as having been updated. IE. Similar to tabbing out of the control. You need neither of those now you understand how to use the .Text property.

[Seth Schrock]

I will give it a try when I get to work on Monday. Thanks.

[Seth Schrock]

That worked. I actually have four different fields for search fields (txtAddress, txtCity, txtState, txtZIP) so I had to make a few additions to the code, but I did get it to work. Please let me know what you think of my final code.

Private Sub FormFilter()
Dim strFilter As String

Me.txtAddress.SetFocus
strFilter = "PropAddress like '" & Me.txtAddress.Text & "*'"

Me.txtCity.SetFocus
strFilter = strFilter & "AND PropCity Like '" & Me.txtCity.Text & "*'"

Me.txtState.SetFocus
strFilter = strFilter & "AND PropState Like '" & Me.txtState.Text & "*'"

Me.txtZIP.SetFocus
strFilter = strFilter & "AND PropZIP Like '" & Me.txtZIP.Text & "*'"


Me.sfrmAddressSearchResults.Form.Filter = strFilter

If Not Me.sfrmAddressSearchResults.Form.FilterOn Then
    Me.sfrmAddressSearchResults.Form.FilterOn = True
End If

End Sub

txtAddress OnChange event (all the others are the same, but reference themselves):

Private Sub txtAddress_Change()
FormFilter

Me.txtAddress.SetFocus
With Me.txtAddress
    .SelStart = Len(.Text)

End With
End Sub

[NeoPa]

Seems the bee's knees Seth. One point I would make is that line #4 of the OnChange event would be better placed after, and to take advantage of, the With statement in line #5.

[Seth Schrock]

Duh... That is so simple even I should have seen that. Oh well.

[NeoPa]

I'm sure you would have, given time to allow your concentration to recede from the main issue of getting it to work at all. It's not uncommon to miss details like that while concentrating on just getting it to work.