Access Right Trimming Text Boxes 1

[PWise]

I have A search as you type form that that works fine until I want to search form a name that has a space in it.

What happens is that after I reset the forms recordset with the new critria Access trims the space off the end of the text box. So I have no way to type in a word with a space.

 

[Remou]

Can you check for a space before resetting the recordset and add it on afterwards?

 

[MajP]

Not sure how you are doing this, but you might need to capture the text property of the control on before update instead of the value property.

 

[MajP]

To clarify here is a demo:

Private Sub txtOne_BeforeUpdate(Cancel As Integer)
Debug.Print txtOne.Text & "Text"
Debug.Print txtOne & "Value"
End Sub

output:
Test Text
TestValue

Where "Test" is in txtOne

 

[PWise]

Sorry Remou
I am runing this code from the on change event and tacking it on causes the on change event to run again and trim the space. It loops about 65 time and then stops..

I have noticed that if I have a form with two text boxes and i type a word with a space at the end and go to the other text box access will trim the first text box

 

[PWise]

This is code that i am using

The on change of all vxxxxxxx is

=UpdateVControl()

Function UpdateVControl()
Me("v" & Me.ActiveControl.Name) = Me.ActiveControl.Text
RequreySubform
Me.ActiveControl.SelStart = Len(Me.ActiveControl.Text)
End Function

Function RequreySubform()
Dim Rst As ADODB.Recordset
Set Rst = ExecuteAdoRS("CheckForProviderName", 4, 0, Me.vVStaffLastName + "%", Me.vVStaffFirstName + "%", Me.vVStaffAddress + "%", _
                                                        Me.vVStaffCity + "%", Me.vVStaffState + "%", Me.vVStaffZip + "%", Me.vVStaffPhone1 + "%", Me.vVStaffPhone2 + "%")
[COLOR=red]Till here the space is not trimed[/color]
Set Me.sFrmProviderFormsEntry.Form.Recordset = Rst
[COLOR=red] here the space is  trimed[/color]
Set Me.Providers.Recordset = Rst.NextRecordset
Me.ActiveControl.Text = Me.ActiveControl.Text & sp

End Function

 

[MajP]

So capture the text in a variable on the event.

Private Sub txtOne_Change()
Dim strText As String
strText = txtOne.Text
Debug.Print strText & "Not trimmed"
End Sub

output with spaces:
tNot trimmed
teNot trimmed
tesNot trimmed
testNot trimmed
test Not trimmed
test Not trimmed
test Not trimmed
test Not trimmed
test Not trimmed

 

[PWise]

Majp:

I dont want to do it on before update because I want it to search after each letter

 

[PWise]

Sorry
Majp we crossed in posting

The problem is that it trims when I run this line

Set Me.sFrmProviderFormsEntry.Form.Recordset = Rst

 

[Remou]

I have not tested this properly, but you may find a space-like character suits:

Private Sub txtText_KeyPress(KeyAscii As Integer)
    If KeyAscii = 32 Then KeyAscii = 160
End Sub

160 is not trimmed by Access and you can use replace in your query to set Chr(160) to Chr(32).

 

[PWise]

Remou:
Thank you very much parlimnary test show good results.