Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by Harold Good, Jul 28, 2006.

  1. Harold Good

    Harold Good
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    India Govt Mulls Proposal to Limit Number of Guests at Weddings Breaking News Feb 22, 2011
    Opinion The Limitations of Being ‘Spiritual but Not Religious’ Breaking News Mar 23, 2013
    What actually are the 3 worlds? And is Maya limited only to the physical plane? Questions and Answers Nov 30, 2012
    Legal Cash Limit Breaking News Apr 4, 2012
    The Limits of Free Will Interfaith Dialogues Feb 7, 2011

  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Harold Good wrote:
    > 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


    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.

    --
    Joseph Meehan

    Dia duit
     
  4. Stephen Lebans

    Stephen Lebans
    Expand Collapse
    Guest

    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 Meehan" <sligojoe_Spamno@hotmail.com> wrote in message
    news:VeJkg.71433$YI5.63070@tornado.ohiordc.rr.com...
    > Harold Good wrote:
    >> 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

    >
    > 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.
    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
     
  5. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Joseph Meehan wrote:
    > > 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.
    > >
    > > Is there any way that I can prevent someone entering more than 5 rows
    > > of data on the form?

    >
    > There is no way I can think of to limit the number of ROWS


    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.

    --
     
  6. John Nurick

    John Nurick
    Expand Collapse
    Guest

    Hi Jamie and Joseph,

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

    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1150790894.218739.301540@u72g2000cwu.googlegroups.com...
    >
    > Joseph Meehan wrote:
    >> > 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.
    >> >
    >> > Is there any way that I can prevent someone entering more than 5 rows
    >> > of data on the form?

    >>
    >> There is no way I can think of to limit the number of ROWS

    >
    > 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.
    >
    > --
    >
     
  7. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    John Nurick wrote:
    > I can't see Joseph's original post, but this may provide what's needed:
    > http://www.lebans.com/limitcharsmemo.htm


    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.

    --
     

Share This Page