Selecting value contingent on other field

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 expression builder:

=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]. How do I do this.

Thanks for any help you can give.

JCA -

Look up the DLookup command in help - it needs three parameters, and some
need double-quotes. You don't have the table-name or query-name in your
statement.

--
Daryl S


"JCA" wrote:

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 expression builder:

=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]. How do I do this.

Thanks for any help you can give.