CODEWITHSUNDEEP
vbams-access
InvisibleKid56
InvisibleKid56

Reputation: 53

Prohibit and strip out line breaks in MS Access form

I have an MS Access form which contains text fields into which users enter text as well as copy/paste text. I want to make sure that all text stored has no line breaks as I will process these text fields with ArcGIS, which cuts everything after a line break in text fields. Ideally, I want users to see what they get, that is, they see while they enter text how it will be stored.

I have tried this solution: Disabling multi-line fields in MS Access I gather that I will need a keystroke event for the text box that turns line breaks into spaces (or ideally semicolons with a space after) for text entered as well as another event that strips out all of the line breaks out of pasted text.

However, using this event from the above thread by BIBD, had some unexpected side effects.

Private Sub SingleLineTextBox_ KeyPress(ByRef KeyAscii As Integer)
If KeyAscii = 10 _
    or KeyAscii = 13 Then
        '10 -> Ctrl-Enter. AKA ^J or ctrl-j
        '13 -> Enter.      AKA ^M or ctrl-m
    KeyAscii = 0  'clear the the KeyPress
End If 
End Sub

If I accept the changes to the field before this text field by pressing enter, the focus jumps to the text field and the event fires, deleting all previously entered text and replacing it with a space (or semicolon with a space, in my case). However, I have to preserve the previously entered text.

Also, I could not figure out how to get the second event to strip out all line breaks to work.

  If InStr(Me!MyMemoControl, vbCrLf) Then
 Me!MyMemoControl = Replace(Me!MyMemoControl, vbCrLf, vbNullString)
End If

I'm sorry if the answer is obvious, but unfortunately, my VBA skills are minimal. If you see a more convenient way to solve this problem than using these two events, I'm all ears. I appreciate any help you might have.

Upvotes: 3

Views: 880

Answers (1)

Andre
Andre

Reputation: 27644

As David W. Fenton wrote in the linked question, it is preferable to do the replacement in the After Update event of the control.

If your users manage to enter different kinds of line breaks (e.g. by pasting from Excel which uses vbLf while Access uses vbCrLf), handle them separately. E.g.

Private Sub MyMemoControl_AfterUpdate

    Dim S as String
    S = Nz(Me!MyMemoControl.Value, "")

    If S <> "" Then
        S = Replace(S, vbCrLf, "; ")
        S = Replace(S, vbLf, "; ")
        S = Replace(S, vbCr, "; ")    ' this one is probably not needed

        Me!MyMemoControl.Value = S
    End If

End Sub

Upvotes: 2

Related Questions