Check for null fields

[caronii]

I am trying to add a section to some VBA code that checks a field in a Style table for null values. If it finds null values I want it to open another form I've made which allows the user to enter a value into the null fields. I'm thinking a while loop would work best, because I want the code to check for missing values again after the form is closed and continue to open the frmNewStyle form until there are no null fields.

I have tried to use the DCount function to count the number of records with a null value in the Category field and if the count is greater than 0, open frmNewStyle, but I haven't been able to get this to work.

This is the code I have used:
Dim NullCount As Integer

NullCount = DCount("[Category]", "Style Ref", "[Category]="" ")
While NullCount > 0
DoCmd.OpenForm "frmNewStyle", acNormal, , , acFormAdd, acWindowNormal
Wend

"Style Ref" is a table with three fields: Cetegory, Style Desc & Untrimmed Style. Style Desc and Untrimmed Style are automatically updated when a new style is found, but Category is user defined.

KP

 

[tonnic]

Hi,

as far as I know, the function "DCount" cant count NULL-Values.

Thats why you don't get a value back.

My suggestion:

Dim NullCount As Integer
Dim TotalCount as integer

TotalCount = DCount("Category", "[Style Ref]","[Style Desc]")
NullCount = DCount("Category", "[Style Ref]", "Category")

And then you compare the 2 values.