How to slow-down a domain function

I have created a data examination routine to list all data fields in a table that contain null values. The code seems to step on itself because the domain function (DCount) is unable to finish as my code loops to the next field. I need a way to stop the code until the domian function finishes.

My table has the caption defined for every field.

Attached is my code.
Public Function DataExamination(strTableName As String)
'***************************************************************
'* This function displays data results in the Immediate Window *
'* as a data examination of any table in the current database. *
'***************************************************************
    
   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Dim fld As DAO.Field
   Dim iTheCount As Long
   
   Set db = CurrentDb()
   Set tdf = db.TableDefs(strTableName)
   
   '*****************
   '* Print headers *
   '*****************
   Debug.Print "No Value Count", "FIELD NAME"
   Debug.Print "==============", "=========="
 
   For Each fld In tdf.Fields
        iTheCount = 0
        iTheCount = DCount("*", strTableName, "IsNull(fld.Name)")
    
    '*********************************************
    '* Print data elements that have null values *
    '*********************************************
        If iTheCount > 0 Then
            Debug.Print iTheCount, fld.Properties("Caption")
        End If
        
    '********
    '* Loop *
    '********
   Next
   Debug.Print "==============", "=========="
 
   Set db = Nothing
   Exit Function
 
End Function

Open in new window

LVL 10
LennyGrayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
why not use recordset? or a simple select query.
++++++++

For Each fld In tdf.Fields
        iTheCount = 0
        iTheCount = DCount("*", strTableName, "IsNull(fld.Name)")
       
        doevents   '<<< try adding this
    '*********************************************
    '* Print data elements that have null values *
    '*********************************************
        If iTheCount > 0 Then
            Debug.Print iTheCount, fld.Properties("Caption")
        End If
       
    '********
    '* Loop *
    '********
   Next
0
digital_thoughtsCommented:
Have you tried using "DoEvents" ?
0
GRayLCommented:
How did you define a caption for every field?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Eric ShermanAccountant/DeveloperCommented:
Change the following line in your code as shown below ....  

iTheCount = DCount("*", strTableName, "IsNull(" & fld.NAME & ")")


I works when I tested it and you should not need the Do Events.

ET
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LennyGrayAuthor Commented:
GrayL -

In the design of a table, the field name has properties of its own, such as field size, format, input mask, caption, etc.

You can use caption to give a good description of a table field name. For example, I migth define the field name for "Last Name" as strNameLast. The Caption would be assigned the value, Last Name.


To the others: This database has about 40 tables and some of the tables have up to 30 elements (not of my design). It is easier to examine all of the tables at once and flag all of the null fields.

Lenny
0
LennyGrayAuthor Commented:
I will try all of your suggestions tomorrow morning and award the points tomorow.

Thanks for the quick responses and your assistance.

Best Regards,

Lenny Gray
0
LennyGrayAuthor Commented:
Thanks for the assist !!!!
0
GRayLCommented:
Lenny:  Don't know where my head was at when I posted to this question.  Of course you can set the caption for each field.  You just have to remember VBA will throw an error if you reference a field caption which has not been set.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.