Lookup Criteria returning #Error

[kevnaff]

Hello All

I have a continuous subform called ContractsReadSubform, that is based on a query called QueryContractsSubform.

One of the fields in the query is called CodeNo, which relates to the unique equipment number of a device. Every CodeNo has a serial number which is stored in the ImportEquipment table.

On the subform I want to be able to show the serial number of the CodeNo in an unbound text box. In the ImportEquipment table, the CodeNo field is called Code No with a space.

I have attempted this using the below example:

DLookUp("SerialNumber","ImportEquipment","Code No = " & Me![CodeNo]")

However the criteria that I am entering, always shows #Error in the unbound text box.

Can anybody spot where I am going wrong with this?

Thanks

 

[ComputerVersteher]

Fieldnames with blanks need to be enclosed by square brackets like you did(unnecessarily) on CodeNo and the last double quote is wrong:

DLookUp("SerialNumber","ImportEquipment","[Code No] = " & Me!CodeNo)

 

[kevnaff]

Fieldnames with blanks need to be enclosed by square brackets like you did(unnecessarily) on CodeNo and the last double quote is wrong:

DLookUp("SerialNumber","ImportEquipment","[Code No] = " & Me!CodeNo)

Thanks for your help.

However, the unbound text box is now showing #Name? instead of #Error.

I will look in to why this is.

 

[ComputerVersteher]

Me is only valid in VBA Code. In form design properties you have to reference the form by name from the forms collection:

Forms("FormName")

and avoid the Bang-Operator.

 

[Pat Hartman]

DLookUp("SerialNumber","ImportEquipment","[Code No] = " & Me!CodeNo)

the statement is out of context. if it is actually in the Control Source, it needs to be:
=DLookUp("SerialNumber","ImportEquipment","[Code No] = " & CodeNo)