questions on variant data type

[ckib]

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

 

[ezakovich_35]

variant data type

it may not be the variant data type but the DAO recordset. I saw a MoveFirst statement, with a DAO recordset, it should be followed immediately by a MoveLast statement so that the whole recordset is initialized otherwise I have found errors. Not sure if that will fix this problem but you should try it.

 

[ckib]

My guess is that is not the problem because the value of the recordset field shows the full value (while tracing). I have recently been told to stay clear of using Variant data type. I used it so that I could test for null with IsNull. I'm going to try re-coding using long and string variables and using IsEmpty to test for the presence of a value.

I had not heard of the need to do MoveLast ... then what ... do a MoveFirst again to position the recordset at the first record?

Thanks.

 

[ckib]

I just realized that the reason I used Variant data type for all the buffer fields is so that I could simply read a record from the recordset and move the fields into variables like this:

buf_projectID = myRS!cont_projectID

but if the target of the assignment statement is a string the instruction will fail if the recordset field has a value of null (you get an error something like 'invalid use of null').

This must be a common problem with a simple solution ... and there must be a better way than having to test every recordset field for null before attempting to assign the value to the buffer field!

Any help would be appreciated. Thanks.