Results 1 to 6 of 6
  1. #1
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99

    Custom built message to prevent Duplicate Records

    Hi Access Family,



    I have this problem I've been trying to solve. I have this table with a foreign key and a Datefield that we want the user to not duplicate. So I indexed the two fields. one person can have many dates on record, but we don't one them to have multiply records with the same date. your next record must be a new date.
    I have this code am playing around with and it sort of works the way I would like for the user. One small problem , I am unable to set another record, the msgbox keeps poping up when I try to create another record even if its unique. More-less am trying to get rid of MS Access generic messaging when there's a duplicate where two fields are Indexed.

    Can someone guide me to my mistake here... is it that a string criteria is missing. I have did everything possible with my limited vba skills

    Code:
    Private Sub txtNextDate_BeforeUpdate(Cancel As Integer)       Dim vCount As Integer
          
    If IsNull(Me.txtNextDate) Then
            MsgBox " A Date is Required!"
            Cancel = True
            Exit Sub
        End If
        vCount = DCount("Inm_id", "tblsalesDates", "txtNextDate")
        
        Debug.Print salesDate, vCount
        Select Case Me.NewRecord
            Case False
                If vCount > 1 Then
          MsgBox "Sorry! you cannot add same date." & vbCrLf & _
         "A Duplicate Record Cannot Exist.", vbExclamation, "NO Duplicates"
                   Cancel = True
                    Me.Undo
                End If
                End Select
                End Sub

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't think you want to look for a field value in a before update event (txtNextDate) because if the control is bound and there was a value, then it may not match what's showing in the control because that hasn't been committed yet. I think I would use its Text property. I would try DLookup - simpler than what you have:

    Private Sub txtNextDate_BeforeUpdate(Cancel As Integer)
    If DLookup("Inm_id", "tblsalesDates", Me.txtNextDate.Text)>0 Then '<<means the date exists

    then your msgbox, cancel and undo
    EDIT - you'd only need to worry about a new record if you were opening this form in data entry mode - even then not really. That's assuming this field can be edited once the record exists. Then what matters most is finding if the date exists without regard to new or old record.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Hey Micron

    My response is a bit late but I found the answer to my initial post earlier. You have to use the "Const" Function and just trap the duplication error message that's built in with Ms Access.
    I have indexed both fields in a Table, because we don't want the user to duplicate dates in a date field with the foreign_keys of potential candidates.


    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Why instead all of this coding you don't create an UNIQUE composite index based on person ID and date field? Based on your post (it is possible to add same dates for same person several times ), even when you created your index based on those 2 fields, you didn't set it unique). When the index is designed correctly, it will be impossible to add same date for same person repeatedly!

  5. #5
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Hi AvrilLaanemets You are correct, yes this is what was done in the table design...

    Why instead all of this coding you don't create an UNIQUE composite index based on person ID and date field? Based on your post (it is possible to add same dates for same person several times ), even when you created your index based on those 2 fields, you didn't set it unique).
    As the Post is titled the main objective was a Custom built message and not the generic one that is usually displayed... yes all that coding was not necessary

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    I deleted this!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 09-09-2020, 10:28 PM
  2. Replies: 1
    Last Post: 12-07-2016, 09:46 AM
  3. How do you prevent duplicate records?
    By cwjakesteel in forum Database Design
    Replies: 28
    Last Post: 11-28-2016, 09:05 AM
  4. How to prevent duplicate records
    By talktime in forum Programming
    Replies: 7
    Last Post: 05-15-2013, 11:02 PM
  5. Replies: 13
    Last Post: 12-31-2012, 10:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums