DLookup problems

[Alistair J]

Hi

I have this DLookup as the control source on a text box which is on subform 'clientworkerlink' under form 'Client Details':

=DLookUp("[jobtitle]","[Worker Details]","workerID=Forms![Client Details]![clientworkerlink].Form!workerID")

This works fine and displays the jobtitle

I copied the 'Client Details' Form to 'Client Details2' and also the subform 'clientworkerlink' to 'clientworkerlink2'. I changed the subform on 'Client Details2' from 'clientworkerlink' to 'clientworkerlink2' and changed the DLookup to this:

=DLookUp("jobtitle","[Worker Details]","workerID=Forms![Client Details2]![clientworkerlink2].form!workerID")

However, this doesn't work and displays #Error.

I can't see why. Can anyone enlighten me? What have I got wrong?

 

[DCrake]

Don't see how the first one works? You need to split the criteria and the condition, such as:

x = DLookup("[Field Name]","Domain","[NumericFieldName]=" & condition )

If the value you are looking up is a text field then you need to encapsulate it with quotation marks

x = DLookup("[Field Name]","Domain","[TextFieldName]= '" & condition "'" )

If dates use #

x = DLookup("[Field Name]","Domain","[DateFieldName]= #" & condition "#" )

 

[Alistair J]

Thanks for the prompt reply.

OK, now I have:

=DLookUp("[jobtitle]","[Worker Details]","[workerID]=" & Forms![Client Details2]!clientworkerlink2.Form!workerID)

but I get #Name?

(workerID is a numeric field)

 

[Alistair J]

Ok, got it. The problem was that I had changed 'clientworkerlink' to 'clientworkerlink2' in the Source Object property of the subform control, instead of completely deleting the subform and re-inserting the subform onto the form. Once I did that, the DLookups worked. Even the ones that shouldn't! I'll change them all anyway, just to be safe.

Thanks.