Append Query- null values

[dhaskins43]

Hi there

I'm using an append query to add records to a linked table. The problem I'm having having is with a [Barcode] field that is indexed to Yes(no duplicates) but Required = No. If I add a record manually without a barcode, it is fine, but when I try to append records with no barcode I'm getting a Key Violation error. I'm assuming it's trying to add Null more than once and considers it a duplicate.

I've been searching all day and have found a few hints but no real answer. Some ideas that looked promising:
? use recordset instead of append query
? use another method to exclude duplicates (validation rule? VBA code?)
? improve the SQL code somehow

Any ideas or directions to point me in would be most appreciated!!

 

[jal]

Hi there

I'm using an append query to add records to a linked table. The problem I'm having having is with a [Barcode] field that is indexed to Yes(no duplicates) but Required = No. If I add a record manually without a barcode, it is fine, but when I try to append records with no barcode I'm getting a Key Violation error. I'm assuming it's trying to add Null more than once and considers it a duplicate.

I've been searching all day and have found a few hints but no real answer. Some ideas that looked promising:
? use recordset instead of append query
? use another method to exclude duplicates (validation rule? VBA code?)
? improve the SQL code somehow

Any ideas or directions to point me in would be most appreciated!!

I am just a beginner, but I doubt that ANY null values are permissible with a primary key. When you add a record manually, possibly what is happening is that the default value of the column (presumably not null) is populating. Whereas when you do the append, perhaps it is actually trying to put a null value in there. I really know nothing about what you are doing, and virtually nothing of Access. Is there some way to change the value being inserted from null to an empty string - or some other non-null value?