Help using Dlookup to find null value

[joecosmides]

I have a form that opens as soon as the Access Database is opened.
It's called MainMenuF. The focus is set on a field and as soon as that
field loses focus it does a Dlookup. If the Dlookup finds that there
are null values in the particular table it's looking in then it opens
another form so that the user can correct the null values and input
the data in those null fields. The ItemType field is the one that
we're searching for null values. Values can only be: New, Used, or
Refurbished. (Or any text data, technically).

Dim x As Variant

If Not IsNull(x) Then
MsgBox "There are items with no New/Used/Refurb status.",
vbOKOnly, "Message."
DoCmd.OpenForm "UpdateInventory2F"
End If

My problem is that after I've updated the null values and there are no
longer any null values, the UpdateInventory2F form still opens up
anyways even though there are no more null values. It opens up and it
blank. It should not be opening if the ItemType field is not null.

Thanks!

 

[Douglas J. Steele]

You should be able to use DCount:

If DCount("*", "[NameOfTable]", "[NameOfField] IS NULL") > 0 Then
MsgBox "There are items with no New/Used/Refurb status.", vbOKOnly,
"Message."
DoCmd.OpenForm "UpdateInventory2F"
End If

 

[joecosmides]

You should be able to use DCount:

If DCount("*", "[NameOfTable]", "[NameOfField] IS NULL") > 0 Then
  MsgBox "There are items with no New/Used/Refurb status.", vbOKOnly,
"Message."
  DoCmd.OpenForm "UpdateInventory2F"
End If

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

I have a form that opens as soon as the Access Database is opened.
It's called MainMenuF. The focus is set on a field and as soon as that
field loses focus it does a Dlookup. If the Dlookup finds that there
are null values in the particular table it's looking in then it opens
another form so that the user can correct the null values and input
the data in those null fields. The ItemType field is the one that
we're searching for null values. Values can only be: New, Used, or
Refurbished. (Or any text data, technically).

  Dim x As Variant

  If Not IsNull(x) Then
    MsgBox "There are items with no New/Used/Refurb status.",
vbOKOnly, "Message."
    DoCmd.OpenForm "UpdateInventory2F"
  End If

My problem is that after I've updated the null values and there are no
longer any null values, the UpdateInventory2F form still opens up
anyways even though there are no more null values. It opens up and it
blank. It should not be opening if the ItemType field is not null.

Thanks!- Hide quoted text -

- Show quoted text -

Excellent! It works perfect.