Problem with text box and automatic space removal

[David Richards]

Hi,

Hopefully someone can help me. I have setup a continuous form that displays
customer names and addresses. I have then place a txt box on the form
header. Using the onChange event I've setup some VBA to update the record
source on the form based on the data entered into the text box. The problem
I have is that when if I type some search data containing a space into the
text box, when the requery is finished access appear to automatically remove
the space off the end of my search string. This means that I can't search
for data such as "H S M Tools" I have attached the VBA code below.

I am pretty new to access (only been working with it for 3 weeks) so may
have missed something simple.

Any help would be great
cheers
Dave

------------------ OnChange Event Code ---------------
Dim strSQL As String

strSQL = "SELECT tblCustomer.intCustomerID, tblCustomer.strName,
tblCustomer.strAccountNo, " & _
"tblCustomer.strBranchNo, tblCustomer.strAddressLine1,
tblCustomer.strAddressLine2," & _
"tblCustomer.strAddressLine3, tblCustomer.strAddressLine4,
tblCustomer.strAddressLine5," & _
"tblCustomer.strPostCode, tblCustomer.strContact,
tblCustomer.strPhoneNo, " & _
"tblCustomer.strMobileNo, tblCustomer.strFaxNo,
tblCustomer.strArea, tblCustomer.strType, " & _
"tblArea.strSalesman FROM tblArea INNER JOIN tblCustomer ON
tblArea.strArea=tblCustomer.strArea "

If strSQL <> "" Then
strSQL = strSQL & "WHERE tblCustomer.strName LIKE '" &
Me.txtAccount.Text & "*'"
End If

Me.RecordSource = strSQL

Me.Refresh
Dim intLength As Integer

If Not IsNull(Me.txtAccount) Then
intLength = Len(Me.txtAccount)

If intLength >= 0 Then
Me.txtAccount.SelStart = Len(Me.txtAccount) + 1
End If
End If

[jimfo...@compumarc.com]

David Richards wrote:
> Hi,
>
> Hopefully someone can help me. I have setup a continuous form that
displays
> customer names and addresses. I have then place a txt box on the form

> header. Using the onChange event I've setup some VBA to update the
record
> source on the form based on the data entered into the text box. The
problem
> I have is that when if I type some search data containing a space
into the
> text box, when the requery is finished access appear to automatically
remove
> the space off the end of my search string. This means that I can't
search
> for data such as "H S M Tools" I have attached the VBA code below.
>
> I am pretty new to access (only been working with it for 3 weeks) so
may
> have missed something simple.
>
> Any help would be great
> cheers
> Dave
>

Access also lops spaces off the end of source code lines and off the
end of a table's field values. Put in a command button that says, "Add
trailing space." Then in the OnClick event put code that looks
something like Text1.Value = Text1.Value & " ". Access won't
automatically remove trailing spaces that have been added through code.
I don't know if there's an internal setting that can change the
automatic space removal.

James A. Fortune

www dot shh dot com -- Dr. Evil

[David Richards]

That did the trick, thanks for your help.