How to remove #Error display value of your control

[daryll]

I have a continuous form with field "Attachment" which will count all attachment relevant to the ID of the form. I was able to count the attachment with this

=Nz(DCount("EntryDocID","tblPoolAttachmentDetail","EntryDocID = " & [tbID].[Value]),0)

but at the end of the row where you will see the (New) a value of #Error in the "Attachment" column is displayed. How to remove this?

 

[The_Doc_Man]

I believe the problem is that in the form, that last (New record) row has no [tbID] value and therefore you have null following your "EntryDocID = " clause, which results in a syntax error in the DCount.

Your enclosing Nz() function essentially does nothing useful. DCount does not return nulls. It can easily handle the case of getting a count of zero even if there are null records to be counted. (They just would not be counted.) I might instead use Nz to "massage" the criteria value.

=DCount( "EntryDocID", "tblPoolAttachmentDetail", "EntryDocID = " & Nz( tbID.Value, "0" ) )

If 0 is a valid and possible ID, then pick a validly formed but totally unused ID for the criteria clause.

By the way, since no spaces are involved and you are presumably running this inside the form, you don't need the square brackets []. You never WOULD need them for returning the .Value property anyway. And in this case, it is possible that you don't even need to specify the .Value property.

Unless that [tbID] is a combo box or list box and there is some ambiguity in what you are looking up, the .Value property is the default selection of properties from a control that has a value. Note that special cases apply for combo boxes and list boxes, but those cases do not apply to text boxes.

 

[daryll]

By the way, since no spaces are involved and you are presumably running this inside the form, you don't need the square brackets [].

I thought this was a good practice to enclose a [] on field name so you may able to identify it easily between a variable and a field name.

Anyhow, thanks @The_Doc_Man. Your lecture gives me an additional insight.

 

[Mark_]

Alternative would be to validate that you have an ID to check first, then deal with the results as needed...

= IIF( IsNull(tbID),"",DCount( "EntryDocID", "tblPoolAttachmentDetail", "EntryDocID = " & tbID )

In this instance you check if tbID is infact NULL before you use DCount so you would not need to warp tbID in an NZ(). This would also allow you to put in a description, such as "Please enter a new record" if that is warranted.

 

[arnelgp]

No, you will never get the exact count
of attachments on a particular EntryDocID like
that.

build a Total Query (eg. qryAttachmentCount)

SELECT tblPoolAttachmentDetail.EntryDocID, Count(tblPoolAttachmentDetail.AttachmentField.FileName)AS Attachment
FROM tblPoolAttachmentDetail
GROUP BY tblPoolAttachmentDetail.EntryDocID;

then DLookup this Query for the total attachments:

=DLookup("Attachment","qqryAttachmentCount", "EntryDocID=" Nz([tbID].Value, 0))

 

[The_Doc_Man]

Arnel, I am missing something in your comment. I was addressing the "#Error" message, and you appeared to adopt the solution I offered.

But why do you say that the DCount done that way wouldn't work? I've "DCounted" recordsets that way even when they contained nulls and have not had issues. What did you see that I did not?

 

[The_Doc_Man]

I thought this was a good practice to enclose a [] on field name so you may able to identify it easily

If you intend to do this as a mnemonic for identifying a particular control or field, I guess you could do that. It is all a matter of being consistent in how you do something if the goal is mnemonic value. But syntax-wise, not required.

 

[arnelgp]

I am counting the real attachment field. How many they have. One EntryDocId can have multiple rexords and on each rexords can have multiple attachment file.

 

[The_Doc_Man]

Ah, I see. Arnel, you are working with information I didn't have because I didn't see that much about the structure underlying that query. You must have helped Daryll with another post that I missed.