Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2011
    Posts
    413

    Unanswered: One Instance of Value in Table

    I am trying to ONLY allow ONE instance of a value in my table. If for example in tblUserSecurity you have a SecLvl of admn, then it can ONLY be in there once.

    I am confirming this via a combo box on my form using the after update event.
    EmpType is the name of the Combo and also the control name.

    What am I missing here?

    If DCount("SevLvl", "tblUserSecurity", "SecLvl=Forms![frmUsers]!EmpType") > 1 Then

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Why enforce this in a form?
    its not the right way to do this, especially in Access.
    Define an index which is unique.

    ...if you implement this in a form then you have to make certain the same code is picjed up by other forms. If users can enter data using a datasheet view it circumvents your logic code.
    ...other users can write to yhe table unless you are very carefull with the permissions.

    the right way is to define a unique index using whatever columns are neccesary
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Btw the reason you dlookup isnt working is the value from the form isnt being appended after the seclvl=
    ...you need to drop back into vba to add a value from a control
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2011
    Posts
    413
    admn and dev are Yes/No Data Types in tblUserSecurity and the goal is to only allow one instance of that value. So, what do you suggest?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    see post #2:-

    create a unique index on the column(s) as required
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2011
    Posts
    413
    Post # 2 is of no help. I solved the problem by eliminating the choice in the first place.
    Thanks!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    create a unique index on the column(s) as required

    or if that's too obtuse for you
    https://www.google.co.uk/webhp?sourc...unique%20index
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2011
    Posts
    413
    THREAD is [SOLVED]

    I know how to set a unique index, can't be done per this design.

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    FYI, for future reference, for this to be true
    Code:
    If DCount("SevLvl", "tblUserSecurity", "SecLvl=Forms![frmUsers]!EmpType") > 1 Then

    would mean that you already had at least 2 instances of the given Value in the Table! To check to see whether a single instance of the Value already exists

    Code:
    If DCount("SevLvl", "tblUserSecurity", "SecLvl=Forms![frmUsers]!EmpType") > 0 Then

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •