Variant Data Type Error

[FCCC]

Dim itemctr As Variant
If IsNull(Item_No) Then
itemctr = DCount("Item_No", "dbo_PMOption_History&quot
Item_No = itemctr + 1
End If

This is my simple code that automatically assigns item number everytime a record is added. Unfortunately I get error that says "You tried to assign the Null value to a variable that is not a Variant data type (Error 3162)". It will assign a number though but the error keeps popping-up everytime a new record was added.

Item_No is the key field in dbo_PMOption_History that doesn't allow Null Value.


Does someone have an idea why I get this error.

Thanks for any help!

 

[thornmastr]

I don’t have your history table so this is only a suspicion which you will have to confirm or refute.

If your history table allows nulls in item no, then I would remind you, that

2 + Null + 3 + n + n…. = null,

so, one null value in that field will return null and item_no = itemctr(null) + 1 is null.

Let me know if this is any help.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[FCCC]

Hi Robert,

My table doesn't allow null. It is an SQL table and I made Item_No as the key field. This is the only way I can enter duplicate data as the key field data are unique. If I don't have a key field, it will allow me to enter duplicate data but if I will delete one entry, it will delete all of the entries that has the same data as I am deleting.