?
Solved

Counting non-blank fields

Posted on 2002-07-05
3
Medium Priority
?
547 Views
Last Modified: 2008-03-03
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

Thanks
kiwi
0
Comment
Question by:kiwi246
3 Comments
 
LVL 7

Accepted Solution

by:
ildc earned 400 total points
ID: 7133462
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

ExitFunc:
    Exit Function

ErrFunc:
    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

Regards
0
 
LVL 28

Expert Comment

by:TextReport
ID: 7133476
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

0
 

Author Comment

by:kiwi246
ID: 7136887
Thanks for both replies to this problem. After a bit of up-skilling and fiddling it works beautifully.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question