Solved

Counting non-blank fields

Posted on 2002-07-05
3
540 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 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

713 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