[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

Unexpected Behavior with Textbox Text Property

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
shacho
Asked:
shacho
  • 24
  • 13
  • 7
  • +2
2 Solutions
 
peter57rCommented:
Sounds very odd.
Have tried just deleting the textbox and re-creating it?
0
 
shachoAuthor Commented:
Not a bad idea.  But no dice.  Just tried it.
0
 
peter57rCommented:
If you want to post a sample of the problem I'll take a look here.

Which version of Access are you using?
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
shachoAuthor Commented:
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
 
shachoAuthor Commented:
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
 
shachoAuthor Commented:
And can I work around it?  I need for the form to show nothing when it starts up.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
shachoAuthor Commented:
>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
 
DockieBoyCommented:
This may help

STR = Nz(Trim(Me.tbxSearch.Text),"")
0
 
DockieBoyCommented:
Lol, nope, sorry, I should test things properly before I throw them out on the table!
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
< 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
 
conagramanCommented:
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
 
shachoAuthor Commented:
sorry to leave this one on hold so long. i'll be back online with this in about 8 hours.
0
 
shachoAuthor Commented:
OK.  This DB demonstrates very succinctly the phenomenon I am describing.

Mike

NoGoOnTextProperty.mdb
0
 
shachoAuthor Commented:
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
 
DockieBoyCommented:
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
 
shachoAuthor Commented:
>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
 
conagramanCommented:
use a variable.

Private Sub tbxTest_Change()
Dim mytext As String
mytext = Me.tbxTest.Text
Me.tbxResult = mytext
End Sub
0
 
shachoAuthor Commented:
That's not gonna work.  No chance at all.  You're welcome to test it on the DB I uploaded.
0
 
shachoAuthor Commented:
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
 
conagramanCommented:
it does work in the database you uploaded. fo sho
0
 
shachoAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
shachoAuthor Commented:
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
 
shachoAuthor Commented:
The buttons toggle.  If the message box doesn't appear, click it again.
0
 
conagramanCommented:
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
 
conagramanCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
shachoAuthor Commented:
Very interesting.  What version of Access are you using?
0
 
shachoAuthor Commented:
>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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access 2010, on Windows 7.

I'll also test on 2003/ XP, SP3.
0
 
conagramanCommented:
<<>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
 
shachoAuthor Commented:
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
 
shachoAuthor Commented:
>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
 
shachoAuthor Commented:
It's close to 1AM and I'm wiped.  Back online in 8hrs.
0
 
conagramanCommented:
>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
 
shachoAuthor Commented:
i just did
0
 
conagramanCommented:
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
 
conagramanCommented:
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
 
conagramanCommented:
*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
 
conagramanCommented:
*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
 
conagramanCommented:
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
 
shachoAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
shachoAuthor Commented:
Much appreciated.

0
 
conagramanCommented:
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
 
shachoAuthor Commented:
Mmm, that would make sense.
0
 
DockieBoyCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
shachoAuthor Commented:
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 24
  • 13
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now