Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Not to duplicate the data

    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.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You don't have a unique identifier set to not allow duplicate records on. You need to have a field which is marked as a primary key to uniquely define each record. You can make a unique identifier on a combination of more than 1 field. Once you have one of those marked in the table, it won't allow duplication of those fields of information.
    Last edited by pkstormy; 10-07-06 at 22:57.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jul 2006
    Posts
    157
    Can you please tell me how to i define a unique identifier in that script.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Go to the table design of tblAppointments, highlight 1 or more fields to make up your unique identifier, and click the key button in the upper middle. You should see a key next to each field. There is no script needed.
    Last edited by pkstormy; 10-07-06 at 23:09.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    But FIRST, you'll have to get rid of those duplicated records.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Jul 2006
    Posts
    157
    Is there any way by which thru one query i get rid of all dulicate records. I need to check the three fields in the table "Appt, ApptDate,EndDate".

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This is where an autonumber field would have come in handy. Sometimes you can open the table and the duplicated records start at a certain line. Otherwise, design a new query using the wizard (duplicates query). Once it's designed, change it to unique records (via the properties of the query). You may be able to also then change it to a make-table query. If not, simply design another query with this query as it's source and make it a make-table query.
    Last edited by pkstormy; 10-08-06 at 01:22.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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