Solved

Counting non-blank fields

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

895 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now