databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access Form Events

Counting Used Characters in a Microsoft Access Text Field

You should be aware that, when in Microsoft Access, that a text field will hold a maximum of 255 characters. If you require more characters then you should be using the memo type data field.

There may be the time where you want to restrict your users to using only a certain amount of characters when using a text field. You can set the field size when designing the table and limit the amount of characters allowed, however it would be nice to inform the user of how many characters they have used when they are entering data.

The following example shows how to display the amount of characters used whilst the user is entering the data:

To demonstrate this, you will need to apply the code in two events of the form - The On Current Event of the Form and the On Change Event of the Textbox that you are verifying the data entry of.

The form below shows the data being validated on entry:

Image showing the character count being displayed upon data entry
Image showing the character count being displayed upon data entry

The code used to produce this example is:

Private Sub Form_Current()
'Used in the On Current event of the form
'to display character count when scrolling
'through each record
    Me.txtBookReview.SetFocus
    If Not IsNull(Me.txtBookReview.Text) Then
            Me.txtCharsUsed = Len(Me.txtBookReview.Text) & _
            " characters used in this review."
        Else
            Me.txtBookReview = 0
    End If
End Sub

Using this in the On Current event of the form ensures that when the user is scrolling through the records the character count for each record displays the current count for that record.

The following code is also used in the On Change event of the textbox (txtBookReview), to show the character count as the textbox is being added/updated:

Private Sub txtBookReview_Change()
'Used in the On Change event of the form
'to display character count when the record
'is being added/amended
    Me.txtBookReview.SetFocus
    If Not IsNull(Me.txtBookReview.Text) Then
            Me.txtCharsUsed = Len(Me.txtBookReview.Text) & _
            " characters used in this review."
        Else
            Me.txtBookReview = 0
    End If
End Sub

If the textbox contains no data, a zero (0) will be displayed.

Why not check out the following for much more information on using VBA in Microsoft Access: