Duplicate record with child fields

R

Richnep

Hi all,

I am following Allen Brownes code from this page http://allenbrowne.com/ser-57.html
to copy duplicate records with child fields.

I keep geeting a Run TIme Error 3146 ODBC Call failed at the .Update
command in the code. ANy ideas?
Thanks!

BE Sql Server 2k Standard SP4
FE Access 2003 - Code is activated on a form label's on click event

GelNumber is the primary Key in the main table with a 1:N relationship
with the sub table tblWesternBlotWorksheetsub




Private Sub Label120_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in
the subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!GelNumber = Me.GelNumber
!GelDate = Me.GelDate
!GelDescription = Me.GelDescription
!Medium = Me.Medium
!StimulantInhibtor = Me.StimulantInhibtor
!StimulantInhibtorConc = Me.StimulantInhibtorConc
!NumberOfWells = Me.NumberOfWells
!SerumPreStarvationTime = Me.SerumPreStarvationTime
!IncubationTime = Me.IncubationTime
!MembraneType = Me.MembraneType
!GelTechnician = Me.GelTechnician
!BlotDate = Me.BlotDate
!BlotMedium = Me.BlotMedium
!BlotTechnician = Me.BlotTechnician
!Membrane = Me.Membrane
!ExtractHeated100CTime = Me.ExtractHeated100CTime
!ElectrophroesisCurrent = Me.ElectrophroesisCurrent
!ElectrophroesisTime = Me.ElectrophroesisTime
!TransferTimeMaxSettings = Me.TransferTimeMaxSettings
!BlockBuffer = Me.BlockBuffer
!BlockTime = Me.BlockTime
!BlockTemp = Me.BlockTemp
!AbBuffer = Me.AbBuffer
!PrimaryABIncubationTiime =
Me.PrimaryABIncubationTiime
!PrimaryABIncubationTemp = Me.PrimaryABIncubationTemp
!DetectionMedthod = Me.DetectionMedthod
!SecondaryABDesc = Me.SecondaryABDesc
!SecondaryABPartNumber = Me.SecondaryABPartNumber
!SecondaryABVendor = Me.SecondaryABVendor
!SecondaryABLot = Me.SecondaryABLot
!SecondaryABDilution = Me.SecondaryABDilution
!SecondaryABIncubationTime =
Me.SecondaryABIncubationTime
!SecondaryABIncubationTemp =
Me.SecondaryABIncubationTemp
!Addtovalidationsummaries =
Me.Addtovalidationsummaries

'etc for other fields.
.Update
<================================================(ERROR OCCOURS HERE)
'Save the primary key value, to use as the foreign key for
the related records.
.Bookmark = .LastModified
lngID = !GelNumber

'Duplicate the related records: append query.
If Me.
[frmWesternBlotWorksheetsubform].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tblWesternBlotWorksheetsub]
( GelNumber,AddtoWBValidation,Lane,uGloaded,mLLoaded,Extract,ExtractLot,ExtractConc,PartNumber,LotNumber,ProjectNumber,TargetSite,PeptidePartNumberAdded,ulpeptide,Location,AbConc,WestConc,TestVol,uLper2mLLane,ResultsNotes,TargetSignAlStrength,Up/
DownRegulation,ExtraBands,
ABPepComp,PptaseStripping,MutantAnalysis,Notes,
ABLotRecommendation,SerumRecommendation ) " & _
"SELECT " & lngID & " As NewID,
GelNumber,AddtoWBValidation,Lane,uGloaded,mLLoaded,Extract,ExtractLot,ExtractConc,PartNumber,LotNumber,ProjectNumber,TargetSite,PeptidePartNumberAdded,ulpeptide,Location,AbConc,WestConc,TestVol,uLper2mLLane,ResultsNotes,TargetSignAlStrength,Up/
DownRegulation,ExtraBands,
ABPepComp,PptaseStripping,MutantAnalysis,Notes,
ABLotRecommendation,SerumRecommendation " & _
"FROM [Order Details] WHERE GelNumber = " &
Me.GelNumber & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no
related records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdDupe_Click"
Resume Exit_Handler

End Sub
 
G

Guest

I haven't moved to Sql Server yet, so I might be in the dark, but you said:
GelNumber is the primary Key in the main table with a 1:N relationship
with the sub table tblWesternBlotWorksheetsub
Click to expand...

and using this:
.AddNew
!GelNumber = Me.GelNumber
Click to expand...

