limit the number of rows of data that can be entered in a memo form

  • Thread starter Thread starter Harold Good
  • Start date Start date
H

Harold Good

Hi,

I have a memo field in a form that I've limited to 300 characters so that
all the text will appear in the designated space on the report. On both the
form and report, the height of the text box will contain 5 rows. (The
report space can't grow because I have other graphs, etc. that have to stay
in position).

However, one can hit the Enter key after every few words and it will quickly
grow beyond 5 rows, and data below 5 rows then won't appear on the report.
(The report space can't grow because I have other graphs, etc. that have to
stay in position).

Is there any way that I can prevent someone entering more than 5 rows of
data on the form? I have removed the scroll bars but that doesn't prevent
them entering additional rows of data.

Thanks,
Harold
 
Harold said:
Hi,

I have a memo field in a form that I've limited to 300 characters so
that all the text will appear in the designated space on the report.
On both the form and report, the height of the text box will contain
5 rows. (The report space can't grow because I have other graphs,
etc. that have to stay in position).

However, one can hit the Enter key after every few words and it will
quickly grow beyond 5 rows, and data below 5 rows then won't appear
on the report. (The report space can't grow because I have other
graphs, etc. that have to stay in position).

Is there any way that I can prevent someone entering more than 5 rows
of data on the form? I have removed the scroll bars but that doesn't
prevent them entering additional rows of data.

Thanks,
Harold
Click to expand...

There is no way I can think of to limit the number of ROWS especially
since Access does not really have rows.

What you might want to do is to make it a text field and you can limit
the total number of characters from 1 to 255.
 
Perhaps the OP could have a look at:

http://www.lebans.com/limitcharsmemo.htm
LimitCharsMemo.zip is a database containing functions to limit the number of
characters And/Or lines for Memo fields.

or

http://www.lebans.com/limittextinput.htm
New Version 2.0 LimitTextInput.zip is a database containing a function to
limit the input into a TextBox control. Will allow data input that will fit
within the current displayable area of the control only. Handles both
Keyboard and Mouse events. Includes a self contained function to mimic the
Report objects TextHeight method.

Includes a Report to demonstrate the use of the core fTextHeight function to
allow you to gain this functionality in the Format event of the Detail
Section. This will allow you to respond to CanGrow events before they happen
by moving/sizing your controls in the section's Format event. Example
demonstrates how to vertically center the contents of a control within a
fixed size box or section.


--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
 
Joseph said:
There is no way I can think of to limit the number of ROWS
Click to expand...

You can test for the presence of 'line feed' characters. If you could
assume that all line feeds are vbCrLf then things would be easy. Of
course, you can't make this assumption and handling all the possible
combinations quickly becomes messy.

As I see it the combinations are CHR$(13) & CHR$(10) and CHR$(10);
however, you cannot rule out CHR$(13) only, and, for completeness,
CHR$(10) & CHR$(13). Because the OP's requirement is to allow up to
five lines, then we have to test a lot of combinations.

A replace function would come in handy e.g. for each step replace a
'line' character (combination) with a known but unusual character e.g.
CHR$(22):

REPLACE$(Address, CHR$(10) & CHR$(13), CHR$(22))

REPLACE$(Address, CHR$(13) & CHR$(10), CHR$(22))

REPLACE$(Address, CHR$(13), CHR$(22))

REPLACE$(Address, CHR$(10), CHR$(22))

Then count the number of CHR$(22) characters

LEN(Address) - LEN(REPLACE$(Address, CHR$(22), ''))

Of course, instead of 'Address' above they should be nested like this
(untested):

LEN(REPLACE$(REPLACE$(REPLACE$(REPLACE$(Address, CHR$(10) & CHR$(13),
CHR$(22)), CHR$(13) & CHR$(10), CHR$(22)), CHR$(13), CHR$(22)),
CHR$(10), CHR$(22))) -
LEN(REPLACE$(REPLACE$(REPLACE$(REPLACE$(REPLACE$(Address, CHR$(10) &
CHR$(13), CHR$(22)), CHR$(13) & CHR$(10), CHR$(22)), CHR$(13),
CHR$(22)), CHR$(10), CHR$(22)), CHR$(22), ''))

See what I mean about messy <g>?

In the absence of a replace function all the combinations of 'line
feed' combinations and flavours of wildcard character could be tested,
with a separate validation rule for each e.g. here's one:

Address NOT LIKE '%' & CHR$(10) & '%' & CHR$(10) & '%' & CHR$(10) & '%'
& CHR$(10) & '%' & CHR$(10) & '%'

Allowing for both ANSI and non-ANSI flavours of wildcard character,
that just leaves 2047 other combinations to write ;-)

More practical would be to build up an auxiliary table of combinations.
First the line feeds:

