Not to duplicate data

  • Thread starter Thread starter mustish1 via AccessMonster.com
  • Start date Start date
M

mustish1 via AccessMonster.com

Hi:Is it possible if by mistake i entered the same date range again of same
Appt which is already exist in table tblAppointments. It will not create new
record in that table. As right now It create new records. Duplicating the
existing records.

Private Sub Form_Close()
Dim rs As DAO.Recordset, rs2 As DAO.Recordset, db As DAO.Database, dDates
Set db = CurrentDb

Set rs = db.OpenRecordset("TestDate")
Set rs2 = db.OpenRecordset("tblAppointments")

If rs.EOF Or rs.BOF Then
MsgBox "No records"
Exit Sub
End If
rs.MoveFirst
Do Until rs.EOF
For dDates = rs("ApptDate") To rs("EndDate")
With rs2
.AddNew
!Appt = rs("Appt")
!ApptDate = dDates
!EndDate = dDates
![ApptNotes] = rs("[ApptNotes]")
![Reason] = rs("[Reason]")
![NumberofHours] = rs("[NumberofHours]")
.Update
End With
Next
rs.MoveNext
Loop
End Sub

Thanks.
 
Before add a new entry you can check if the entry already exists.
rs2.Index = "NameOfIndex"
rs2.Seek "=", ValueYouSeek

If rs2.NoMatch=True Then
rs2.AddNew
......................
Else (If you want to change part of the data)
rs2.Edit
.....................
End If
 
Create an index in the table that does not allow duplicates from the
combination of the fields that you consider unique. Then you will need to
trap for the error in your code.
 
Back
Top