Results 1 to 8 of 8
  1. #1
    PSCC is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    4

    Question Best way to limit data entry based on previous data entry?

    Hello,
    I'm trying to figure out the best/most robust/most efficient/fastest way of limiting data entry in cells based on an entered value in another previous cell;
    For example, I have a table (and associated form) for which one of the compulsory items to enter is one of ten possible 5-letter text 'species code'.
    Later in the table there are 22 other cells for number entry (measurements) which all have maximum and minimum possible values depending on which 'species code' was selected. Eg, limb length must be greater than 21 but less than 32 for species 'XXXXX'.
    I am looking for suggestions for the best way of achieving this - I have seen a few possible suggestions on various forums, and have some ideas of my own, but am open to suggestions, as I'm only an intermediate user.
    I greatly appreciate any replies. Thank you.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    This would require VBA code behind a form.

    For a start, do you have a table of species and their attributes? This table could have two fields: MinLen, MaxLen.

    Then code could look at these two fields and validate the user input. Could even have textboxes on form that display this info to users.

    Why would there be limits on measurements? What happens when you find that 'monster catfish' that far exceeds norms?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Just for funsies

    PSCC.zip

    Sample using unbound forms, sorry I was bored this afternoon

  4. #4
    PSCC is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    4
    Eeeek, I was trying to avoid VBA, its not my strongest skill.

    Yes, I have that table already.

    I spoke with the person who I'm creating it for, and just to complicate things, they only need the restrictions on 5 out of the 10 possible species, and only if they are adults (another field already there).

    I am assured there are no monsters!

  5. #5
    PSCC is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    4
    I'll take a look at that db - thank you! Very glad you were bored this afternoon

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    even if you don't want to use vba the table structure should be close to what you want.

    The code attached to the database I gave you makes it relatively easy and painless to add data items to any of the tables and have those fields also be on the data entry form.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Join tables so the MinLen and MaxLen fields are available for reference. Then expression in ValidationRule property can be like:

    >=[MinLen] And <=[MaxLen] Or Is Null

    Then message in ValidationText:

    Input outside allowable range. Enter another value.

    No VBA.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    PSCC is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    4
    Thanks both, I will give it a go!

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

Similar Threads

  1. Replies: 3
    Last Post: 02-25-2015, 04:48 PM
  2. Replies: 5
    Last Post: 03-09-2014, 07:16 PM
  3. Data Entry based on dates
    By islewis in forum Forms
    Replies: 3
    Last Post: 03-07-2014, 02:58 PM
  4. Limit data entry based on another field
    By chemengr in forum Forms
    Replies: 5
    Last Post: 01-02-2014, 01:21 PM
  5. Replies: 0
    Last Post: 02-17-2013, 11:17 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
  •  
Other Forums: Microsoft Office Forums