CREATE TABLE LineFeeds (
line_feed VARCHAR(2) NOT NULL UNIQUE
);
INSERT INTO LineFeeds (line_feed) VALUES (CHR$(10) & CHR$(13));
INSERT INTO LineFeeds (line_feed) VALUES (CHR$(13) & CHR$(10));
INSERT INTO LineFeeds (line_feed) VALUES (CHR$(13));
INSERT INTO LineFeeds (line_feed) VALUES (CHR$(10));

Then create all the combinations (easy with a 'cartesian product') for
each wildcard character:

CREATE TABLE LineFeedPatterns (
line_feed_pattern VARCHAR(143) NOT NULL UNIQUE
)
;
INSERT INTO LineFeedPatterns (line_feed_pattern)
SELECT DT1.line_feed_pattern
FROM
(
SELECT '%' & T1.line_feed & '%'
& T2.line_feed & '%' & T3.line_feed & '%'
& T4.line_feed & '%' & T5.line_feed & '%'
AS line_feed_pattern
FROM LineFeeds AS T1,
LineFeeds AS T2, LineFeeds AS T3,
LineFeeds AS T4, LineFeeds AS T5
UNION ALL
SELECT '*' & T1.line_feed & '*'
& T2.line_feed & '*' & T3.line_feed & '*'
& T4.line_feed & '*' & T5.line_feed & '*'
AS line_feed_pattern
FROM LineFeeds AS T1,
LineFeeds AS T2, LineFeeds AS T3,
LineFeeds AS T4, LineFeeds AS T5
) AS DT1
;

Now the CHECK constraint (validation rule) is simply a matter of using
a LIKE join:

CREATE TABLE Test1 (
memo_col MEMO NOT NULL,
CONSTRAINT memo_col__max_five_lines
CHECK (
0 = (
SELECT COUNT(*)
FROM Test1 AS T1,
LineFeedPatterns AS L1
WHERE T1.memo_col LIKE L1.line_feed_pattern
)
)
)
;

As ever, here's the VBA code to reproduce and test the above SQL:

Sub linefeeds()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
' Create all possible line feeds (4)
.Execute _
"CREATE TABLE LineFeeds (line_feed VARCHAR(2)" & _
" NOT NULL UNIQUE);"
.Execute _
"INSERT INTO LineFeeds (line_feed) VALUES" & _
" (CHR$(10) & CHR$(13));"
.Execute _
"INSERT INTO LineFeeds (line_feed) VALUES" & _
" (CHR$(13) & CHR$(10));"
.Execute _
"INSERT INTO LineFeeds (line_feed) VALUES" & _
" (CHR$(13));"
.Execute _
"INSERT INTO LineFeeds (line_feed) VALUES" & _
" (CHR$(10));"

' Create all possible line feeds and combinations
' for each flavour of wildcard character (2048)
.Execute _
"CREATE TABLE LineFeedPatterns (line_feed_pattern" & _
" VARCHAR(143) NOT NULL UNIQUE);"
.Execute _
"INSERT INTO LineFeedPatterns (line_feed_pattern)" & _
" SELECT DT1.line_feed_pattern FROM ( SELECT" & _
" '%' & T1.line_feed & '%' & T2.line_feed" & _
" & '%' & T3.line_feed & '%' & T4.line_feed" & _
" & '%' & T5.line_feed & '%' AS line_feed_pattern" & _
" FROM LineFeeds AS T1, LineFeeds AS T2," & _
" LineFeeds AS T3, LineFeeds AS T4, LineFeeds" & _
" AS T5 UNION ALL SELECT '*' & T1.line_feed" & _
" & '*' & T2.line_feed & '*' & T3.line_feed" & _
" & '*' & T4.line_feed & '*' & T5.line_feed" & _
" & '*' AS line_feed_pattern FROM LineFeeds" & _
" AS T1, LineFeeds AS T2, LineFeeds AS T3," & _
" LineFeeds AS T4, LineFeeds AS T5 ) AS DT1;"

' Create test table with CHECK constraint
.Execute _
"CREATE TABLE Test1 ( memo_col MEMO NOT NULL," & _
" CONSTRAINT memo_col__max_five_lines CHECK" & _
" ( 0 = ( SELECT COUNT(*) FROM Test1 AS T1," & _
" LineFeedPatterns AS L1 WHERE T1.memo_col" & _
" LIKE L1.line_feed_pattern )));"

' This (five lines) will succeed
.Execute _
"INSERT INTO Test1 (memo_col) VALUES ('legal'" & _
" & CHR$(10) & 'legal' & CHR$(10) & 'legal'" & _
" & CHR$(10) & 'legal' & CHR$(10) & 'legal');"

' This (six lines) will fail
.Execute _
"INSERT INTO Test1 (memo_col) VALUES ('illegal'" & _
" & CHR$(10) & 'illegal' & CHR$(10) & 'illegal'" & _
" & CHR$(10) & 'illegal' & CHR$(10) & 'illegal'" & _
" & CHR$(10) & 'illegal');"

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--
 
