Solved

Unexpected Behavior with Textbox Text Property

Posted on 2011-02-22
50
472 Views
Last Modified: 2013-11-28
I have created an unbound textbox on a form that can be used for freeform searching.  If the user types something in the box, the RowSource for a neighboring listbox is re-written that includes the string in the box.  I've done this many times, but it's not working for some reason.  I am monitoring the textbox's _Change event.  Since the textbox has the focus, the Text property should yield the text in the box (i.e. NOT the Value property).  But Access tells me that the textbox must have the focus in order to reference that property.  Well, that's true, but the textbox DOES have the focus.

I can even test it with this:
If Me.ActiveControl.Name = Me.tbxSearch.Name Then  STR = Trim(Me.tbxSearch.Text)
This ought to work, right?  It fails.

Access thinks that the textbox has the focus, but tells me otherwise.  In fact, not even me.tbxSearch.SetFocus solves the problem.

Any ideas?

Mike


0
Comment
Question by:shacho
  • 24
  • 13
  • 7
  • +2
50 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 34950623
Sounds very odd.
Have tried just deleting the textbox and re-creating it?
0
 

Author Comment

by:shacho
ID: 34950660
Not a bad idea.  But no dice.  Just tried it.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 34950673
If you want to post a sample of the problem I'll take a look here.

Which version of Access are you using?
0
 

Author Comment

by:shacho
ID: 34950676
I discovered that a stale version of the SQL had "stuck" to the listbox's RowSource property.  Much as I hate it, that often happens when you're developing a new form.  Dynamic properties permanently get written to the static object.  I tried deleting that property and now it magically works.  Don't know why but it does.  Any thoughts on why?

Mike
0
 

Author Comment

by:shacho
ID: 34950747
Scratch that.  The REAL problem was something else.
If the form is filtered in a way that it shows no records, the code fails.
If a record is showing, no problem.  So the problem is in the forms filter.  Why?

Mike
0
 

Author Comment

by:shacho
ID: 34950753
And can I work around it?  I need for the form to show nothing when it starts up.
0
 
LVL 84
ID: 34951062
First: I agree with Peter that you might be having some issues with bad/corrupt objects. Have you tried maintenance on the database (i.e. Decompile, Compact, etc)? This might clear up your troubles, but if not:

Can you show more of your code, especially the portion where you think it's failing?

How do you set the Form's Recordsource? Where are you doing that?

0
 

Author Comment

by:shacho
ID: 34951757
>How do you set the Form's Recordsource?
The form is bound directly to a crosstab query.  It's recordset is read only by definition.

>Can you show more of your code
Dim STR as String
STR = Trim(Me.tbxSearch.Text)
That's actually all the code there is.  I haven't gotten past that point yet.

The Form_Load event does this:
Me.Filter = [ID Is Null]
Me.FilterOn = True

This causes the form to display no records because ID is never Null.  When the form is in this mode, that is when it's recordset is filtered to zero records, the Text property does not work even if the control is active.

I'm away from the office so I can't get at the DB, but I suspect if you try this on any DB you'll get the same result.

0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34959787
This may help

STR = Nz(Trim(Me.tbxSearch.Text),"")
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34959798
Lol, nope, sorry, I should test things properly before I throw them out on the table!
0
 
LVL 84
ID: 34959848
< that is when it's recordset is filtered to zero records, the Text property does not work even if the control is active>

If there are no records, then I don't believe you can "focus" a control. You could "focus" a BLANK control (i.e. a control that has no value in a populated record) but I don't think you could focus one where there are NO records.

The suggestion by DockieBoy might help, or you might explicitly check first:

If Not Me.tbxSearch IS NULL Then
  '/run your search now
End If

Or check the RecordCount of your Form's Recordset.

And finally, note that "STR" is also a Function name in Access (it converts a Number to a String). You might consider renaming your variable.
0
 
LVL 10

Expert Comment

by:conagraman
ID: 34960524
it is really odd that you are getting that error using a textbox.
that error is one you would normally get when you use a combo box or a list box.

in your code instead of using "Me.ActiveControl.Name" try putting the actual control you are trying to access. for some reason the activecontrol that has the focus might for whatever reason be a list box or a combo box. and to state the obvious make sure your "textbox" is actually a textbox. if it has an arrow on the right side of it it’s a combo box.




0
 

Author Comment

by:shacho
ID: 34961555
sorry to leave this one on hold so long. i'll be back online with this in about 8 hours.
0
 

Author Comment

by:shacho
ID: 34966515
OK.  This DB demonstrates very succinctly the phenomenon I am describing.

Mike

NoGoOnTextProperty.mdb
0
 

Author Comment

by:shacho
ID: 34966543
I suppose I could embed this form in another and put the textbox on the container form.  That's probably what I'll do.  But I am interested to learn why Access disagrees with itself about whether or not a control has the focus.

You can test this, actually.  Change event handler code from this:

Private Sub tbxTest_Change()
    Me.tbxResult.Value = Me.tbxTest.Text
End Sub

to this:

Private Sub tbxTest_Change()
    MsgBox Me.ActiveControl.Name
    Me.tbxResult.Value = Me.tbxTest.Text
