DLookUp

[JCA]

I have a form (Randomisation) with 3 fields – Cohort, Participant ID, and Box
Number. I want to fill Box Number with a value from a separate table
(Randomisation List). The Box Number to be selected is contingent on the
Cohort (HG or CG) and each Box Number is to be used only once. I’ve tried the
following in the default value and also in control source (which is correct?):

=DLookUp([Randomisation List]![Box Number],[Randomisation
List]![Cohort]=[Cohort (HG/CG)] And [Randomisation List]![Used?]=No)

But I’m just getting #Name?. I’m also conscious that once I do get this to
work, I need to set [Randomisation List]![Used] to Yes for that record, and
I’d also like to copy the Participant ID from the current record in
[Randomisation] to the selected record in [Randomisation List].

 

[Daryl S]

JCA -

Look at the help for DLookUp. I have adjusted this to what I think you need.
Try this:

=DLookUp("[Randomisation List]![Box Number]","[Randomisation List]",
"[Cohort]=" & [Cohort (HG/CG)] & " And [Used?]='No'")