1

I have an access table of vendors, used by many queries and vba. The end users populate the fields (in a form) based on W9 data in Excel, which often contains line breaks (aka character turns, line feeds). If they paste a piece of data which contains line break, then it breaks many other processes. I need to find a way to prevent them pasting data with line breaks, and, prevents them from manually entering a line break into the field. I have tried various vba options to find and remove but this can still result in bad data.

Example: they paste in a vendor number that has a line break from excel such as:

1234

1246

The various find/replace functions can convert this to 1234.1246, or take the first set before the line break: 1234, or the second set after the line break: 1246. But there is no way for the database to know which 1234 or 1246 is correct.

I need a way to 1) prevent the data from being input and 2) to notify the end user that the data they attempted to input was not accepted.

Please help, Sincerely,
Pulling My Hair Out in Chunks

Grrly
  • 43
  • 1
  • 1
  • 7
  • How about creating a Before Update event on the text box(es) involved? Your event would be written in VBA using Chr(10) to check upon carriage returns and Chr(13) to check upon linefeeds. – Rene Mar 24 '20 at 19:58

1 Answers1

1

I figured it out with the help of a coworker!!

Validation Rule:

Not Like "*[ insert line break here

]*"

This won't even let them save if they force close the database!!

Grrly
  • 43
  • 1
  • 1
  • 7