Results 1 to 8 of 8
  1. #1
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48

    How to make the query field unique and not allow to user if there is dublication with new record

    Hi Everyone,



    I want to make my one query field unique. (Lets say the name of the query field "B"). This field (B) is coming from first 5 charachter of another field (lets say, field is "A") which is a member of a Table (lets say for table name "C")

    So the query is calculated = left([A];5). Table field have totaly 9 charachter which is also unique value itself. In a form (Lets say the name of the form is "D") this query field (5 charachter one) is also shown to the users.
    And as I said, when user enters the value in the form to "A" field , if the first 5 charachter exist before which generates my query, then database should give a message that says "there is existing record in query" and also not allow to make this record.

    I could not succeed and appreciated any help.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    You could set an Index on the field in its table and set it as Yes(No Duplicates)
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48
    Quote Originally Posted by Bob Fitz View Post
    You could set an Index on the field in its table and set it as Yes(No Duplicates)
    Sir thanks for answer. But I can make what you want for the fields of table and I set this field as "No dublicates" already. But also my query field should be unique as I already explained in my first message.

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I don't think you can make a calculated field in a query unique. Why would you need to. I would not give users access to any query and certainly not for data entry. That should be done via forms in which you can use data validation code.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if the field is keyed as no duplicates, then no matter what the user does, they cannot enter a duplicate.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You can validate the entry in the Change event of the control on your form where the user is entering the new record:
    Code:
    If dCount("*","[Q]","[B] ='" & me.txtA & "'")>0 Then  ' assumes the characters are alpha, and the query is named Q
        Msgbox "Record already exists!"
    End If
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48
    Thanks Gicu it worked with dcount perfectly.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're welcome!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 4
    Last Post: 06-06-2020, 12:24 AM
  2. Replies: 1
    Last Post: 03-17-2017, 04:19 AM
  3. Can I make a calculated text field unique?
    By alexandervj in forum Access
    Replies: 3
    Last Post: 03-21-2014, 09:17 AM
  4. Replies: 3
    Last Post: 02-12-2014, 12:18 PM
  5. Make command buttons unique to a record
    By timmy in forum Forms
    Replies: 26
    Last Post: 03-09-2011, 09:51 AM

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
  •  
Other Forums: Microsoft Office Forums