Clear Error from textbox

[rkmaitra]

Hello,
I have a continuous form. On that form is an unbound text box which gets its value from another table based on a value in the current record. I use the Dlookup function for this. Now, if there is no matching value in the look-up table, then the textbox shows #Error (which is fine). I was wondering how I could make the textbox display a blank instead of #Error.
The Dlookup code is:
DLookUp("[Attachment]","[tblAttachments]","[ID]= " & [Forms]![frmHalifax]![Attachement_ID])
Thanks in advance,
Rudra

 

[r.harrison]

Hi,

Try this:


me.TEXTBOX = NZ(DLookUp("[Attachment]","[tblAttachments]","[ID]= " & [Forms]![frmHalifax]![Attachement_ID]),"")

 

[vbaInet]

You can still do:

=Nz(DLookUp("[Attachment]","[tblAttachments]","[ID]= " & [Forms]![frmHalifax]![Attachement_ID]))

... in the Control Source.

However, can you tell us whether this code is in frmHalifax form or not?

 

[rkmaitra]

Thanks for the responses. Unfortunately, I tried putting the code in the control source of the form and it did not make any difference. I tried putting the code in the OnCurrent event of the form and it replaced all the boxes with the attachment for the first record only (this is a continuous, multiple entries form).
vbaInet - yes the code is for the form frmHalifax.
Any other bright ideas?
Thanks in advance,
Rudra

 

[missinglinq]

...I tried putting the code in the control source of the form and it did not make any difference. I tried putting the code in the OnCurrent event of the form and it replaced all the boxes with the attachment for the first record only...

The fact that it worked for one Record shows that the DLookup is working. The fact that it is a Continuous Form means it has to be used in the Control Source to be Record-appropriate, and therein lies your problem.

While

[B]"[ID]= " & [Forms]![frmHalifax]![Attachement_ID][/B]))

is the correct syntax for VBA code, in the Control Source you have to use

[B]"[ID]= " & [Attachement_ID][/B]))

In simpler terms, Me.Attachement_ID works in VBA while [Attachement_ID] works in the Control Source.

BTW, Me.Attachement_ID is a shortcut way of saying [Forms]![frmHalifax]![Attachement_ID] in VBA code.

Linq ;0)>

 

[rkmaitra]

Thanks for the idea Missingling. Unfortunately, it did not work. I think the reason is that if the Attachment_ID field is blank, the lookup function goes to an empty record in the lookup table - new record - and returns a value which is #Error. I don't know how to get rid of this. I have tried to add a relationship between the two tables but that does not work either. Any other ideas will be gratefully received.
Rudra

 

[vbaInet]

Is it showing #Error on all records?

What are the data types of the ID and Attachment_ID fields?

 

[rkmaitra]

Sorry for the delay in replying vbaInet. All the boxes that should be empty are showing #Error. The data type for ID is autonumber and for Attachment_ID is number

 

[Taruz]

Hi..

Try this..:

=iif([Attachement_ID] is null,null,DLookUp("[Attachment]","[tblAttachments]","[ID]= " & [Attachement_ID]))

 

[rkmaitra]

Taruz - Genius. Thanks. Worked like a charm. Brilliant.

 

[vbaInet]

Or:

=DLookUp("[Attachment]","[tblAttachments]","[ID]= " & [COLOR=red]Nz([/COLOR][Attachement_ID][COLOR=red],0)[/COLOR])