Obtaining textbox value in change event handler

Question

I've written a form that performs queries asynchronously as text is typed into a textbox, however I somewhat arbitrarily seem to get the following error thrown: "You can't reference a property or method for a control unless the control has focus."

The immediately relevant code is:

Dim UpdateRequested As Boolean
Dim qryText As String

.
.
.

Private Sub txtBox_Change()
    qryText = txtBox.Text
    UpdateRequested = true
End Sub

Some place in the ellipses is the code that handles dynamically loading an ADODB record set, populating a local table, and refreshing a sub form. However, even when I disable this code, the problem persists: Sometimes I get the error. Sometimes I do not.

This seems to be persistent through closing the database and reopening it. Every time it starts working again, it's because I've been fooling around with code in the debugger, but I'm not sure what exactly is causing it to magically "just work" or what is causing it to not work at all.

Update

Just to make things more puzzling, I added a couple of simple event handlers:

Private Sub txtBox_GotFocus()
    MsgBox "Got focus"
End Sub

Private Sub txtBox_LostFocus()
    MsgBox "Lost focus"
End Sub

I run the form. I click in the test box. I receive the "Got focus" message. As soon as I type I see the error as described above. If I re-open the form, I can click between the text box in question (which itself is unbound) and a bound text box in the sub form and see both "Got focus" and "lost focus" messages as one would expect. Furthermore, showing a message box with the current value of "Screen.ActiveControl.Name" shows the expected name just before the Text property is accessed.

Gourmet

@HansUp: qryText = txtVendorName.Text – Gourmet

Answer 1

I know this is an old thread but it's the first I found when I had the same problem. None of the answers helped except Kaganar's own solution, which pointed me in the right direction. I'm guessing the reason people had trouble reproducing the error is there are some important details missing from Kaganar's description:

1. The Textbox was in the form header (or footer).
2. The form did not allow additions.

Because I believe the full answer is...

The Text property of any control is inaccessible when the form has a record source with no records to edit

I think there is part of Access that does not realise the textbox exists :) To understand how that might come about...

• Put the unbound TextBox in the detail of the form
• Do not allow additions
• Set the recordsource to return no records
• Open the form.

Hey presto! No Textbox.

Return a record, or allow additions, or delete the recordsource, et Voila! There is your Textbox with it's Text.

Graminivorous

This was KILLING me. Thank you, thank you, thank you. – Trotyl

Close. Text only "exists" while the control has the focus. And popping the MsgBox removes the focus – Prink

Answer 2

I added a text box named txtFoo to my form. Here is the procedure for its change event.

Private Sub txtFoo_Change()
    Debug.Print "Value: " & Nz(Me.txtFoo.value, "*Null*") & _
        "; Text: " & Nz(Me.txtFoo.Text, "*Null*")
End Sub

Then, with nothing in txtFoo (IOW its value is Null) when I type "abc" into txtFoo, here is what I see in the Immediate window.

Value: *Null*; Text: a
Value: *Null*; Text: ab
Value: *Null*; Text: abc

Basically, each character I add to the text box triggers its change event and prints the text box's current contents to the Immediate window.

As far as I understand, you want to do something similar ... except you want a different action in place of Debug.Print. Take another look at your change event procedure and compare it to mine.

Private Sub txtBox_Change()
    qryText = txtVendorName.Text
    UpdateRequested = true
End Sub

That is the change event for a control named txtBox. Within that procedure you reference the .Text property of a control named txtVendorName. However txtBox is the active control at the time its change event code runs ... so you can not access the .Text property of txtVendorName because it is not the active control.

Given that this problem surfaces for only the one form, but not on other new forms, I would suspect the problem form has become corrupted. Read the 2 answers to this SO question and try decompile to cure the corruption: HOW TO decompile and recompile. Decompile is often recommended as a routine practice during development.

You could also use the undocumented Application.SaveAsText method to save your form as a text file. Delete the bad form, and use Application.LoadFromText to import the saved text copy.

