DLookUp error

[cmraguilar]

I'm having issues with a DLookUp function. Its set up, or so I think, to
pull a company name from a combo box and then display their WSI number. The
combo box also executes 2 quries in seperate subforms On Change and On Got
Focus. When the DLookUp is set as below, I get a #Error. But when I set the
combo box set the "Bound Comlumns" = 2, the text box will display the number
but will I get a Run-time error '3420' Object invalid or no longer set.

Any help would be appreciated.

=DLookUp("[ParentWSINumber]","tblSupplierParent","[SupplierName]='" &
Nz(Forms![Supplier Contact Information]!cboSupplierParentName) & "'")

 

[Jim B]

The bound column from the Lookup box is usually the ID Number from whatever
recordset is used for the combo box. The second column displays the data -
in this case, the SupplierName. In your Dlookup function, you are looking
for a text value of SupplierName, but the boundcolumn of the box is probably
a number. Try using the column property of the combo box.

=DLookUp("[ParentWSINumber]","tblSupplierParent","[SupplierName]='" &
Nz(Forms![Supplier Contact Information]!cboSupplierParentName.Column(2)) &
"'")

This will let you reference the text value, rather that the numeric value.

 

[cmraguilar]

Thanks for your help. I appreaciate the detailed explination rather than
just respoding with the code. It helps me understand the why, rather than
the how. The only change I needed to make was reference it to column(1)
rather than column(2).

The bound column from the Lookup box is usually the ID Number from whatever
recordset is used for the combo box. The second column displays the data -
in this case, the SupplierName. In your Dlookup function, you are looking
for a text value of SupplierName, but the boundcolumn of the box is probably
a number. Try using the column property of the combo box.

=DLookUp("[ParentWSINumber]","tblSupplierParent","[SupplierName]='" &
Nz(Forms![Supplier Contact Information]!cboSupplierParentName.Column(2)) &
"'")

This will let you reference the text value, rather that the numeric value.

I'm having issues with a DLookUp function. Its set up, or so I think, to
pull a company name from a combo box and then display their WSI number. The
combo box also executes 2 quries in seperate subforms On Change and On Got
Focus. When the DLookUp is set as below, I get a #Error. But when I set the
combo box set the "Bound Comlumns" = 2, the text box will display the number
but will I get a Run-time error '3420' Object invalid or no longer set.

Any help would be appreciated.

=DLookUp("[ParentWSINumber]","tblSupplierParent","[SupplierName]='" &
Nz(Forms![Supplier Contact Information]!cboSupplierParentName) & "'")