End Sub
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34967024
I think the last post from LSMConsulting is on the money.

If you take the text box tbxTest out of the header and put it in the footer  and set your form to single form instead of continuous and then change between the filter on/off you will see the problem.

Access then will not allow you to set the focus to the text box at all.  If you then remove the line of code that sets the focus to the text box you will notice it dissappear and reappear dependant on the Filter.

When you are returning zero records the textbox effectively is not there to set the focus to.  The only reason you can still see it currently is because it is in the Form header section.

I think you will need to use an unbound form with a subform to achieve what you are after.

Sorry, but I don't have the knowlege to fully explaine why this is happening, perhaps someone else can explain further.  :)
0
 

Author Comment

by:shacho
ID: 34967150
>I think you will need to use an unbound form with a subform to achieve what you are after.
Indeed, that would work (as I suggested).
0
 
LVL 10

Expert Comment

by:conagraman
ID: 34969536
use a variable.

Private Sub tbxTest_Change()
Dim mytext As String
mytext = Me.tbxTest.Text
Me.tbxResult = mytext
End Sub
0
 

Author Comment

by:shacho
ID: 34969624
That's not gonna work.  No chance at all.  You're welcome to test it on the DB I uploaded.
0
 

Author Comment

by:shacho
ID: 34969659
At this point, the discussion is purely academic.  Evidently, you just can't reference an unbound text box's Text property on a bound form when there is no active record.  I don't know why, but I suspect it has to do with how Access manages transient values before committing them to the source table.  It would be nice if somebody could come up with a link to some documentation about how and why this is the case.  So I'll leave this open a little longer.

Cheers,

Mike

0
 
LVL 10

Expert Comment

by:conagraman
ID: 34969672
it does work in the database you uploaded. fo sho
0
 

Author Comment

by:shacho
ID: 34969739
No chance, no way.  I appreciate the effort, but it definitely won't work if the recordset is null.  Click the button on the top right corner of the form that nulls the recordset - then try your code.  It's will cause an error.
0
 
LVL 84
ID: 34969937
Hi Mike,

I'm not exactly sure how to work with your upload. I've tried typing in the first and second textbox, after clicking the two buttons. Nothing happens, but I also get no errors.

Can you provide exact instructions how to recreate the issue?
0
 

Author Comment

by:shacho
ID: 34970115
Click the button labeled "Then Try Me" on the top right, instructions will appear in a message box.  Click OK and just start typing.  Error will appear.
0
 

Author Comment

by:shacho
ID: 34970132
The buttons toggle.  If the message box doesn't appear, click it again.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 10

Expert Comment

by:conagraman
ID: 34970181
the first problem is that you dont understand how to use the filter property.
and also understanding the difference between a textbox and a listbox would help when asking a question.
 here is a working example of your database. i put the filter code in a command button because the other way was just not working. you can try it though.

the code in the button is this

