Input dependent Dlookup expression

[Charlie_CTP]

Hi



What I am trying to do is make the inputted string into a text box called "Stage" be searched for in a table called "FIN_Routes".
The criteria is that the FIN needs to match what is in the "FIN" box. Both the "Stage" and "FIN" box are in the "Inspection_records" form.

The expression that I have come up with is:
=DLookUp("'" & [Forms]![Inspection_records]![Stage] & "'","[FIN_Routes]","FIN='" & [Forms]![Inspection_records]![FIN] & "'")

After trying this and various other versions all I get is #Error in the box.
I am new to access so I don't see where I am going wrong?

Thanks

[pbaldy]

You're selecting which field's value you want looked up? That implies a design problem, so you may want to address that if its not too late. If it is, you wouldn't want the single quotes around the field name. I'll be at a computer where I can test in a bit.

[Charlie_CTP]

Sorry, let me totally reword the question as a scenario.
Scenario

I have a project where I am tracking quantities of items through stages of completion (eg stages 1,2,3 & 4). I need to design a form whereby a user cannot “book” a quantity of stage 2 until at least that quantity of stage 1 have been completed and so on.

Example:

First form entry
Name : John
Stage : 1
Qty Complete : 5

Second form entry
Name : Emma
Stage : 2
Qty Complete : 7

As per the above I need it to show an error on the 2^nd form entry such as “You can only book 5 into stage 2 as only 5 have been completed at stage 1”

Bit of a big ask but can anyone help? Many thanks in advance for any replies.

[ArviLaanemets]

I'm not sure what your DB structure really is, or what you really are attempting to do, but attached is an example of my guess - i.e. how I would start when I'd want to design a DB like this.

BeforeUpdate of source form (fInspectionRows) in subform control prevents saving changes when all conditions are not filled (empty fields, or not enough items in previous stages).