Joseph said:
There is no way I can think of to limit the number of ROWS
Click to expand...

You can test for the presence of 'line feed' characters. If you could
assume that all line feeds are vbCrLf then things would be easy. Of
course, you can't make this assumption and handling all the possible
combinations quickly becomes messy.

As I see it the combinations are CHR$(13) & CHR$(10) and CHR$(10);
however, you cannot rule out CHR$(13) only, and, for completeness,
CHR$(10) & CHR$(13). Because the OP's requirement is to allow up to
five lines, then we have to test a lot of combinations.

A replace function would come in handy e.g. for each step replace a
'line' character (combination) with a known but unusual character e.g.
CHR$(22):

REPLACE$(Address, CHR$(10) & CHR$(13), CHR$(22))

REPLACE$(Address, CHR$(13) & CHR$(10), CHR$(22))

REPLACE$(Address, CHR$(13), CHR$(22))

REPLACE$(Address, CHR$(10), CHR$(22))

Then count the number of CHR$(22) characters

LEN(Address) - LEN(REPLACE$(Address, CHR$(22), ''))

Of course, instead of 'Address' above they should be nested like this
(untested):

LEN(REPLACE$(REPLACE$(REPLACE$(REPLACE$(Address, CHR$(10) & CHR$(13),
CHR$(22)), CHR$(13) & CHR$(10), CHR$(22)), CHR$(13), CHR$(22)),
CHR$(10), CHR$(22))) -
LEN(REPLACE$(REPLACE$(REPLACE$(REPLACE$(REPLACE$(Address, CHR$(10) &
CHR$(13), CHR$(22)), CHR$(13) & CHR$(10), CHR$(22)), CHR$(13),
CHR$(22)), CHR$(10), CHR$(22)), CHR$(22), ''))

See what I mean about messy <g>?

In the absence of a replace function all the combinations of 'line
feed' combinations and flavours of wildcard character could be tested,
with a separate validation rule for each e.g. here's one:

Address NOT LIKE '%' & CHR$(10) & '%' & CHR$(10) & '%' & CHR$(10) & '%'
& CHR$(10) & '%' & CHR$(10) & '%'

Allowing for both ANSI and non-ANSI flavours of wildcard character,
that just leaves 2047 other combinations to write ;-)

More practical would be to build up an auxiliary table of combinations.
First the line feeds:

CREATE TABLE LineFeeds (
line_feed VARCHAR(2) NOT NULL UNIQUE
);
INSERT INTO LineFeeds (line_feed) VALUES (CHR$(10) & CHR$(13));
INSERT INTO LineFeeds (line_feed) VALUES (CHR$(13) & CHR$(10));
INSERT INTO LineFeeds (line_feed) VALUES (CHR$(13));
INSERT INTO LineFeeds (line_feed) VALUES (CHR$(10));

Then create all the combinations (easy with a 'cartesian product') for
each wildcard character:

CREATE TABLE LineFeedPatterns (
line_feed_pattern VARCHAR(143) NOT NULL UNIQUE
)
;
INSERT INTO LineFeedPatterns (line_feed_pattern)
SELECT DT1.line_feed_pattern
FROM
(
SELECT '%' & T1.line_feed & '%'
& T2.line_feed & '%' & T3.line_feed & '%'
& T4.line_feed & '%' & T5.line_feed & '%'
AS line_feed_pattern
FROM LineFeeds AS T1,
LineFeeds AS T2, LineFeeds AS T3,
LineFeeds AS T4, LineFeeds AS T5
UNION ALL
SELECT '*' & T1.line_feed & '*'
& T2.line_feed & '*' & T3.line_feed & '*'
& T4.line_feed & '*' & T5.line_feed & '*'
AS line_feed_pattern
FROM LineFeeds AS T1,
LineFeeds AS T2, LineFeeds AS T3,
LineFeeds AS T4, LineFeeds AS T5
) AS DT1
;

Now the CHECK constraint (validation rule) is simply a matter of using
a LIKE join:

CREATE TABLE Test1 (
memo_col MEMO NOT NULL,
CONSTRAINT memo_col__max_five_lines
CHECK (
0 = (
SELECT COUNT(*)
FROM Test1 AS T1,
LineFeedPatterns AS L1
WHERE T1.memo_col LIKE L1.line_feed_pattern
)
)
)
;

As ever, here's the VBA code to reproduce and test the above SQL:

