• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

Counting non-blank fields

On a form I have 20 fields where entries can be made and I want to have another field which displays how many of those 20 fields contain information.  How do I do a count of the non-blank fields?

Am using Office 2000 version of Access

1 Solution
Hi Kiwi,

Goto the code window of your form (that is the square like button with three colors and a little sparkle on the top left) and paste the code underneath
Private Function UpdateField() As Byte
On Error GoTo ErrFunc:

Dim ctl As Access.Control
Dim bytResult As Byte

bytResult = 0

    For Each ctl In Me.Controls
     If ctl.ControlType = acTextBox And ctl.Name <> "TextResult" Then
        If Not IsNull(ctl) Then
            bytResult = bytResult + 1
        End If
     End If
    Next ctl
    UpdateField = bytResult

    Exit Function

    MsgBox "Could not count"
    Resume ExitFunc
End Function

Then in the afterupdate of each textbox you put the following line :
    Me.Textresult = UpdateField()
(to find the afterupdate event : right click on the textbox in form design, take 'properties', goto the event tab, double click in white space next to event name and click on triple points)

The only assumption I took here was that the textbox in which you want to store the result is called "Textresult".
You can change that in the code to match the name you used

Instead of returning the value from the function why not set the control in the function and you then just need to specify the AfterUpdate Event as =UpdateField() rather than having to put an event procedure in each of them.

The code would need the line me.TextResult = bytResult in place of the line UpdateField = bytResult. You could also return UpdateField = True to say the function has worked but it is not neccessary.

Cheers, Andrew

kiwi246Author Commented:
Thanks for both replies to this problem. After a bit of up-skilling and fiddling it works beautifully.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now