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