Sub linefeeds()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
' Create all possible line feeds (4)
.Execute _
"CREATE TABLE LineFeeds (line_feed VARCHAR(2)" & _
" NOT NULL UNIQUE);"
.Execute _
"INSERT INTO LineFeeds (line_feed) VALUES" & _
" (CHR$(10) & CHR$(13));"
.Execute _
"INSERT INTO LineFeeds (line_feed) VALUES" & _
" (CHR$(13) & CHR$(10));"
.Execute _
"INSERT INTO LineFeeds (line_feed) VALUES" & _
" (CHR$(13));"
.Execute _
"INSERT INTO LineFeeds (line_feed) VALUES" & _
" (CHR$(10));"

' Create all possible line feeds and combinations
' for each flavour of wildcard character (2048)
.Execute _
"CREATE TABLE LineFeedPatterns (line_feed_pattern" & _
" VARCHAR(143) NOT NULL UNIQUE);"
.Execute _
"INSERT INTO LineFeedPatterns (line_feed_pattern)" & _
" SELECT DT1.line_feed_pattern FROM ( SELECT" & _
" '%' & T1.line_feed & '%' & T2.line_feed" & _
" & '%' & T3.line_feed & '%' & T4.line_feed" & _
" & '%' & T5.line_feed & '%' AS line_feed_pattern" & _
" FROM LineFeeds AS T1, LineFeeds AS T2," & _
" LineFeeds AS T3, LineFeeds AS T4, LineFeeds" & _
" AS T5 UNION ALL SELECT '*' & T1.line_feed" & _
" & '*' & T2.line_feed & '*' & T3.line_feed" & _
" & '*' & T4.line_feed & '*' & T5.line_feed" & _
" & '*' AS line_feed_pattern FROM LineFeeds" & _
" AS T1, LineFeeds AS T2, LineFeeds AS T3," & _
" LineFeeds AS T4, LineFeeds AS T5 ) AS DT1;"

' Create test table with CHECK constraint
.Execute _
"CREATE TABLE Test1 ( memo_col MEMO NOT NULL," & _
" CONSTRAINT memo_col__max_five_lines CHECK" & _
" ( 0 = ( SELECT COUNT(*) FROM Test1 AS T1," & _
" LineFeedPatterns AS L1 WHERE T1.memo_col" & _
" LIKE L1.line_feed_pattern )));"

' This (five lines) will succeed
.Execute _
"INSERT INTO Test1 (memo_col) VALUES ('legal'" & _
" & CHR$(10) & 'legal' & CHR$(10) & 'legal'" & _
" & CHR$(10) & 'legal' & CHR$(10) & 'legal');"

' This (six lines) will fail
.Execute _
"INSERT INTO Test1 (memo_col) VALUES ('illegal'" & _
" & CHR$(10) & 'illegal' & CHR$(10) & 'illegal'" & _
" & CHR$(10) & 'illegal' & CHR$(10) & 'illegal'" & _
" & CHR$(10) & 'illegal');"

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--
 
John said:
I can't see Joseph's original post, but this may provide what's needed:
http://www.lebans.com/limitcharsmemo.htm
Click to expand...

I downloaded the mdb and inserted unhindered a 300 characters then 10
'lines' into the testmemo column. If there's something in there to
limit the number of character/lines then it has no effect at the
database engine level, which is less than ideal.

My approach was to use a CHECK constraint in the database layer to
prevent bad data from getting in from any source.

Here's my code to insert the 'illegal' values. Note there is nothing
'malicious' about this code, I'm simply using the mdb as I would any
other to which I had been granted write permissions:

Sub LimitCharsMemo()
Dim con
Set con = CreateObject("ADODB.Connection")
With con
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\LimitCharsMemo.mdb"
.Open

' Attempt to insert 300 characters
' (succeeds)
.Execute _
"INSERT INTO customer (testmemo)" & _
" VALUES ('12345678901234567890" & _
"123456789012345678901234567890" & _
"123456789012345678901234567890" & _
"123456789012345678901234567890" & _
"123456789012345678901234567890" & _
"123456789012345678901234567890" & _
"123456789012345678901234567890" & _
"123456789012345678901234567890" & _
"123456789012345678901234567890" & _
"123456789012345678901234567890" & _
"1234567890');"

' Attempt to insert 10 lines
' (succeeds)
.Execute _
"INSERT INTO customer (testmemo) VALUES ('1'" & _
" & CHR$(10) & '2' & CHR$(10) & '3' & CHR$(10)" & _
" & '4' & CHR$(10) & '5' & CHR$(10) & '6'" & _
" & CHR$(10) & '7' & CHR$(10) & '8' & CHR$(10)" & _
" & '9' & CHR$(10) & '10');"

.Close
End With
End Sub

Jamie.

--
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

text length in a text box versus memo field 2
Excel Help to copy information from one row at a time to another sheet 1
Can Grow Property in Report 6
Limit number of lines in a memo field 3
Data Entry on a form 3
Limit Number Of Rows In Report 10
Growable Text Box in Report 2
Memo Data Type 5

Back
Top