By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,131 Members | 1,425 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,131 IT Pros & Developers. It's quick & easy.

Problem with text box and automatic space removal

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
That did the trick, thanks for your help.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.