DLookUp
in [Form]
|
From: JCA on 26 Jan 2010 09:27 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].
From: Daryl S on 26 Jan 2010 09:49 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'") -- 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 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]. > > >
|
Pages: 1 Prev: Creating matching record in new form Next: Combo box in sub-form |