IsNull not catching all nulls

[lmcc007]

I have a field in Event table called EventAttachment. EventAttachment is an Attachment datatype. I want the code to see if it is null and if so put No Attachment in the caption. Sometimes it works and sometimes it doesn’t. For instance, Record 455 has no attachments but it displaying Attachment.

Below is the code:

   If IsNull(DLookup("EventAttachment", "Event", "CompanyID = " & Me!txtCompanyID)) Then
        Me!cmdAttachments.Caption = "No Attachment"
        Me!cmdAttachments.ForeColor = vbRed
    Else
        Me!cmdAttachments.Caption = "Attachment"
        Me!cmdAttachments.ForeColor = -2147483615
    End If

Does anyone see anything wrong with the code?

 

[SOS]

I wouldn't use IsNull like that. Use DCount instead:

If DCount("EventAttachment", "Event", "CompanyID = " & Me!txtCompanyID) = 0 Then

 

[vbaInet]

I think this is a similar thing we did with the paths. Don't use a DLookup, do it in the query and return 0 for no attachment and 1 for attachment(s).

Check against that field and for 0s change the colour.

 

[lmcc007]

I wouldn't use IsNull like that. Use DCount instead:

If DCount("EventAttachment", "Event", "CompanyID = " & Me!txtCompanyID) = 0 Then

Yep, DCount did it.

I am using IsNull for EventID and it seems to be work. Below is what I got:

If IsNull(DLookup("EventID", "Event", "CompanyID = " & Me!txtCompanyID)) Then

Is that okay or should I change this also?

 

[SOS]

Yep, DCount is a bit more efficient. Also, looking for NULL from a DLookup isn't really the best thing to do ever.

 

[lmcc007]

I think this is a similar thing we did with the paths. Don't use a DLookup, do it in the query and return 0 for no attachment and 1 for attachment(s).

Check against that field and for 0s change the colour.

These are different--command buttons on another form that was created from a table. They don't want to have to click the button to see if there are any attachments.

 

[vbaInet]

These are different--command buttons on another form that was created from a table. They don't want to have to click the button to see if there are any attachments.

I see, so obviously it's not the continuous form?

 

[lmcc007]

Yep, DCount is a bit more efficient. Also, looking for NULL from a DLookup isn't really the best thing to do ever.

Okay, I thought DLookup was an easy way to look into another table to see if something is there or empty.

 

[lmcc007]

I see, so obviously it's not the continuous form?

No, it's not--just a single form with some command buttons.

 

[vbaInet]

No, it's not--just a single form with some command buttons.

--Oki Doki--