questions on variant data type

ckib

Board Regular
Joined
Sep 14, 2005
Messages
219
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

ezakovich_35

Board Regular
Joined
Oct 21, 2005
Messages
65
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

Board Regular
Joined
Sep 14, 2005
Messages
219
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

Board Regular
Joined
Sep 14, 2005
Messages
219
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,628
Messages
5,597,247
Members
414,133
Latest member
lucid33

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top