Hi all,
I have some code that does a merge. I read a record into a buffer area, then compare the current record field values to the values in the buffer area. I'm using a DAO.recordset. The problem is that a duplicate is not always detected (see logic below). I've traced through the code and what I see is that the assignment statement:
buffer field = rs field
appears to move only part of the field value! How can this be? All buffer fields are defined as variant. Please see code below. Any help would be appreciated. Thanks.
Set myDB = CurrentDb
Set myRS = myDB.OpenRecordset(strSQL)
myRS.MoveFirst
' begin loop
Do Until myRS.EOF
' move all contact fields into storage
buf_contactID = myRS!contactID
buf_projectID = myRS!cont_projectID
.
.
.
' read the next record
myRS.MoveNext
If myRS.EOF = True Then
GoTo Print_msg
End If
' if the last name, first name, and company name are the same, it's a dup or
' if the email addresses are the same, it's a dup
If (myRS!cont_lname = buf_lname And myRS!cont_fname = buf_fname And myRS!cont_co_name = buf_co_name) Or _
(myRS!cont_co_email1 = buf_co_email1) Then
myRS.Edit
If IsNull(myRS!cont_projectID) And Not IsNull(buf_projectID) Then
myRS!cont_projectID = buf_projectID
End If
.
.
.
myRS.Update
' write out contactID from buffer
strSQL = "INSERT INTO tblTempContact ( contactID ) " & _
"SELECT tblContact.contactID FROM tblContact " & _
"WHERE (((tblContact.contactID)=" & buf_contactID & "));"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
intCount = intCount + 1 ' count number of dups found and set to delete
End If ' end of merge/delete
Loop
I have some code that does a merge. I read a record into a buffer area, then compare the current record field values to the values in the buffer area. I'm using a DAO.recordset. The problem is that a duplicate is not always detected (see logic below). I've traced through the code and what I see is that the assignment statement:
buffer field = rs field
appears to move only part of the field value! How can this be? All buffer fields are defined as variant. Please see code below. Any help would be appreciated. Thanks.
Set myDB = CurrentDb
Set myRS = myDB.OpenRecordset(strSQL)
myRS.MoveFirst
' begin loop
Do Until myRS.EOF
' move all contact fields into storage
buf_contactID = myRS!contactID
buf_projectID = myRS!cont_projectID
.
.
.
' read the next record
myRS.MoveNext
If myRS.EOF = True Then
GoTo Print_msg
End If
' if the last name, first name, and company name are the same, it's a dup or
' if the email addresses are the same, it's a dup
If (myRS!cont_lname = buf_lname And myRS!cont_fname = buf_fname And myRS!cont_co_name = buf_co_name) Or _
(myRS!cont_co_email1 = buf_co_email1) Then
myRS.Edit
If IsNull(myRS!cont_projectID) And Not IsNull(buf_projectID) Then
myRS!cont_projectID = buf_projectID
End If
.
.
.
myRS.Update
' write out contactID from buffer
strSQL = "INSERT INTO tblTempContact ( contactID ) " & _
"SELECT tblContact.contactID FROM tblContact " & _
"WHERE (((tblContact.contactID)=" & buf_contactID & "));"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
intCount = intCount + 1 ' count number of dups found and set to delete
End If ' end of merge/delete
Loop