Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2005
    Posts
    121

    Unanswered: Duplicate Values

    I of course know that, you can set a field in a table to not allow duplicate values.

    Is there a way when a value is entered into an existing record, that it can check the entire table (all records, all fields) for a duplicate and if so be used in the If/Then statement to reject the entry?

    Thanks,
    Bob

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Have a look at Access Help for the DCount() function.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Dec 2005
    Posts
    121
    I use DCount quite a bit, but have not used wildcards. The table has 35 carton number fields. The table has 20 fixed records, each a seperate pallet holding the 35 cartons.

    I tried some variations of DCount looking thru the 35 carton fields (I made the mistake of naming them 1Tote, 2Tote, 3Tote, etc.). So what I needed was for it to look thru every record at the 35 carton (Tote) fields to make sure no one has already scanned that carton to the pallet.

    I tried DCount("[*Tote]","RTV Pallet","[*Tote]=Text0"), but that isn't finding the intentional duplicate I put into the table to test it.

    Am I missing something?

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can always write a function to return if there is a duplicate record.

    ex (in ADO):

    Private Sub ButtonX_Click()
    if isDupRec(me!SomeRecIDField) = true then
    ...
    else
    ...
    end if

    Function isDupRec(RecID as variant) as boolean
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyTableName where myRecordIDField = " & RecID & ""
    rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly
    if rs.eof and rs.bof then
    isDupRec = false
    else
    isDupRec = true
    end if
    rs.close
    set rs = nothing
    End Function
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2005
    Posts
    121
    You state this would find a duplicate record, but what about a duplicate value within multiple fields in 1 record? Or across several records? For example, a table with 35 fields, how can you check a data entry off a form to make sure it hasn't already been put into one of the 35 fields for a "record"?

    My knowledge only tells me how to check a table in just "one" field.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    fundamtentally its down to how you structure your data. SQL is loosley based on English so you ask question s in the SQL dialect. if you can say what your constraints are then you can express them in SQL. you may need to be carefull (or should that be creative) with your choice of bracketing to make certain that the SQL matches the plain English of what you are trying to do.
    so put the values into a where clause
    where acolumn = "blah" AND (column1=anumericvalue OR column2="astringvalue".... Or columnn = somethingorother)
    would only retrieve rows where acolumn = blah and one or more of the subsequent columns [ the specified value)

    or the less effcient way extract all data for that

    if you are looking for one or more occurrence of a symbol within one column then you need to do a wild card match.. have a look at the Access help for wildcards and the like predicate

    but

    where mystring like "?blah*" 'where mycolumn conatins blah
    where mystring like "blah*" 'where mycolumn starts with blah

    failing that you could use instr.. however that be much more inefficient than like
    in fact whilst you are at it I'd strongly recommend that you look up the string function also in help or online
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by Bob.Carter.17 View Post
    You state this would find a duplicate record, but what about a duplicate value within multiple fields in 1 record? Or across several records? For example, a table with 35 fields, how can you check a data entry off a form to make sure it hasn't already been put into one of the 35 fields for a "record"?

    My knowledge only tells me how to check a table in just "one" field.
    In situations like this where you have 35 fields and don't want a duplicate value of all 35 field values, you may want to consider creating a 'concatenated' field which would be a field with all the values concatenated together (you'd need to create the field in the table and then put in some code so when the record is saved, it puts all the field values together and writes to the concatenated field.) Then you could make this a primary key field (so no dups are allowed) or you can check for dups using a function and just the concatenated field to check for dups.

    This can get tricky though, especially when you're dealing with 35 fields (and the 255 text character limitation of the concatenated field) and some fields which may or may not be required. I usually only do this kind of scenario on 2 or 3 key fields, concatenating them into 1 primary key field.
    Last edited by pkstormy; 01-09-10 at 12:14.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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