Solved Add record if not found, still adding record (1 Viewer)

[J_Fo]

Hello -

I am attempting to add records to a table only if the record does not exist based on the "Fingerprint" field. When I step through the code, in the fuction FindRecord at the bottom of my code snippet, when it gets to FindRecord = Not rsDestination.NoMatch I get a rsDestination.NoMatch True or False, depending if the record is found. The record is still being added when a match is found. Any help in resolving what I'm doing wrong would be appreciated.

Destination Table:
ID "Fingerprint" FP_ID
75 J002-601-18-17 10001
76 J002-601-18-30 10002
77 J002-601-18-99 10003
78 J002-601-18-19 10004
79 J002-601-18-30 10005
80 J002-601-18-9 10006

Option Compare Database
Option Explicit

Public Sub tryagain()

    Dim db As DAO.Database
    Dim rsSource As DAO.Recordset
    Dim rsDestination As DAO.Recordset
    Dim newNumber As Long

    
    ' Open the source recordset from the select query
    Set db = CurrentDb
    Set rsSource = db.OpenRecordset("scheduleID", dbOpenDynaset)
    
   ' Open the destination recordset
    Set rsDestination = CurrentDb.OpenRecordset("ListScheduleIDtbl", dbOpenDynaset, dbSeeChanges)
    

    ' If not found, proceed to add the new record
        ' Loop through the records in rsSource
        Do While Not rsSource.EOF
    

'  'Check if the record already exists based on "Fingerprint"

If FindRecord(rsDestination, "Fingerprint", rsSource.Fields("Fingerprint").value) Then
         'Record already exists, do not add a new one
         'Exit Sub
    End If

    'Set Fingerprint ID field 
    newNumber = Nz(DMax("FP_ID", "ListScheduleIDtbl"), 10000) + 1

                ' Add the record to the destination table
                rsDestination.AddNew
                'For Each fld In rsSource.Fields
                    'rsDestination.Fields(fld.Name).Value = fld.Value
                'Next fld
                
            'Assign claim import fields
                rsDestination.Fields("Fingerprint").value = rsSource.Fields("Fingerprint").value
                rsDestination.Fields("FP_ID").value = newNumber
                  
              
                rsDestination.Update
                
            'End If
            rsSource.MoveNext
            
        Loop
                
        
        ' Clean up
        rsDestination.Close
        rsSource.Close
        Set rsDestination = Nothing
        Set rsSource = Nothing
        Set db = Nothing
 Exit Sub
End Sub


Function FindRecord(rsDestination As DAO.Recordset, Fingerprint As String, value As Variant) As Boolean

    ' Check if there are any records
    If Not rsDestination.EOF Then
        rsDestination.FindFirst "Fingerprint" & " = '" & value & "'"
        FindRecord = Not rsDestination.NoMatch
    Else
        ' No records found
        FindRecord = False
    End If

End Function

 

[tvanstiphout]

That's a lot of code.
Alternatively you would use an append query to copy data from Source to Destination, with a Fingerprint parameter. Then use 1 line of code:
Currentdb.Execute yourQuery, dbFailOnError
This will throw a runtime error if the record already existed (assuming you have a unique index on Destination.Fingerprint).

 

[J_Fo]

That's a lot of code.
Alternatively you would use an append query to copy data from Source to Destination, with a Fingerprint parameter. Then use 1 line of code:
Currentdb.Execute yourQuery, dbFailOnError
This will throw a runtime error if the record already existed (assuming you have a unique index on Destination.Fingerprint).

Thank you Tom. Yes, using an append query is a lot less code. What I'm trying to fix is the start to a larger project. My attempt to build it out in small bites since this level of coding is new to me.

 

[tvanstiphout]

Thank you Tom. Yes, using an append query is a lot less code. What I'm trying to fix is the start to a larger project. My attempt to build it out in small bites since this level of coding is new to me.

There are additional considerations I left out in first instance. dbSeeChanges leads me to think you have a linked table to a server-class database server like SQL Server. Opening a recordset over all records is incredibly wasteful and a violation of proper client-server techniques. Such approach may come back to haunt you in the form of locking issues. (ask me how I know - wry smile).

 

[plog]

Yes, using an append query is a lot less code. What I'm trying to fix is the start to a larger project. My attempt to build it out in small bites since this level of coding is new to me.

The 2nd and 3rd sentences are non-sequitor to the first. If anything they contradict it.

"Small bites" would mean less, more efficient code. Recordsets are overkill for both things you are trying to accomplish:

1. DCount to find duplicates. With 1 line of code with a DCount you know if duplicates exists.

2. INSERT INTO query to add data. With 2 lines of code (1 to construct SQL, 1 to execute it) you can easily add your new record.

All that code could be considerable smaller with those 2 changes. 1 sub, no recordsets and probably 6 lines of code total.

 

[ebs17]

What I'm trying to fix is the start to a larger project. My attempt to build it out in small bites since this level of coding is new to me.

However, small nuggets can be solved with small, clear codes, and that is better than long codes that are confusing and leave a lot of room for errors.
Elaborate and more complex measures can be saved for more complex tasks. This is just about appending records with two conditions. You can do something like this in one go.

The query for attaching only new fingerprints without running numbering looks something like this.

sSQL = "INSERT INTO ListScheduleIDtbl(Fingerprint)" & _
       " SELECT S.Fingerprint FROM (SELECT DISTINCT Fingerprint FROM scheduleID) AS S" & _
       " LEFT JOIN ListScheduleIDtbl AS L" & _
       " ON S.Fingerprint = L.Fingerprint WHERE L.Fingerprint IS NULL"

Inclusive running numbering

 sSQL = "INSERT INTO ListScheduleIDtbl((Fingerprint, FP_ID)" & _
        " SELECT S.Fingerprint," & _
        " QueryIncrement(S.Fingerprint, DMax('FP_ID', 'ListScheduleIDtbl') + 1) AS RunNo" & _
        " FROM (SELECT DISTINCT Fingerprint FROM scheduleID) AS S" & _
        " LEFT JOIN ListScheduleIDtbl AS L ON S.Fingerprint = L.Fingerprint" & _
        " WHERE L.Fingerprint IS NULL AND Reset_Globals() = True"

' mod_QueryIncrement
Public gQI_Category As String
Public gQI_Icount As Long

Public Function QueryIncrement(ByVal Init As Variant, ByVal StartValue As Long, ParamArray Categories() As Variant) As Long
    Dim k As Long, v As Variant
    Dim sAllCategories As String

    v = Init
    If IsMissing(Categories) Then
        sAllCategories = "$$$$$"
    Else
        For k = 0 To UBound(Categories)
            If IsNull(Categories(k)) Then Categories(k) = vbNullString
        Next
        sAllCategories = Join(Categories, "|")
    End If

    If gQI_Category = sAllCategories Then
        gQI_Icount = gQI_Icount + 1
    Else
        gQI_Category = sAllCategories
        gQI_Icount = StartValue
    End If
    QueryIncrement = gQI_Icount
End Function

Public Function Reset_Globals()
    gQI_Category = vbNullString
    gQI_Icount = 0
    Reset_Globals = True
End Function

 

[J_Fo]

Thanks everyone for the feedback, I've learned lots and it is working.