Not to duplicate the data

[Anna]

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.

 

[Guest]

Hi Anna

In the table that holds the Date create a no duplicates index.

Open the table in design view
Select View
Select Indexes
At the base of the index column insert "Date"
Select the date field from the Field Name column drop down
Select "unique" = Yes
Save and close

NOTE - Important. DO NOT do this on your table until you have tested it.
Make a copy of the table then try this on the "copy". If you have some dates
that are not unique you may delete them in error. So do a test first then if
it's OK do this on your table.


Hope this helps