[AccessD] Practical way to handle non-standard length "long text" fields

[Ryan Wehler]

These are linked tables.  Since Access sees anything over (N)VARCHAR(255)
as "Long Text", that's the problem I'm encountering. So Say I set a column
to VARCHAR(500)... access still treats that as "long text" and a cryptic
error (IIRC) is produced if you type over 500 characters and exit the field
or move to another record.

So in a local table I'd define Columns: FormName (MainForm), TargetField
(Notes), Length (500).  Then I'd use the class to read out that the Notes
field has a max length of 500 and use the keydown event to suss that out.



On Thu, Mar 7, 2024 at 8:48 AM John Colby <jwcolby at gmail.com> wrote:

> I am looking at the table def in Access and it doesn't have a field for max
> length.  How are you setting the max length?  In code or when you define
> the table?
>
> On Thu, Mar 7, 2024 at 9:46 AM Ryan Wehler <wrwehler at gmail.com> wrote:
>
> > Right, that was kind of the idea (I think?)
> >
> > Feed a form into the class, the class scans for textboxes/combo boxes on
> > that form or subform that are contained within a table that defines the
> max
> > length and then applies some logic in/withevents on an event.
> >
> > Since not all textbox lengths are created equal I need a way to define a
> > max length, right?  That seemed like the easiest way unless there's a
> slick
> > way to read the actual table/column DDL from a remote table?
> >
> >
> >
> > On Thu, Mar 7, 2024 at 8:40 AM John Colby <jwcolby at gmail.com> wrote:
> >
> > > I was thinking a generic class which wraps a specific text box which is
> > > passed in to the class.  Then the class reads that length property out.
> > > Use Withevents to sink the keydown and check the length every time it
> > > fires.  Check the length (because the key could be a delete or
> something
> > > which actually shortens the length) and when the length equals the max,
> > so
> > > something such as change the back color or put up a message box.
> > >
> > > You can now use this class for any text box,  Or every text box.
> > >
> > > On Thu, Mar 7, 2024 at 9:31 AM Ryan Wehler <wrwehler at gmail.com> wrote:
> > >
> > > > John,
> > > >  I considered trying to 'class up' the textboxes. My idea would have
> > been
> > > > to have a local table with the form name, field name and maximum
> length
> > > and
> > > > read that in as the class initializes for the textbox/combo box in
> > > question
> > > > and use some events to catch the KeyDown or OnChange.
> > > >
> > > > Right now there's just a couple textboxes that are giving me grief
> so I
> > > > wasn't sure if it was worth writing a class module at this point in
> > time.
> > > >
> > > >
> > > >
> > > > On Thu, Mar 7, 2024 at 8:25 AM John Colby <jwcolby at gmail.com> wrote:
> > > >
> > > > > LOL one of my favorite sayings - "Wish in one hand and spit in the
> > > other.
> > > > > See which one fills up faster."
> > > > >
> > > > > Given which fills up faster, of course I would suggest a class but
> we
> > > all
> > > > > know where that would go.  Something about hammers IIRC.
> > > > >
> > > > > Would you like me to build a class for you?
> > > > >
> > > > > On Thu, Mar 7, 2024 at 8:50 AM Ryan Wehler <wrwehler at gmail.com>
> > wrote:
> > > > >
> > > > > > Thanks Rocky!
> > > > > >
> > > > > > I guess my wish was that Access read in the field length for
> fields
> > > > like
> > > > > > this and applied that more gracefully.
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > On Wed, Mar 6, 2024 at 3:00 PM Rocky Smolin <
> > rockysmolin2 at gmail.com>
> > > > > > wrote:
> > > > > >
> > > > > > > Ryan:
> > > > > > >
> > > > > > > I don't know of a good way to handle this. I have used KeyDown
> to
> > > > good
> > > > > > > advantage when I wanted to inspect every keystroke in a text
> > field.
> > > > But
> > > > > > > it's a little kludgey.
> > > > > > >
> > > > > > > Two ideas: I believe if you use the input mask property to
> allow
> > a
> > > > > > textbox
> > > > > > > to take a maximum number of characters. It will not allow any
> > more
> > > > > > > characters than that input mask allows. So at 500 characters no
> > > more
> > > > > > input
> > > > > > > would be accepted. But that won't generate a message to the
> user.
> > > > And I
> > > > > > > don't know if you can input mask a text box to limit to that
> high
> > > of
> > > > a
> > > > > > > number of characters.
> > > > > > >
> > > > > > > A second thought would be to accept the data into an unbound
> text
> > > box
> > > > > and
> > > > > > > in the Lost Focus event, test for the length of the string that
> > was
> > > > > > input -
> > > > > > > copy it to the bound text box if shorter than your Max length,
> or
> > > > give
> > > > > a
> > > > > > > message to the user that their entered text was too long and
> then
> > > > shift
> > > > > > the
> > > > > > > focus back to the unbound text box.
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > r
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > On Wed, Mar 6, 2024 at 7:01 AM Ryan Wehler <wrwehler at gmail.com
> >
> > > > wrote:
> > > > > > >
> > > > > > > > Is there any practical way to handle non-standard
> VARCHAR(MAX)
> > > > "long
> > > > > > > text"
> > > > > > > > fields in access?
> > > > > > > >
> > > > > > > > If I set one to VARCHAR(500), Access still treats it as Long
> > > > > Text(MAX)
> > > > > > > and
> > > > > > > > the user doesn't really know it's going to be a problem until
> > > they
> > > > > exit
> > > > > > > the
> > > > > > > > field and an error pops up.
> > > > > > > >
> > > > > > > > Ideally what I'd like is for access to just stop typing as it
> > it
> > > > > would
> > > > > > if
> > > > > > > > you hit the char limit for the field.  Is there a way to do
> > that
> > > > > > without
> > > > > > > > using OnChange or KeyDown events to count characters up and
> > warn
> > > > the
> > > > > > > user?
> > > > > > > > --
> > > > > > > > AccessD mailing list
> > > > > > > > AccessD at databaseadvisors.com
> > > > > > > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > > > > > > Website: http://www.databaseadvisors.com
> > > > > > > >
> > > > > > > --
> > > > > > > AccessD mailing list
> > > > > > > AccessD at databaseadvisors.com
> > > > > > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > > > > > Website: http://www.databaseadvisors.com
> > > > > > >
> > > > > > --
> > > > > > AccessD mailing list
> > > > > > AccessD at databaseadvisors.com
> > > > > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > > > > Website: http://www.databaseadvisors.com
> > > > > >
> > > > >
> > > > >
> > > > > --
> > > > > John W. Colby
> > > > > Colby Consulting
> > > > > --
> > > > > AccessD mailing list
> > > > > AccessD at databaseadvisors.com
> > > > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > > > Website: http://www.databaseadvisors.com
> > > > >
> > > > --
> > > > AccessD mailing list
> > > > AccessD at databaseadvisors.com
> > > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > > Website: http://www.databaseadvisors.com
> > > >
> > >
> > >
> > > --
> > > John W. Colby
> > > Colby Consulting
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> John W. Colby
> Colby Consulting
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>