Insert row ...key violation

[stingers]

I am trying to insert a row into a table where there exists a unique index on an integer field. I am familiar with access autonumber fields where the db makes it easy and automatically inserts the next number, but how do I do this when using SQL tables. (I am trying to upsize) I continue to get a key violation, which I am sure is because I dont know how to populate the ID field. I am using the following code to do the insert..

*******

'Perform the SQL update for tblLOANMEMO
sql1 = "INSERT INTO dbo_tblLOANMEMO " _
& "(MemoDate, DescCode, UserID, Detail, LoanNumber, StudentID)"

sql2 = "VALUES " & "('" & dteTransDate & "', '" & strCode _
& "', '" & strUser & "', '" & strDetail & "', '" & lngLnNumber _
& "', '" & lngStudentID & "');"

DoCmd.RunSQL sql1 & sql2

******

the field in question is LoanMemoCounter

thanks in advance
ryan paul

 

[rac2]

Hello Ryan,

Well now. Does the error message state that LoanMemoCounter is the field with the key violation?

That would be very strange since LoanMemoCounter is not listed as one of the INSERT columns.

What about all those ID columns? Are they foreign keys? If so, are any new ID values added to the primary tables before this INSERT query runs? If not, that would generate an error.

Just some thoughts.

Richard

 

[rac2]

Another thought. Is there a foreign key in the dbo_tblLOANMEMO table that you have not included in the INSERT? That would generate an error because a foreign key cannot be NULL.

 

[stingers]

the field tblLnMemoCounter was in fact the problem. I had to create another recordset to find the highest value:

Set rst = CurrentDb.OpenRecordset("SELECT Max dbo_tblLOANMEMO.LnMemoCounter) AS MaxOfLnMemoCounter FROM dbo_tblLOANMEMO;&quot

and then add one to this value, to get the next number to keep the index intact, and add it to the insert statement. I am not sure if this is the best thing to do. Any ideas?

Ryan

p.s. the error message doesn't mention the field but since it can not be null the led me to think that this was the error.

 

[hirick]

Ryan, is LnMemoCounter was an identity field (equivalent to an AutoNumber in Access)? If not, can you make it into an identity?

Holly Irick

 

[stingers]

yes! duh on me