How do I get the updated value of a textbox in VBA Access in a custom Sub?

Question

This is for VBA Access 2003

I've got a textbox I want to use as a filter for a list box rowsource command. I also have a checkbox which adds another filter for the same rowsource command. I've only programmed in C# and I'm trying to write a single Sub which will simply set the RowSource regardless of if my textbox filter is changed or if my checkbox filter is changed. However, my textbox is giving me problems.

If my checkbox filter changes and I run my method the textbox.Text throws an error saying that it must have focus - Text is null. If I do a null check on that property it throws an error saying the control must have focus.

I've used the .Value property, but for whatever reason it doesn't update to the newer values.

My current attempt:

   If Me.txtClientFilter.Text = Null Then  ' Error 2185
      filter = Me.txtClientFilter.Value
    Else
      filter = Me.txtClientFilter.Text
    End If

Should I

Manually add focus then remove it everytime I want to check a control?
Duplicate my code in each control's event Sub?
Manually set the .Value property when the change happens?

Basically you dont need the control to have focus to read it. There must be some other problem. — A.S.H
what i'm getting is: Runtime error 2185: You can't reference a property or method for a control unless the control has the focus. — Adam Heeg
probably this might help. It might be specific to Access, since the OP there was also using access. Never had such an issue with Excel :/ stackoverflow.com/questions/20703479/run-time-error-2185 — A.S.H

Adam Heeg Adam Heeg · Accepted Answer · 2015-10-12T13:51:06

I fixed my problem with some code which I'll show below. I don't know what was happening behind the scenes, but the .Value was not getting updated with the .Text value. I decided to set it explicitly, which then caused the entire text box value to be selected.

I ended up with the following code which explicitly sets the .Value of the control and also reset the cursor to the end of the text in the control. Thanks to some guy named Brent Spalding here for the cursor code.

Private Sub txtClientFilter_Change()

  Me.txtClientFilter.Value = Me.txtClientFilter.Text
  ProcessFilter
  txtClientFilter.SelStart = Len(Me.txtClientFilter.Text)
  txtClientFilter.SelLength = 0

End Sub

How do I get the updated value of a textbox in VBA Access in a custom Sub?

1 Answers