How can I determine the number of characters allowed in a text field?

I would like to display the number of characters typed so far and the max number of chars allowed in a text field. I created a function to accomplish  this and I can use this function for any text field. The logic is working well, but the max number of characters is set by a simple VBA assignment before calling the function (("intMaxChars = 30".) I prefer to retrieve this number from the field's definition in the database. How can I accomplish this?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
you need to refer to the fields size, something like:

intMaxChar = me.recordsetclone.fields("YourTextField").size
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
To get a field's Size, you can try like:


Here's an example to limit the number of chars to be entered in a textfield in a form.

Unbound text box: limiting entry length

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David Johnson, CD, MVPOwnerCommented:
Try this
intMaxChars = CurrentDb.TableDefs("TableName").Fields("FieldName").Size
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Gustav BrockCIOCommented:
You can come a bit closer, as the textbox could have a name different from the field in the recordset:

    intMaxChar = Me.RecordsetClone.Fields(Me!txtYourTextBox.ControlSource).Size

Here's a function that grabs the size of the field whenever you go to another control that also calls it:
Public Function CharLimitReached() As Boolean
    Dim iCharCount As Integer
    Dim isNewControl As Boolean
    Static ctl As Control
    Static frm As Form
    Static iMaxSize As Integer
    'check if on a different form
    If frm Is Nothing Then Set frm = Screen.ActiveForm
    If Screen.ActiveForm.Name <> frm.Name Then
        Set frm = Screen.ActiveForm
        isNewControl = True
    End If
    'check if on a different control
    If ctl Is Nothing Then Set ctl = Screen.ActiveControl
    If Screen.ActiveControl.Name <> ctl.Name Then
        Set ctl = Screen.ActiveControl
        isNewControl = True
    End If
    'get size of field if on a new control
    If isNewControl Then
        iMaxSize = frm.RecordsetClone.Fields(ctl.ControlSource).Size
    End If
    'check if maximum size of field has been reached
    iCharCount = Len(ctl.Text)
    If iCharCount >= iMaxSize Then
        CharLimitReached = True
    End If

End Function

Open in new window

So in the OnChange event of your textboxes you would add something like this:
If CharLimitReached() Then ...

SowleManRetiredAuthor Commented:
Gustav's solution worked immediately. It was exactly what I wanted and needed at that moment.
IrogSinta and Ryan Chong provided good stuff for me to study because I saw new things in their examples.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.