Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: duplicate question....

    HI,

    I have a form where a user fill data and hits a button and it stores it in a table..

    The problem that I am having is duplicate records.. Is there a way that when the user hits the button add record.. that it checks the fields that are unique
    and if they match then pop a msgbox say that you have enter a duplication of records.. then the user hits ok to return to the original form to re-enter the new
    record.

    Here is the code behind the button..

    Private Sub Command9_Click()

    Dim RS As Recordset
    Set RS = CurrentDb.OpenRecordset("tblQR", dbOpenTable)
    With RS
    .AddNew
    !strProject = strProject.Value
    !strArea = strArea.Value
    !strReference = strReference.Value
    !Site = Site.Value
    '!System = System.Value
    !EPO = EPO.Value
    !ControlNo = ControlNo.Value
    !strDate = strDate.Value
    .Update
    .Close

    End With
    MsgBox "Record: " & ControlNo & " has been Added!", vbInformation, "New Project"

    GetID
    Me.Refresh
    Me.strProject = Null
    Me.strArea = Null
    Me.strReference = Null
    Me.EPO = Null
    Me.strDate = Null

    End Sub

    The fields that need to be checked for duplicates are.. strProject, strArea, and strReference

    thanks for the help..

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    What I would do is to base your recordset on a query and then if there are no results then add the record. For example:

    strSQL = "SELECT * FROM tblQR WHERE [strProject]='" & strProject & "' AND [strArea]='" & strArea & "' AND [strReference]='" & strReference & "'"

    Then open the recordset like this:

    Set RS = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

    Then check to see if there are any records, if there are then it has already been entered and you just pop up your message, otherwise enter the new record.

    If RS.RecordCount=0 Then
    'Add the record

    Else

    MsgBox "Record already added."

    End If

  3. #3
    Join Date
    Jul 2003
    Posts
    292
    I think I goofed somewhere... Here's and update of the code..

    strSQL = "SELECT * FROM tblQR WHERE [strProject]='" & strProject & "' AND [strArea]='" & strArea & "' AND [strReference]='" & strReference & "'"
    Set RS = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    If RS.RecordCount = 0 Then

    Dim RS As Recordset
    Set RS = CurrentDb.OpenRecordset("tblQR", dbOpenTable)
    With RS
    .AddNew
    !strProject = strProject.Value
    !strArea = strArea.Value
    !strReference = strReference.Value
    !Site = Site.Value
    '!System = System.Value
    !EPO = EPO.Value
    !ControlNo = ControlNo.Value
    !strDate = strDate.Value
    .Update
    .Close

    End With
    MsgBox "Record: " & ControlNo & " has been Added!", vbInformation, "New Project"

    GetID
    Me.Refresh
    Me.strProject = Null
    Me.strArea = Null
    Me.strReference = Null
    'Me.Site = Null
    'Me.System = Null
    Me.EPO = Null
    Me.strDate = Null
    Else

    MsgBox "Record already added."

    End If


    Errors says...

    strSQL not defined..

    thanks for your help..

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You'll need to declare the variable. Add:

    Dim strSQL as String

  5. #5
    Join Date
    Jul 2003
    Posts
    292
    It Works !

    I do have a question..

    I had to take the recordset I had of another table off for this to work..

    I still need the orginal recordset on there.. it was a table with my counter..

    Is there a way I can work around this ?

    thanks

  6. #6
    Join Date
    Jul 2003
    Posts
    292
    I got it.. !!

    thanks..

    I just added the fields that I needed into the query.. thanks!

    I do have a quick question.. I'm still in the learning process.. and I just want to understand the code..

    How does this code:

    strSQL = "SELECT * FROM tblQR WHERE [strProject]='" & strProject & "' AND [strArea]='" & strArea & "' AND [strReference]='" & strReference & "'"

    Then open the recordset like this:

    Set RS = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

    Then check to see if there are any records, if there are then it has already been entered and you just pop up your message, otherwise enter the new record.

    If RS.RecordCount=0 Then
    'Add the record

    Else

    MsgBox "Record already added."

    End If
    Know to use the query that I just created?

    thanks!

Posting Permissions

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