Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2016
    Posts
    3

    Post Unanswered: How I stop a user from entering duplicate data into same village but allow to other

    hi all, this is my first post
    and i need help, in validation rule : i need to prevent duplicate on entering the same name of farmer in the same village, and allow to entering that name to other village like this example:

    this can be allowd to entering:
    farmer name -------------village code
    john ----------------------------- 1235489
    john ----------------------------- 1452876

    this example not allowd and must prevent deplicates becouse the duplicates in the same village
    farmer name ------------------ village code
    john --------------------- 1235489
    john -------------------- 1235489

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Don't think of this as a validftion rule, think of its as a constraint on the table(s)
    An index can be defines as unique (only on instance of that rows key columns in that table)
    A primary key is a unique value that identifies a specific row in the table. Essentially a primary key is the same as a unique key EXCEPT there can only ever be one prim,ary key, whereas you cna have one or more unique keys

    so its down to your table design
    I would expect:-
    a table for farmers, with a unique key (probably an ID / autonumber column, but could just as easily be a code or an abbreviation of the farmers name eg NAPOLN_666)
    a table for farms, with a unique key (probably an ID / autonumber column, but could just as easily be a code or an abbreviation of the farms name eg MANORFM_123)
    a table for villages, with a unique key (probably an ID / autonumber column, but could just as easily be a code or an abbreviation of the village name)

    if a farmer can only ever have one farm, then the village ID is stored in the farmer row

    strictly speaking Id expect farmer as a separate entity to farm (this allows for a farmer to have one or more farms)

    I suspect your assignment requires a many to many relationship, where one or more farmers can have farms in one or more villages, and villages may have one or more farms. have a look at intersection / junction tables

    to model a many to many relationship normally you woudl use an intersection table which would identify what famrs are in what villages
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You can use DCount() in the AfterUpdate events of both Village Code and Farmer Name, but the syntax varies by Datatype. Is Village Code defined as a Number or as Text?

    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

  4. #4
    Join Date
    Jan 2016
    Posts
    3

    Yeah, my p.k is the village code and farmer code

    Quote Originally Posted by healdem View Post
    Don't think of this as a validftion rule, think of its as a constraint on the table(s)
    An index can be defines as unique (only on instance of that rows key columns in that table)
    A primary key is a unique value that identifies a specific row in the table. Essentially a primary key is the same as a unique key EXCEPT there can only ever be one prim,ary key, whereas you cna have one or more unique keys

    so its down to your table design
    I would expect:-
    a table for farmers, with a unique key (probably an ID / autonumber column, but could just as easily be a code or an abbreviation of the farmers name eg NAPOLN_666)
    a table for farms, with a unique key (probably an ID / autonumber column, but could just as easily be a code or an abbreviation of the farms name eg MANORFM_123)
    a table for villages, with a unique key (probably an ID / autonumber column, but could just as easily be a code or an abbreviation of the village name)

    if a farmer can only ever have one farm, then the village ID is stored in the farmer row

    strictly speaking Id expect farmer as a separate entity to farm (this allows for a farmer to have one or more farms)

    I suspect your assignment requires a many to many relationship, where one or more farmers can have farms in one or more villages, and villages may have one or more farms. have a look at intersection / junction tables

    to model a many to many relationship normally you woudl use an intersection table which would identify what famrs are in what villages
    All this records on the same table so know you give me an idea which i take farmer recirds on other table in order to make it many to many relationships

  5. #5
    Join Date
    Jan 2016
    Posts
    3

    I was trying the dcount but it prevent all duplicates on the whole table not on the v

    Quote Originally Posted by Missinglinq View Post
    You can use DCount() in the AfterUpdate events of both Village Code and Farmer Name, but the syntax varies by Datatype. Is Village Code defined as a Number or as Text?

    Linq ;0)>
    Do you know how to make it check duplicates on village level

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    With your current design you could deckare a unique index of farmer and village code... that means you cannot have duplicates. But it does mean you may need to change your form to trap the error gracefully
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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