Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2013
    Posts
    22

    Unanswered: Allow limited duplicate values in access table field

    Hello sir,

    I want to limit the values like numbers or text in one record field of ms access table. say i want to allow value <1234> entered only up to 10 times in the record field, if entered 10 times more it will indicate that the value entered in all records of particular filed is more than required nos ie 10.

    Pl help me to find the solution.

    Thanx and regards..

    Putur

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    A field can only have 1 value.

  3. #3
    Join Date
    Oct 2013
    Posts
    22
    Thanks for reply..
    but there may be some way like some code in VBA or through query or form... i want to restrict/limit the "n" number of same value entered in field.
    Pl help me to find the solution..
    Thanks & regards..
    Putur

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    I would place some code in the ON Key down event looking for a spacebar to be press

    them you have to SPLIT() the text by the spaces

    Theword = Split(me.thetextbox," ",-1,vbTextCompare)

    now the Theword is a array

    Theword(0) = 1st word
    Theword(1) = 2rd word
    Theword(2) = 3rd word
    ....
    ....
    ....
    so on

    which would put all words into a array

    Now the Hard Bit founding the dups in the array ill ask google

    this should start you down the right track

    https://bytes.com/topic/visual-basic...e-values-array

    use the above code with some changes

    when the words are the same you need to flag some

    I would use and other array

    dup(i)=dup(i)+1

    when finish finding the dups

    for a=0 to ubound(dup)
    if dup(a)>1 then
    do the dup thing
    end IF
    next
    Last edited by myle; 10-11-16 at 16:46. Reason: spelling ,gramma
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Oct 2013
    Posts
    22
    Quote Originally Posted by myle View Post
    I would place some code in the ON Key down event looking for a spacebar to be press

    them you have to SPLIT() the text by the spaces

    Theword = Split(me.thetextbox," ",-1,vbTextCompare)

    now the Theword is a array

    Theword(0) = 1st word
    Theword(1) = 2rd word
    Theword(2) = 3rd word
    ....
    ....
    ....
    so on

    which would put all words into a array

    Now the Hard Bit founding the dups in the array ill ask google

    this should start you down the right track

    https://bytes.com/topic/visual-basic...e-values-array

    use the above code with some changes

    when the words are the same you need to flag some

    I would use and other array

    dup(i)=dup(i)+1

    when finish finding the dups

    for a=0 to ubound(dup)
    if dup(a)>1 then
    do the dup thing
    end IF
    next
    Thanks lot dear for your help with some code, but it looks difficult for me to figure out how and what to do as i am new to ms.access, i will try this and will post the result..

    Let me explain about my requirement. I have table namely "order" with 7 fields as follows.

    itemcode description size qty Report_no Report_date Remarks
    1 abcd 100 10 123 1/2/16
    2 efg 50 5 123 1/2/16
    3 hij 100 8 123 1/2/16
    4 klm 100 10 124 2/2/16 .... cont

    In the report column one should not enter report no 123 more than 3times (or as required "n" times), if entered some warning message to be displayed.

    Pl help me how this can be done.. waiting for valuable tips..

    Thanks & regards

    Putur

  6. #6
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    An ugly solution is to test the field using a combination of dcount with like as the criteria (eg. if dcount( "id", "aTable" , " afield LIKE & chr(34) & "*" & 123 & "*" & chr(34) ) > 10 then....)

    J. Smith
    Aylmer, Quebec

Posting Permissions

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