Restrict Text Box to 5 Lines

B

Bob Vance

I need to restrict my Client/Address text box to 5 lines only
Is there a way I can do this, as it causes a problem when they enter a 6th
Line
 
C

Clifford Bass

Hi Bob,

Try this: Go into the design view of your table. Right-click and
choose Properties Add this to the table's Validation Rule property:

Len([Client/Address])-Len(Replace([Client/Address],Chr$(13) & Chr$(10),""))<=8

And something like this to the Validation Text:

Please enter no more than five lines in the Client/Address field.

Alternatively, you might set these values in the text box's Validation
Rule and Validation Text properties. Or you might use an After Update event
on the text box to do the checking. My preference would be to put it in the
table so that it gets enforced regardless of how the data gets into the table
(imports, forms, direct entry, SQL statements in code, etc.).

By the by, if your field name really does contain a slash, I suggest
you change it so as to avoid potential problems that might arise should you
or someone else forget to enclose the name in brackets; which would cause
Access to try to divide Client by Address.

Hope that helps,

Clifford Bass
 
B

Bob Vance

Actually worked out its not the lines but it seems to only allow me 90
letters before it errors , does 90 sound a familiar number for anything?
Regards Bob
 
B

Bob Vance

And sorry this is what is yellowing out just part of my code
.Fields("OwnerAddress") = recOwnersInfo.Fields("OwnerAddress")
Thanks Bob
 
B

Bob Vance

Also this Microsoft Warning
"The field is to small to accept the amountof data attempted to add,try
inserting and pasting less data"
Thanks Bob
 
B

Bob Quintal

I need to restrict my Client/Address text box to 5 lines only
Is there a way I can do this, as it causes a problem when they
enter a 6th Line
Click to expand...
you should have 6 or 7 one line textboxes, not a single 5 line box.

e.g. ClientName, Street, SuiteNo, City, StateOrProvince, postCode,
Country
 
C

Clifford Bass

Hi Bob,

I would suspect that you need to increase the size of the OwnerAddress
field on the left side of that assignment. Or truncate the data coming from
the right side. As you are only giving tiny snippets of code and no
descriptions of the actual data, it is hard to say for sure.

Clifford Bass
 
C

Clifford Bass

Hi Bob,

I would agree with you for the most part. Except for the street and
suite number. Only one line for street is too restrictive. Perhaps you were
saying to limit it to one line? Separating out the suite number can cause
problems when trying to reattach it. How do you know to use Apt or Ste(?) or
Room or any number of other suite-type abbreviations? And how do you know
exactly where to put it. Normally it might come after, but it could be
needed elsewhere. I like to use a single large field for the "street"
address part that allows for any number of lines of unspecified length so
that people can enter all kinds of "street" addresses in free form as fits
their situation. The only limit being the size of the field. It does cause
issues when trying to transfer to other systems that use Address1, Address2,
etc. But those are solvable. On the plus side it also simplifies adding an
address block into reports, letters, merge data and so forth.

My $0.02 worth,

Clifford Bass
 
B

Bob Vance

Thanks Guys , Found the problem, When Distributing to tblInvoice the
OwnerAddress field was set to text 100 so changed it to 255........Regards
Bob
 
B

Bob Quintal

=?Utf-8?B?Q2xpZmZvcmQgQmFzcw==?=
Hi Bob,

I would agree with you for the most part. Except for the
street and
suite number. Only one line for street is too restrictive.
Click to expand...

Both street and suiteno are text fields, so I can enter
123 Sesame Street
Suite 567

or
666 Satan Drive
Apt 22

Po Box 12345
Station Z

as is the need. My point was mostly that lumping all the info into a
single field violates database normalization rules. and creates
problems like the OP's how do I limit it to 5 lines, as well as the
inability to sort on PostCode, or City..
 
Top