Error 2185 for Text property in Header when form's recordset is em

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

Re Acces 2003 SP3

I have a simple form based on a parameter query. An unbound textbox &
command button exist in the Form Header section; where the textbox
(txtSearchString) provides the parameter for the form's query.
The user should not be able to enter a space as the first character, as this
would return all records. I have the following code:

Private Sub txtSearchString_KeyPress(KeyAscii As Integer)
On Error GoTo Err_KeyPress

If KeyAscii > 31 Then
If (Len(txtSearchString.Text) = 0) And (KeyAscii = 32) Then KeyAscii = 0
End If

cmdFind.Enabled = Len(txtSearchString.Text)

Exit_KeyPress:
Exit Sub

Err_KeyPress:
If Err.Number = 2185 Then
Resume Next
Else
MsgBox Err.Number & " : " & Err.Description, vbCritical,
"frmSearchPoItemDescription.txtSearchString_KeyPress"
Resume Exit_KeyPress
End If
End Sub

The problem is that the .Text property suddenly gets a value of:-
<You can't reference a property or method for a control unless the control
has the focus.>
....when there are no records returned after pressing the command button,
which Requery's the form.

(I presume I need to check the .Text property for each character change in
the textbox)

Also tried:-
Private Sub cmdFind_Click()
txtSearchString.SetFocus
If txtSearchString.Text = " " Then
MsgBox "Searching for a single space will return ALL records!" & vbCrLf
& vbCrLf & _
"Therfore the search is cancelled", vbCritical, "Space only in
Search box..."
Exit Sub
End If

Me.Detail.Visible = True
Me.RecordSource = "qrySearchItemDescription"
Me.Requery
End Sub

....but get the same error.

Why does access do this for empty form record sets with unbound textboxes?

Many thanks.
 
Use the Before Update event of the control to test for the leading space:

If Left(Me.txtSearchString, 1) = Space(1) Then
MsgBox "Leading Space Not Allowed"
Cancel = True
Me.txtSearchString = Null
End If
 
Hi Klatuu

Many thanks for the reply.

This works until the follwing line is executed:
Me.txtSearchString = Null
Access then raises the the error:
The macro or function set to the BeforeUpdate or ValidationRule property for
this field is preventing Purchasing Database from saving the data in the
field.

Is there any other way to blank the field?
Many thanks.
 
My mistake. You really can't do it there.
If Me.txtSearchString.Undo doesn't work, just leave it like it is.
 
Just tried utilising a 2nd (invisible) textbox and assigniing it the value of
txtSearchString in the command button's click event. This now works as I want
it; but feel this is a "fudge" to get around Access's (many) foibles.

Many thanks again for your replies Klattu!
 
Back
Top