Help with IF statement

[mkallover]

For some reason I just can't get this to work. On load I have a sub that checks the current project number against a table. Depending on whether or not the project number is on the table one of several different labels is displayed at the bottom of the form.

However, if the user has no active projects, the line with the Dlookup is throwing a "You entered an expression that has no value." VB error back at me.

Code:

If IsNull(Forms!frmMain!frmMainMyProj!CPOProjectNum) Then
Exit Sub
Else

If IsNull(DLookup("CPOProjectNum", "qryMyProj", "CPOProjectNum = '" & Forms!frmMain!frmMainInfo!CPOProjectNum & "'")) Then
Me.Follow.Visible = True
Me.Following.Visible = False
Me.NoFollow.Visible = False
Else
Me.Follow.Visible = False
Me.Following.Visible = True
Me.NoFollow.Visible = True
End If

End If

[pbaldy]

I suspect you're running into this issue:

http://www.mvps.org/access/forms/frm0022.htm

Given what you appear to be doing, you could also try a DCount() instead of DLookup() and test for 0.

[mkallover]

I tried:

Dim CheckCount As Long

CheckCount = DCount("CPOProjectNum", "qryMyProj", "CPOProjectNum = '" & Forms!frmMain!frmMainInfo!CPOProjectNum & "'")

If CheckCount = 0 Then
Exit Sub
Else

If IsNull(DLookup("CPOProjectNum", "qryMyProj", "CPOProjectNum = '" & Forms!frmMain!frmMainInfo!CPOProjectNum & "'")) Then
Me.Follow.Visible = True
Me.Following.Visible = False
Me.NoFollow.Visible = False
Else
Me.Follow.Visible = False
Me.Following.Visible = True
Me.NoFollow.Visible = True
End If

End If

That gives me an error of "You entered an expression that has no value"

[pbaldy]

If the problem is that the subform has no records, I'd use that link, or a technique like it. Or is there a Null value there? Can you post the db?

[mkallover]

I can't post the Db because it contains proprietary info but the problem occurs when the subform has no records. I'll try that link but I'm having trouble grasping how I'd use it.

To use that method would I just do:

If nnz(Forms!frmMain!frmSub!FieldName) = 0 Then
Do.Stuff
Else
Do.OtherStuff
End If

Where Stuff is what I want it to do if there are no records in the subform and OtherStuff is what I want it to do if there are records in the subform?

[pbaldy]

That looks about right; it should return 0 when the subform has no records.

[mkallover]

Awesome, thank you. I'll give that a try ASAP.

Edit: Worked great! Thanks again!

[pbaldy]

Happy to help; glad we got it sorted out.