Results 1 to 9 of 9

Preventing Duplicate Records

  1. #1
    d9pierce1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Tennessee
    Posts
    201

    Preventing Duplicate Records


    I have an unbound form i will be using to add records to a table. Combo Boxes with SQL Insert code. Works just fine. I am trying to check and prevent from being duplicated and i think i know why this isnt working but not sure? I sure cant seam to fix it to work. I beleive it is not working due to being text i am looking up and its seeking a number. Any assistance would be great.
    Thanks
    Dave


    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub CmdAddCSZ_Click()
        If IsNull(CboCity) Or IsNull(CboState) Or IsNull(CboZipCode) Then
                MsgBox "You're missing some data!"
                Exit Sub
        End If
        
        Dim ZC As String
        Dim CC As String
        Dim stLinkCriteria As String
        Dim ZCkey As String
        Dim CCkey As String
        
        ZC = Nz(Me.CboZipCode, "")
        CC = Nz(Me.CboCity, "")
        ZCkey = Nz(DLookup("ZipCode", "CSZTbl", "ZipCode=" & "'" & ZC & "'"), 0)
        CCkey = Nz(DLookup("City", "CSZTbl", "City=" & "'" & CC & "'"), 0)
        
        'Neither exist, add record
        If ZCkey = 0 And CCkey = 0 Then
            Exit Sub
        End If
        'both ZC and CC exist but not in same record, Add Record
        If ZCkey > 0 And CCkey > 0 And (ZCkey <> CCkey) Then
            Exit Sub
        End If
        'both exist and are in same existing record, Cancel
        If ZCkey > 0 And CCkey = CCkey Then
            Me.Undo
            'Cancel = True
            MsgBox "Warning!  City of " _
            & CC & " and the ZipCode " & ZC & " are already in Database." _
            & vbCr & vbCr & "You cannot duplicate record.", _
            vbInformation, "Duplicate Information"
            Exit Sub
        End If
        
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO CSZTbl (ZipCode, Primary, City, State, County) " & _
            "VALUES (" & "CboZipCode" & ", " & ChkPrimary & ", " & "CboCity" & ", " & "CboState" & ", " & "CboCounty" & ")"
        DoCmd.SetWarnings True
        
        DoCmd.Close

  2. #2
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,050
    These lookups make no sense. You're looking up the zipcode by specifying the zipcode as the criteria. I think you mean to lookup the key field.
    Code:
    ZCkey = Nz(DLookup("ZipCode", "CSZTbl", "ZipCode=" & "'" & ZC & "'"), 0)
    CCkey = Nz(DLookup("City", "CSZTbl", "City=" & "'" & CC & "'"), 0)
    Let's assume you have a primary key in CSZTbl, called CSZ_PK.

    Code:
    ZCkey = Nz(DLookup("CSZ_PK", "CSZTbl", "ZipCode=" & "'" & ZC & "'"), 0) CCkey = Nz(DLookup("CSZ_PK", "CSZTbl", "City=" & "'" & CC & "'"), 0)
    Last edited by davegri; 08-01-2019 at 09:18 AM. Reason: fix format

  3. #3
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,050
    Taking a look at the db, your existing code seems overcomplicated. See if this works for you:

    Code:
    Option Compare Database
    Option Explicit
    Private Sub CmdAddCSZ_Click()
        Dim sSQL As String
        If IsNull(CboCity) Or IsNull(CboState) Or IsNull(CboZipCode) Then
                MsgBox "You're missing some data!"
                Exit Sub
        End If
        
        Dim ZC As String
        Dim CC As String
        Dim ZCkey As String
        ZC = Nz(Me.CboZipCode, "")
        CC = Nz(Me.CboCity, "")
        ZCkey = Nz(DLookup("CSZID", "CSZTbl", "ZipCode=" & "'" & ZC & "'" & " AND City =" & "'" & CC & "'"), 0)
        
        'duplicate, exit
        If ZCkey > 0 Then
            MsgBox "Duplicate"
            Exit Sub
        End If
        
        sSQL = "INSERT INTO CSZTbl (ZipCode, Primary, City, State, County) " & _
            "VALUES ('" & ZC & "', " & ChkPrimary & ", '" & CC & "', '" & CboState & "', '" & CboCounty & "')"
        CurrentDb.Execute sSQL, dbFailOnError
        
        DoCmd.Close
    End Sub

  4. #4
    d9pierce1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Tennessee
    Posts
    201
    thank you very much!

  5. #5
    d9pierce1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Tennessee
    Posts
    201
    HI Davegri,
    I just wanted to thank you again, that worked perfect. With that, i would like to know where does someone get this knowledge? I love access and SQL and have taken some coarses but these things were not covered at all. I just have to wonder where you learned this? Does it jsut come natural, years of doing it or did you take some schooling? Where can i go to get this knowledge from? I would really love to learn and i beleive i would be good at it eventually? If you have a minute, could you just let me know if you can recomend something. I have read books, taken a few coarses, and trial and error methods but it just amazes me how you are able to put this togeither and make it do things.
    Thanks again
    Dave

  6. #6
    d9pierce1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Tennessee
    Posts
    201
    Hi Davegri,
    I have another question. I am trying to do an after update event on the form for the CboZipCode. I am not sure to use an If statement with a DLookup or an Iff With a DLookup or a Dlookup with an If statement? Confused?

    What I am trying to do is set the ChkPrimary automatically depending on weather or not there is all ready a Primary selected in table for the ZipCode Field. If there is a Primary = True to that ZipCode, then I want to set ChkPrimary to = False for the new record, if no Primary for that ZipCode , then set ChkPrimary to = True. There can be more than one city per ZipCode but only one Primary. Not real sure how to go about this writing the criteria. Have played with a lot of DLookup Statements but so far nothing has worked out to do what I am trying to do here.
    Thanks
    Dave

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,303
    Quote Originally Posted by d9pierce1 View Post
    HI Davegri,
    I just wanted to thank you again, that worked perfect. With that, i would like to know where does someone get this knowledge? I love access and SQL and have taken some coarses but these things were not covered at all. I just have to wonder where you learned this? Does it jsut come natural, years of doing it or did you take some schooling? Where can i go to get this knowledge from? I would really love to learn and i beleive i would be good at it eventually? If you have a minute, could you just let me know if you can recomend something. I have read books, taken a few coarses, and trial and error methods but it just amazes me how you are able to put this togeither and make it do things.
    Thanks again
    Dave
    Hi Dave,
    I think there are several factors involved. Formal training is one, but not necessarily the most critical/important. Experience is important -as are an understanding of the concepts of database; an ability to identify and analyze facts/opportunities; methodical problem decomposition and solving; appreciation of tools/facilities (data model, data flow diagrams..); prototyping....

    I have identified a number of articles related to Database Planning and Design in a link in my signature. I have found these to be clear and factual and some of the best I have found over the years.

    As a side note, I find many "newbies" to Access(or other DBMS software) think that buying the software is a major step and magically it will create databases for them. And many of these have the latest hardware and software. Database and life generally doesn't quite work like that. If you scan the forums, you'll note that many suggestions revolve around -"clearly identifying the requirement"; "can you give a simple example of your input and expected output"; -basically effective communication so that readers and posters are on same frequency. There is a basic understanding that must exist, but also a willingness to accept comments and criticism and refine/build upon existing knowledge and abilities. A lot of this comes down to learning by doing; reviewing code/databases; tutorials; videos...
    But often a key factor is having a personal issue/problem/opportunity that needs a database solution. Since it's a personal need, there is focus and an emphasis to learn and resolve the various issues that arise.

    It isn't any one type of individual that has/learns these various factors/traits. Some of the best data analysts, DBAs I have worked with have been people who have had some career, then undertook some "retraining" to change their line of work. These have included --shoe salesman; child care worker; lay preacher; urban geographer, school teacher....

    Bottom line seems to be keen to learn, somewhat self-motivated, willingness to experiment, inquisitive, persistent...

    Hope this is useful.
    Good luck.
    Last edited by orange; 08-03-2019 at 08:21 AM.

  8. #8
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,050
    I have another question. I am trying to do an after update event on the form for the CboZipCode. I am not sure to use an If statement with a DLookup or an Iff With a DLookup or a Dlookup with an If statement? Confused?
    Try it this way. New code in red:
    Code:
    Option Compare Database
    Option Explicit
    Private Sub CmdAddCSZ_Click()
        Dim sSQL As String
        Dim pCount As Integer
        Dim TF As Boolean
        If IsNull(CboCity) Or IsNull(CboState) Or IsNull(CboZipCode) Then
                MsgBox "You're missing some data!"
                Exit Sub
        End If
        
        Dim ZC As String
        Dim CC As String
        Dim ZCkey As Long
        ZC = Nz(Me.CboZipCode, "")
        CC = Nz(Me.CboCity, "")
        ZCkey = Nz(DLookup("CSZID", "CSZTbl", "ZipCode=" & "'" & ZC & "'" & " AND City =" & "'" & CC & "'"), 0)
        
        'duplicate, exit
        If ZCkey > 0 Then
            MsgBox "Duplicate"
            Exit Sub
        End If
        'Count number of Primary=True for this city
        pCount = DCount("City", "CSZTbl", "City='" & CC & "'" & " AND Primary =" & True)
        
        If pCount = 0 Then
            TF = True
        Else
            TF = False
        End If
        
        sSQL = "INSERT INTO CSZTbl (ZipCode, Primary, City, State, County) " & _
            "VALUES ('" & ZC & "', " & TF & ", '" & CC & "', '" & CboState & "', '" & CboCounty & "')"
        CurrentDb.Execute sSQL, dbFailOnError
        
        DoCmd.Close
    End Sub
    Last edited by davegri; 08-02-2019 at 08:26 AM. Reason: more red

  9. #9
    d9pierce1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Tennessee
    Posts
    201
    thank you, I appreciate that very much

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

Similar Threads

  1. Preventing duplicate records
    By dniezby in forum Programming
    Replies: 13
    Last Post: 03-24-2017, 12:58 PM
  2. Preventing Duplicate Entries
    By FormerJarHead in forum Modules
    Replies: 12
    Last Post: 01-29-2015, 10:16 AM
  3. Preventing duplicate entries...
    By graccess in forum Forms
    Replies: 4
    Last Post: 01-26-2014, 09:29 PM
  4. Preventing duplicate record
    By wpryan in forum Forms
    Replies: 2
    Last Post: 03-30-2013, 09:43 AM
  5. preventing duplicate records
    By Alliana Gray in forum Access
    Replies: 6
    Last Post: 08-11-2011, 01:18 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
  •  
Tech Forums: Microsoft Office Forums