Hi

I have an Access 97 database used for entering product information that
has several drop down lists and one large free flow text area for the
description to which I have prevented new line feed when someone uses
the Enter key, but the user is still able to cut and paste text with a
carriage return in, which tends to mess up the database as the database
is exported as a delimited text file with each line being a separate
item.

I have just spoken to Microsoft and they say that it is possible to
remove carriage returns (or at least change them into another character
or space) by doing the following:

On the AfterUpdate command, call a bit of VB code to poplulate an array
which is equal to the length of the string in the textbox, then loop the
array, and if the array contains any Chr(13) or &Ch10 make this equal to
a space or another character.

One problem, I am not an expert at VB coding and have become slightly
stuck! I understand what the method is and what it is supposed to so as
I do a little Perl and JavaScript programming, but VB is a bit new to
me, can anyone show how this should look in code form?

Any help is greatly appreciated!

James

Hi,

Try using this function.
Function ReplaceStr(TextIn, SearchStr, Replacement, CompMode As Integer)
Dim WorkText As String, Pointer As Integer
  If IsNull(TextIn) Then
    ReplaceStr = Null
  Else
    WorkText = TextIn
    Pointer = InStr(1, WorkText, SearchStr, CompMode)
    Do While Pointer > 0
      WorkText = Left(WorkText, Pointer - 1) & Replacement & Mid(WorkText,
Pointer + Len(SearchStr))
      Pointer = InStr(Pointer + Len(Replacement), WorkText, SearchStr,
CompMode)
    Loop
    ReplaceStr = WorkText
  End If
End Function

So from teh Afterupadate of your text box, you can put the following code in
the event procedure (this will replace the return with a space)

me!SomeControl = replaceStr(me!SomeControl, vbCrLf, " ",0)

HTH

--
Just my $0.001
Dev Ashish
---------------

:

Hi Dev,

Thank you so much for this Dev, it is absoloutly perfect!! Thanks!

Best wishes

James