Dim mystring As String
mystring = Nz(Me.tbxTest, "")
Me.tbxResult = mystring
Me.Filter = "GENDER = """ & mystring & """"
Me.FilterOn = True

IWork.mdb
0
 
LVL 10

Expert Comment

by:conagraman
ID: 34970200
if you type in a m or an F  and click the button the records will be filtered to the matches found in the gender field
0
 
LVL 84
ID: 34970225
Still don't see anything going on. Here's what I did:

1) Open the form.
2) Type the letter M into the first textbox. This immediately shows the same value in the box below
3) Click the first button.
4) Click OK to the msgbox. Focus is set to the first textbox
5) Type something. Value shows in the second textbox, but nothing happens.
6) Click the second button.
7) Click OK in the msgbox. Focus is set to the first textbox
8) Type something. Values shows in the second textbox, but nothing happens

So:

What sequence of actions should I take to recreate the issue?

What should I expect to happen?

What happens on your machine?
0
 

Author Comment

by:shacho
ID: 34970305
Very interesting.  What version of Access are you using?
0
 

Author Comment

by:shacho
ID: 34970407
>the first problem is that you dont understand how to use the filter property.
How so?

>and also understanding the difference between a textbox and a listbox would help when asking a question.
Listboxes have nothing to do with this.  I mentioned a listbox in the question, but it's peripheral to this issue.

So, to be perfectly clear, I will pare down the demo model again and make it totally totally totally obvious what this is about.  Please stand by for a few...

Mike
0
 
LVL 84
ID: 34970654
Access 2010, on Windows 7.

I'll also test on 2003/ XP, SP3.
0
 
LVL 10

Expert Comment

by:conagraman
ID: 34970700
<<>the first problem is that you dont understand how to use the filter property.
How so?>>
maybe you do -but you should know that you have to turn it on before you set it or it wont work. this is why your sample database buttons dont work.  

<<Listboxes have nothing to do with this.  I mentioned a listbox in the question, but it's peripheral to this issue.>>
there is a big difference between listboxes and texboxes. knowing which is to be addressed can help in giving an answer that will work and one that wont.



0
 

Author Comment

by:shacho
ID: 34970708
Okay.  Here's the bullet proof demo.  If this doesn't fail for you it's a mystery.  I'm running English Access 2003 on Japanese XP SP3.  Effectively the same setup.

Mike

NoGoOnTextPropertyV2.mdb
0
 

Author Comment

by:shacho
ID: 34970770
>there is a big difference between listboxes and texboxes
Dude.  I know.  This question is not about listboxes.
It is about the Text property of the Access.TextBox class.
That's all.

>you have to turn it on before you set it or it wont work.
Not true.  The order makes no difference.

>this is why your sample database buttons dont work.  
The buttons work fine.  See remark 1.

Mike
0
 

Author Comment

by:shacho
ID: 34970827
It's close to 1AM and I'm wiped.  Back online in 8hrs.
0
 
LVL 10

Expert Comment

by:conagraman
ID: 34970831
>you have to turn it on before you set it or it wont work.
Not true.  The order makes no difference.
??really
can you upload of a working example of that?
0
 

Author Comment

by:shacho
ID: 34970880
i just did
0
 
LVL 10

Expert Comment

by:conagraman
ID: 34971566
first in your tbxIn2_change() code
change me.tbxIn1.text  to me.tbxin2.text

you dont have to but you can remove the ".value" because value is the default property.

now the key.
its giving you an error because your focus is on the filter. turn it off and no error.
so in your code change your btnfilter_click() code to

Me.Filter = "GENDER Is Null"
    Me.FilterOn = True
    Me.FilterOn = False


0
 
LVL 10

Expert Comment

by:conagraman
ID: 34971679
if the purpose of the question was just to get an answer to why there you go.
-but if you need to hide the recordset you will need to find another way -or turn it to false/off before you are trying to reference the field
0
 
LVL 10

Expert Comment

by:conagraman
ID: 34971857
*correction

what i said in my last two posts is not true
the error is telling you it dosent have the focus which is true.
check what control has the focus and if it doesnt change it to the control.

so

  If Me.ActiveControl <> tbxIn1 Then
    Me.tbxIn1.SetFocus
  End If
   Me.tbxOut1 = Me.tbxIn1.Text
0
 
LVL 10

Expert Comment

by:conagraman
ID: 34972442
*correction
i'm going to start over -i am starting to get a better understanding.

ok it seems if you set the filter = is null -or if you set it to something else which results in a 0 record count it will give you that error. when the filter is null it seems it will keep the focus until it is off.  the only way to keep it from throwing an error at that point is to set the filteron = false.  
0
 
LVL 10

Expert Comment

by:conagraman
ID: 34972624
what you can do is add an if then statment checking the recordcount. if it results in 0 then turn the filter off. here is your database with the changes.
NoGoOnTextPropertyV2gg.mdb
0
 

Author Comment

by:shacho
ID: 34974999
conagraman:
I appreciate your contribution to the discussion.  You're up to speed on the phenomenon now, which is good, but the question is not how to work around it; the question is "why does it happen".  Please reread my comment here.

LSM:
Have you tried the updated version?  If it fails on 2003, what happens on 2010?

Mike
0
 
LVL 84
ID: 34975369
I did. With the new "V2", I get errors on 2010 (Win7), 2007 (Win7) and 2003 (Win7 and Win XP).

I have no idea why it's doing this. I'll post in the private Access MVP thread and see if anyone there has an insight.
0
 

Author Comment

by:shacho
ID: 34975861
Much appreciated.

0
 
LVL 10

Assisted Solution

by:conagraman
conagraman earned 200 total points
ID: 34980522
ok i have been looking around and what i can find is:

what is probably happening is when there are no records in the forms recordset access by default overrides the "datamode" changing the properties to not allow edits.
i believe this is the case because if you set the forms "allow edits" property to no and try to access the textbox.text it will produce the same error 2185.
the only place i can find talking about access overriding the "datamode" is in this link.
http://office.microsoft.com/en-us/access-help/allowadditions-property-HA001232715.aspx

so it would seem this behavior although called a “bug” and may appear that way is more along the lines of a built in error handler.
that link also talks about being able to keep access from overriding the "datamode" but i haven’t been able to get that to work.
0
 

Author Comment

by:shacho
ID: 34984820
Mmm, that would make sense.
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34986040
First of all, to shacho, my appologies for posting a suggestion that you had already concluded.  

Second, and this could just be just dribble without a brain mouth filter, but try this and let me know how you go.

Private Sub tbxIn2_Exit()
   Me.tbxOut2.SetFocus
   Me.tbxIn2.SetFocus
   Me.tbxOut2 = Me.tbxIn2
   Me.tbxOut2.SetFocus
End Sub
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 300 total points
ID: 34987546
This seems to be a known issue:

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

Apparently has to do with the methods used to hide the controls during a "no data" sort of event, as you get when you set your Filter to an expression that will return no records. The Detail section is hidden (and therefore focus is removed from all controls), but the Form's Header/Footer are not.
0
 

Author Comment

by:shacho
ID: 34989633
Interesting.  We'll I think we've taken the discussion to it's logical conclusion.  Thanks to all who contributed!

Cheers,

Mike
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now