Results 1 to 4 of 4

Input dependent Dlookup expression

  1. #1
    Charlie_CTP is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    2

    Input dependent Dlookup expression

    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

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,725
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Charlie_CTP is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    2
    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 2nd 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.

  4. #4
    Join Date
    Apr 2017
    Posts
    998
    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).
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Maths dependent on input
    By RikApple in forum Access
    Replies: 7
    Last Post: 09-29-2019, 06:32 PM
  2. Replies: 3
    Last Post: 07-09-2018, 05:12 PM
  3. Replies: 4
    Last Post: 02-13-2016, 06:00 PM
  4. Input mask or expression
    By data808 in forum Forms
    Replies: 9
    Last Post: 01-28-2014, 02:41 AM
  5. Input Box Bound to Expression
    By CementCarver in forum Forms
    Replies: 13
    Last Post: 10-12-2013, 04:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums