Solved #Error (1 Viewer)

[HillTJ]

Friends, I have the following code behind a control on a datasheet subform. Problem is that it displays #Error Message on a new record. Clearly because neither of the 'where' components have values at that time. I tried the NZ but did not work.

=Nz(DLookUp("[SupplierProdCode] &' '& [SupplierProdDesc]","tblPart_Supplier","PartID=" & [Forms]![frmPurchaseOrders]![frmPO_Line_Item_subformNew]![PartID] & " And SupplierID =" & [Forms]![frmPurchaseOrders]![SupplierID]),"")

Appreciate it if someone can assist.

 

[Gasman]

Use IIF()?
Though you can only retrieve one element of data with dlookup?

 

[CJ_London]

You can return multiple fields in a dlookup providing they are strung together as a single string

the nz function needs to be applied to the form references in the criteria, not the dlookup although you could still use it to display a message like ‘no record’

Ensure you substitute a value such as 0 if a null is incurred

if this control is on the same subform, you do not need the form identifier, partid is all that is required. Similarly you can identify the master form with parent.supplierid. Cuts down on typing and debugging if you change form names

 

[HillTJ]

Gents, thanks. You've highlighted a flaw in my logic as either 'supplierprodcode' or ' supplierproddesc' could be null. Never thought of this before. As usual a seemingly simple piece of code gets more complex. But, better figure it out now!. I'll have a play. If anyone has an idea, i'd appreciate the input. CJ_London, in answer to you're question. The control with this code is on the subform. So, i need to do the NZ on each part of the dlookup, not overall?

 

[CJ_London]

no, you need

=Nz(DLookUp("[SupplierProdCode] &' '& [SupplierProdDesc]","tblPart_Supplier","PartID=" & nz(PartID,0) & " And SupplierID =" & nz(Parent.SupplierID,0)),"Nothing Selected")

or just

=DLookUp("[SupplierProdCode] &' '& [SupplierProdDesc]","tblPart_Supplier","PartID=" & nz(PartID,0) & " And SupplierID =" & nz(Parent.SupplierID,0)

 

[HillTJ]

CJ_London, works fine. Thanks for the support.