Not to duplicate data

[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.

 

[Svetlana]

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

 

[Guest]

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.