Textbox Focus error

[KevinC]

I'm working on an existing Access 2000 database which the client will not
upgrade to 2002.

I'm having serious problems with a form I want the user to search a table
with.
I have a continuous form with an unbound combo box with the field names I
want to search on and an unbound text box for the search text in the form
header.
I have the following code in the textbox change event.

Private Sub txtSearch_Change()
Me.RecordSource = strQry & cboField & " LIKE '" & txtSearch.Text & "*'
ORDER BY " & cboField
Me.Requery
If Me.RecordsetClone.RecordCount <> 1 Then
Me.txtSearch.SetFocus
Me.txtSearch.SelStart = Len(Me.txtSearch.Text)
End If
End Sub

on the line - Me.RecordSource = strQry & cboField & " LIKE '" &
txtSearch.Text & "*' ORDER BY " & cboField
the txtSearch.Text is causing the following error

Run Time Error 2185
You can't reference a property or method for a control unless the control
has the focus.

Putting txtSearch.SetFocus before the line does not cure it.
Using just txtSearch rather than txtSearch.Text works but causes an the same
error on the line - Me.txtSearch.SelStart = Len(Me.txtSearch.Text)
And only searches on the preceding keypress ie:- entering 'wa' will only
result in - LIKE w* - in the SQL string. Then Pressing backspace and
deleting the 'a' and it will result in - LIKE wa* - in the SQL string.

Is this an undocumented feature in Access 2000 i usually work in VB.

 

[Guest]

The .text property is only available while the control has the focus. Try
using either the .value property or just the name of the control (
Me.txtSearch.Value or Me.txtSearch). Check VBA Help for detailed info on the
..text property. There are some subtlties in when the vaules in the
properties change you may need to be aware of to ensure you are getting the
correct value.

I also notice that txtSearch in the first line of your sub is unqualified.
You need the Me. there.

 

[KevinC]

If you read my original post you will see that I have tried the value but
this does not return the correct updated value the user has typed. Surely
the control must have the focus since the code is triggered by the controls
change event.

I have tried passing the .Text value to a variable immeadiately on entering
the subroutine with the same results. I have qualified the value with Me.
I have even tried a completely new form. All with the same results.

 

[Guest]

I did read your post and I am trying to help. That is why I suggested what I
did. One other thing you may try is to move your code from the Change event
to the After Update event. By then, the value should have changed.

 

[KevinC]

I have actually found a work around.
Immeadiately on entering the _Change subroutine set the focus to another
control this updates the value of the textbox control you can then use the
..Value property

It does however still appear to be a bug in Access.

 

[Guest]

It is not an Access bug. You are just doing it in the wrong place. If you
are too stuborn to listen to advice, then why even post?
Work Around = I don't know what I'm doing, but I found a kluggy way to fix it.

 

[John Nurick]

PMFJI, Kevin, but are you sure it's a good idea to requery the form
after every keystroke? How about something like this (air code) instead:

Private Sub txtSearch_Change()
Dim strWhere As String
Dim lngRecordsFound As Long

strWhere = Me.cboField.Value & "LIKE '" & Me.txtSearch.Text & "*'"

lngRecordsFound = DCount("*", "MyTable", strWhere)
Me.lblRecordsFound.Caption = lngRecordsFound & " matching records"
If lngRecordsFound <> 1 Then
Me.RecordSource = strQry & " " & strWHERE & _
& " ORDER BY " & Me.cboField.Value & ";"
Me.Requery
Me.OtherControl.SetFocus
End If
End Sub