Make sure you have a backup copy of your db file in case anything goes wrong.

Bumpy

Whoops, good catch.. Unfortunately, what you caught was a bad problem description and not bad code in this case. (Actually, the text box is named "txtVendorName", but I made it more generic in the example but forgot to change that one line.) – Gourmet

If you temporarily substitute my change event procedure for yours, and give it the proper control name in all 3 places, do you still get the intermittent error about "can't reference a property or method for a control unless the control has focus", a different error, or no error at all? – Bumpy

I don't know if it's relevant, but I do see that HansUp has Me. in his code, where the reference is missing in yours – Shephard

@HansUp, I get the same error message when I do as you've suggested. @Sean, Given this, it looks like Me. did not make the difference. – Gourmet

I'm not sure -why- this form does this -- I can get similar code to work on new, blank forms. Additionally, if I make a nice new shiny text box on this form in the footer (the usual one is in the header) an give it your change event handler, I still get the error message. – Gourmet

Answer 3

To set or return a control's Text property, the control must have the focus, or an error occurs.
To move the focus to a control, you can use txtBox.SetFocus or DoCmd.GoToControl "txtBox".

Also, the Text property is not always available:
While the control has the focus, the Text property contains the text data currently in the control; the Value property contains the last saved data for the control. When you move the focus to another control, the control's data is updated, and the Value property is set to this new value. The Text property setting is then unavailable until the control gets the focus again.

Shephard

Tried both prior to posting this. Alas, it doesn't fix the issue if I do it in the change event handler prior to trying to access the Text property. – Gourmet

For that matter, it doesn't matter if I reload the form after putting it in the form's Load event handler, too. – Gourmet

found another annoyance with the Text property. Fixing answer! – Shephard

I'd love to simply use the default property, but it's not up to date and I'd like the query results to change as the text box's immediate value is changed. Is there a way I can force the text value to 'save' so I can use the default property instead? – Gourmet

@Remou, Also, I'm curious.. When should the Text property be used (if ever) in your opinion? – Gourmet

oops, I did not read that carefully enough! The Change event is the special case! – Fluorescein

Answer 4

The form had a lingering data source. I'm not sure why this would cause to the behavior described above, especially considering the text box controls are unbound, however since removing the data source the text boxes are behaving as expected.

Gourmet

Answer 5

You said "somewhat arbitrarily" I think if everything is fine you must get the error when your form's recordset is empty.

In fact it's a know bug in Access and this error can occur if these conditions are met: a) The control is in the Form Header or Form footer section b) The form is filtered such that no records match (or there are no records) c) No new record can be added.

In this case, the Detail section of the form goes blank. The controlis still visible, but Access gets really confused and can throw the error you describe.

More info: http://allenbrowne.com/bug-06.html

Selfdetermination

Answer 6

I have a table containing names of people starting with "Acc..."

I have a TextBox, named txtFilter, defining the first letters of the name of the people, I want to display on my form.

In the txtFilter_Change event, I use the Text (or Value) property to define a filter in the WHERE clause in my query (where the names start with the filter)

Typing "A" in the TextBox: everything works fine: people with names like "A..." get displayed.

Adding a "C" in the TextBox: everything is working fine, and people with names like "Ac..." are displayed.

Adding a "D" in the TextBox: after the query (no people get displayed), the TextBox is in an intermediate status:

   Text : unable to access (error)
   Value = "ACD"

Removing the "D" from the TextBox: before the query, the TextBox is still in an intermediate status:

   Text : unable to access (error)
   Value = "ACD"
   SelStart = 2

After I removed the "D" character, the cursor in the TextBox was at the location SelStart=2 (after "AB").

When the Text property is in error, I can retrieve the expected filter from the following statement: sFilter = Mid(txtFilter.Value, 1, txtFilter.SelStart)

I hope this can help...

Spillage

Answer 7

I know my answer is out of date. Yet you just can set focus three times. On TextBox in header, on any texbox in detail space and On TextBox in header again. I use access 2003.

Melissiamelita