Insert Into .. autonumber field

[Stapes]

Hi

I am trying to append records into a table that has its first field as
an Autonumber field. The system is meant to generate the next unique
number.

This is the SQL: -

INSERT INTO TM_CampaignCustomers ( RunID, FK_Contact, Spend, Books,
DT_Audit, NUM_Invoice )
SELECT tempCampaignCustomers.RunID, tempCampaignCustomers.FK_Contact,
tempCampaignCustomers.Spend, tempCampaignCustomers.Books,
tempCampaignCustomers.DT_Audit, tempCampaignCustomers.NUM_Invoice
FROM tempCampaignCustomers;

It does not metion the AutoNumber field, TM_CCID, and generates an
error - hasn't added any records because of key violations. I guess it
is trying to stuff the RunID in the unmentioned field.
Is there a way round this?

Stapes

 

[Ken Snell \(MVP\)]

That query will not try to put data into the TM_CCID field because the
TM_CCID field is not in the list of fields in the first set of parentheses.
The error that you're getting suggests to me that one or more of the other
fields has a Unique index set on it, and the data that you're trying to
append violate the index's constraints. Carefully review the data that the
SELECT portion of the query returns, and see if indeed you're trying to add
"non-unique" data for one or more fields (note -- you may have a Unique
constraint set on a multi-field index as well).

 

[Scott McDaniel]

Hi

I am trying to append records into a table that has its first field as
an Autonumber field. The system is meant to generate the next unique
number.

This is the SQL: -

INSERT INTO TM_CampaignCustomers ( RunID, FK_Contact, Spend, Books,
DT_Audit, NUM_Invoice )
SELECT tempCampaignCustomers.RunID, tempCampaignCustomers.FK_Contact,
tempCampaignCustomers.Spend, tempCampaignCustomers.Books,
tempCampaignCustomers.DT_Audit, tempCampaignCustomers.NUM_Invoice
FROM tempCampaignCustomers;

It does not metion the AutoNumber field, TM_CCID, and generates an
error - hasn't added any records because of key violations. I guess it
is trying to stuff the RunID in the unmentioned field.
Is there a way round this?

Do you have any Unique indexes declared on any fields in your
CampaignCustomers table? While the Autonumber is not infallible, normally it
works as planned and your Insert statement should not have any interaction
with the CCID field. Also, your Insert statment will only insert data into
the columns you designate; it won't try to put the
tempCampaigncustomers.RunID into the first table column, it'll put it into
the RunID column in TM_CampaignCustomers.

 

[Stapes]

Hi

Well, it did not work as you have said. In the end, I hard coded it
like this, and it worked:

On Error GoTo Err_Command0_Click


Dim db As Database
Dim RST_Out As Recordset
Dim RST_IN As Recordset
Dim INT_Count As Integer

Dim NewID As Long


Set db = CurrentDb
INT_Count = 0
'#### open the input table
Set RST_IN = db.OpenRecordset("tempCampaignCustomers")

Set RST_Out = db.OpenRecordset("TM_CampaignCustomers")

Do Until RST_IN.EOF

With RST_Out
.MoveLast
NewID = !TM_CCID
NewID = NewID + 1
.AddNew


!RunID = RST_IN.Fields(1) 'RunID
!TM_CCID = NewID
!FK_Contact = RST_IN.Fields(2)
!Spend = RST_IN.Fields(3)
!Books = RST_IN.Fields(4)
!DT_Audit = RST_IN.Fields(5)
!NUM_Invoice = RST_IN.Fields(6)
.Update
End With
RST_IN.MoveNext
INT_Count = INT_Count + 1
Loop
RST_Out.Close
RST_IN.Close
MsgBox INT_Count & "Records added"
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description & "Record Count = " & INT_Count
Resume Exit_Command0_Click

 

[Ken Snell \(MVP\)]

By chance, are some of the table's fields text fields? And were you trying
to generate the SQL statement and run it from VBA code? If yes to both, you
need to concatenate the ' delimiter character around the values from the
source table's fields for the text fields.

Also, the code that you're showing is setting an explicit value to the
Autonumber field in your table. Your original SQL statement did not include
the autonumber field. It should be unnecessary to include the autonumber
field because the table will set the value of that field automatically.
Therefore, one must wonder if that field is correctly set up as an
autonumber field -- or whether your needs would dictate that your original
query should be setting its value via the append query.