Solved

Counting non-blank fields

Posted on 2002-07-05
3
542 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 7

Accepted Solution

by:
ildc earned 100 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

737 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