Struggling with my insert query...

[ArielZusya]

I've got this insert query running embedded in a VBA statement. The VBA
looks at a table and finds the largest number stored in the column BatchNum
and then adds 1 to it and stores that new number in the variable lngBatch.
It then runs the following SQL (note that the <lngBatch> as seen below is
just a placeholder... in the actual code the SQL statment is put into a
string and would be "... CaseNum," & lngBatch & vbCrLf & "FROM ..."):

INSERT INTO tblRef ( RefNum, BatchNum )
SELECT DISTINCT CaseNum, <lngBatch>
FROM tblMain;

This statement works just fine for my first insert (when tblRef is empty and
this is the first record being insterted) but when I go to the second time I
run this query on a new set of data with unique values, I get the following
error:

The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again.

What am I doing wrong? Thanks for your help!

 

[Michael Gramelspacher]

I've got this insert query running embedded in a VBA statement. The VBA
looks at a table and finds the largest number stored in the column BatchNum
and then adds 1 to it and stores that new number in the variable lngBatch.
It then runs the following SQL (note that the <lngBatch> as seen below is
just a placeholder... in the actual code the SQL statment is put into a
string and would be "... CaseNum," & lngBatch & vbCrLf & "FROM ..."):

INSERT INTO tblRef ( RefNum, BatchNum )
SELECT DISTINCT CaseNum, <lngBatch>
FROM tblMain;

This statement works just fine for my first insert (when tblRef is empty and
this is the first record being insterted) but when I go to the second time I
run this query on a new set of data with unique values, I get the following
error:

The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again.

What am I doing wrong? Thanks for your help!

I am thinking that the primary key of tblRef is CaseNum when it should be RefNum
and BatchNum. In other words, the table needs a compound key, because CaseNum
is being duplicated when the second record is inserted. The error message is
clear; inserting the second record is duplicating the primary key.

 

[ArielZusya]

Woops... my bad... that was a typo... At one point I was using CaseNum
instead of RefNum but it should have read RefNum in both spots... as in:

INSERT INTO tblRef ( RefNum, BatchNum )
SELECT DISTINCT RefNum, <lngBatch>
FROM tblMain;

So... the primary key of tblRef was RefNum and strangely enough the RefNum
was unique but regardless of my typo and that strange turn, your solution was
right on. Thanks for the help!