Results 1 to 6 of 6

Thread: Duplicates

  1. #1
    Join Date
    Aug 2003
    Posts
    31

    Unanswered: Duplicates

    Hi All,

    I have a question, i think its really simple but im not sure how to do it.

    I have a form which i use to update a main table.

    These are the relevant fields

    Name------Account Number-----Code------Date

    What i would like to do is if i try and input a duplicate record then i want a message to come up and tell me that the record is already in the database.

    None of the fields are key fields.

    Another solution if this isnt possible is to write the data to a table then create a query which will search for duplicates and then delete them.

    How do i do this.

    Thanks in advance

    Matt

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: Duplicates

    Originally posted by mattygg
    Hi All,

    I have a question, i think its really simple but im not sure how to do it.

    I have a form which i use to update a main table.

    These are the relevant fields

    Name------Account Number-----Code------Date

    What i would like to do is if i try and input a duplicate record then i want a message to come up and tell me that the record is already in the database.

    None of the fields are key fields.

    Another solution if this isnt possible is to write the data to a table then create a query which will search for duplicates and then delete them.

    How do i do this.

    Thanks in advance

    Matt
    Suppose your AccountNr is the supposed unique Field, then in the field AccountNr after lost focus make something like that in a function:

    SELECT Count(myTable.AccountNr) AS [Found]
    FROM FGD
    HAVING (((Count(AccountNr)) Is Not Null));

    This Select yields a number if it is > 0 then pop up a message error otherwise allow to next field

  3. #3
    Join Date
    Aug 2003
    Posts
    31
    The Account Number isnt a unique field.

    This is because each customer (Account number) can have more than 1 bill a year.

    Name-----Account No----Code----Date

    John------1111------------1--------1/10/03
    John------1111------------2--------20/10/03

    Etc

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    or make such function in the lost focus of the [AccountID]

    Private Sub txt_AccountID_Exit(Cancel As Integer)


    Dim db As Database, rs As Recordset, sql As String

    Set db = CurrentDb
    sql = "SELECT myTable.* FROM myTable WHERE myTable.AccountID= '" & txt_AccountID & "' ORDER BY MyTable.AccountID;"

    Set rs = db.OpenRecordset(sql, dbOpenDynaset)

    If rs.RecordCount > 0 Then
    MsgBox("Already exists ")

    End If

    Set rs = Nothing
    Set db = Nothing

    End Sub



  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Just saw your answer now,
    in the secound solution in the where section you have to use the fields which make your record uniqu i.e AccountNr + Date + CustomerID

    Ok?

  6. #6
    Join Date
    Aug 2003
    Posts
    31
    Hi All,

    If i make all three fields as unique records will it automatically not allow me to input another record with the same details?

    Also with my second question how would i make a delete query(i think i would use this) to delete all the duplicates in a table?

Posting Permissions

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