it seems to me that you are taking the primary key value from the current
record and trying to put it in the primary key of the new record. This would
result in a key violation.
So the code halts.

Comment out or remove this line:
!GelNumber = Me.GelNumber
Click to expand...

and try it.

I used your code (modified of course) without the primary key line and it
worked. I added the PK line and got an error at the update line.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Richnep said:
Hi all,

I am following Allen Brownes code from this page http://allenbrowne.com/ser-57.html
to copy duplicate records with child fields.

I keep geeting a Run TIme Error 3146 ODBC Call failed at the .Update
command in the code. ANy ideas?
Thanks!

BE Sql Server 2k Standard SP4
FE Access 2003 - Code is activated on a form label's on click event

GelNumber is the primary Key in the main table with a 1:N relationship
with the sub table tblWesternBlotWorksheetsub




Private Sub Label120_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in
the subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!GelNumber = Me.GelNumber
!GelDate = Me.GelDate
!GelDescription = Me.GelDescription
!Medium = Me.Medium
!StimulantInhibtor = Me.StimulantInhibtor
!StimulantInhibtorConc = Me.StimulantInhibtorConc
!NumberOfWells = Me.NumberOfWells
!SerumPreStarvationTime = Me.SerumPreStarvationTime
!IncubationTime = Me.IncubationTime
!MembraneType = Me.MembraneType
!GelTechnician = Me.GelTechnician
!BlotDate = Me.BlotDate
!BlotMedium = Me.BlotMedium
!BlotTechnician = Me.BlotTechnician
!Membrane = Me.Membrane
!ExtractHeated100CTime = Me.ExtractHeated100CTime
!ElectrophroesisCurrent = Me.ElectrophroesisCurrent
!ElectrophroesisTime = Me.ElectrophroesisTime
!TransferTimeMaxSettings = Me.TransferTimeMaxSettings
!BlockBuffer = Me.BlockBuffer
!BlockTime = Me.BlockTime
!BlockTemp = Me.BlockTemp
!AbBuffer = Me.AbBuffer
!PrimaryABIncubationTiime =
Me.PrimaryABIncubationTiime
!PrimaryABIncubationTemp = Me.PrimaryABIncubationTemp
!DetectionMedthod = Me.DetectionMedthod
!SecondaryABDesc = Me.SecondaryABDesc
!SecondaryABPartNumber = Me.SecondaryABPartNumber
!SecondaryABVendor = Me.SecondaryABVendor
!SecondaryABLot = Me.SecondaryABLot
!SecondaryABDilution = Me.SecondaryABDilution
!SecondaryABIncubationTime =
Me.SecondaryABIncubationTime
!SecondaryABIncubationTemp =
Me.SecondaryABIncubationTemp
!Addtovalidationsummaries =
Me.Addtovalidationsummaries

'etc for other fields.
.Update
<================================================(ERROR OCCOURS HERE)
'Save the primary key value, to use as the foreign key for
the related records.
.Bookmark = .LastModified
lngID = !GelNumber

'Duplicate the related records: append query.
If Me.
[frmWesternBlotWorksheetsubform].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tblWesternBlotWorksheetsub]
( GelNumber,AddtoWBValidation,Lane,uGloaded,mLLoaded,Extract,ExtractLot,ExtractConc,PartNumber,LotNumber,ProjectNumber,TargetSite,PeptidePartNumberAdded,ulpeptide,Location,AbConc,WestConc,TestVol,uLper2mLLane,ResultsNotes,TargetSignAlStrength,Up/
DownRegulation,ExtraBands,
ABPepComp,PptaseStripping,MutantAnalysis,Notes,
ABLotRecommendation,SerumRecommendation ) " & _
"SELECT " & lngID & " As NewID,
GelNumber,AddtoWBValidation,Lane,uGloaded,mLLoaded,Extract,ExtractLot,ExtractConc,PartNumber,LotNumber,ProjectNumber,TargetSite,PeptidePartNumberAdded,ulpeptide,Location,AbConc,WestConc,TestVol,uLper2mLLane,ResultsNotes,TargetSignAlStrength,Up/
DownRegulation,ExtraBands,
ABPepComp,PptaseStripping,MutantAnalysis,Notes,
ABLotRecommendation,SerumRecommendation " & _
"FROM [Order Details] WHERE GelNumber = " &
Me.GelNumber & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no
related records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdDupe_Click"
Resume Exit_Handler

End Sub
Click to expand...
 
Top