hi BoB
yeah its not good at all, im at the stage of giving up.
i have 1 form Quotations and 4 subforms: QuotationParts, QuotationLabour, QuotationOutwork, QuotationPaint.
now instances arise where we need to edit a quote but we would like access to save the origional quote upon some1 trying to edit the quote.
i have created a query in query desighn whereby ive added the above tables and dragged all thier fields down to the box and ive saved the query. this is the code i used but upon editing a quote i get the "Run-Time Error 3164': field cannot be edited" error
the code is below, ive only added half the tables to this code just for testing purposes, if it would work then i'd add the other half because i diddnt want to add all the tables then the code doesnt work at the end.
Private Sub Form_Dirty(Cancel As Integer)
'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 any 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
TrackID = Me.TrackID
TrackID_Main = Me.TrackID_Main
BookedInBy = Me.BookedInBy
BookInMethod = Me.BookInMethod
BookInDate = Me.BookInDate
BookInComments = Me.BookInComments
BookInDamages = Me.BookInDamages
ClaimNumber = Me.ClaimNumber
AuthorisationStatusID = Me.AuthorisationStatusID
WorkProviderID = Me.WorkProviderID
EstimatorID = Me.EstimatorID
PolicyNumber = Me.PolicyNumber
OtherReference = Me.OtherReference
RepairTypeID = Me.RepairerTypeID
InsuredAmount = Me.InsuredAmount
ExcessAmount = Me.ExcessAmount
DiscountPercentage = Me.DiscountPercentage
EstimatedRepairCost = Me.EstimatedRepairCost
RepairsAuthorisedID = Me.RepairsAuthorisedID
Damages = Me.Damages
DateOfAccident = Me.DateOfAccident
RepairStartDate = Me.RepairStartDate
RepairEndDate = Me.EstimatedRepairDate
EstimatedRepairTime = Me.EstimatedRepairTime
ActualRepairTime = Me.ActualRepairTime
PartsOrderedDate = Me.PartsOrderedDate
HireCarStartDate = Me.HireCarStartDate
HireCarEndDate = Me.HireCarEndDate
UnderWarranty = Me.UnderWarranty
WarrantyExpires = Me.WarrantyExpires
PreAccidentCondition = Me.PreAccidentCondition
SteeringCondition = Me.SteeringCondition
BrakesPedalTravel = Me.BrakesPedalTravel
PleaceOfInspection = Me.PleaceOfInspection
SeverityOfImpact = Me.SeverityOfImpact
VehicleStatusOnInspection = Me.VehicleStatusOnInspection
DateOfVehicleInspection = Me.DateOfVehicleInspection
TyreCondition = Me.TyreCondition
TreadDepthLHF = Me.TreadDepthLHF
TreadDepthRHF = Me.TreadDepthRHF
TreadDepthRHR = Me.TreadDepthRHR
TreadDepthLHR = Me.TreadDepthLHR
ShopID = Me.ShopID
CustomerID = Me.CustomerID
CompanyName = Me.CompanyName
CustomerName = Me.CustomerName
CustomerSurname = Me.CustomerSurname
Address = Me.Address
Suburb = Me.Suburb
City = Me.City
PostalCode = Me.PostalCode
PhoneNumber = Me.PhoneNumber
FaxNumber = Me.FaxNumber
CellNumber = Me.CellNumber
SendViaCell = Me.SendViaCell
'etc for other fields.
.Update
'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = !OrderID
'Duplicate the related records: append query.
If Me.[Quotation Subform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [Quotation Details] ( TrackID, TrackID_Main, BookedInBy, BookInMethod, BookInDate, BookInComments, BookInDamages, ClaimNumber, AuthorisationStatusID, WorkProviderID, EstimatorID, PolicyNumber, OtherReference, RepairerTypeID, InsuredAmount, ExcessAmount, DiscountPercentage, EstimatedRepairCost, RepairsAuthorisedID, Damages, DateOfAccident, RepairStartDate, RepairEndDate, EstimatedrepairDate, EstimatedRepairTime, ActualRepairTime, PartsOrdered, PartsOrderedDate, HireCarStartDate, HireCarEndDate, UnderWarranty, WarrantyExpires, PreAccidentCondition, SteeringCondition, BrakesPedalTravel, PleaceOfInspection, SeverityOfImpact, VehicleStatusOnInspection, DateOfVehicleInspection, TyreCondition, TreadDepthLHF, TreadDepthLHR, TreadDepthRHF, TreadDepthRHR, ShopID, CustomerID, CompanyName, CustomerName, CustomerSurname, Address, City, PostalCode, PhoneNumber, FaxNumber, CellNumber, SendViaCell ) " & _
"SELECT " & lngID & " As NewID, TrackID, TrackID_Main, BookedInBy, BookInMethod, BookInDate, BookInComments, BookInDamages, ClaimNumber, AuthorisationStatusID, WorkProviderID, EstimatorID, PolicyNumber, OtherReference, RepairerTypeID, InsuredAmount, ExcessAmount, DiscountPercentage, EstimatedRepairCost, RepairsAuthorisedID, Damages, DateOfAccident, RepairStartDate, RepairEndDate, EstimatedrepairDate, EstimatedRepairTime, ActualRepairTime, PartsOrdered, PartsOrderedDate, HireCarStartDate, HireCarEndDate, UnderWarranty, WarrantyExpires, PreAccidentCondition, SteeringCondition, BrakesPedalTravel, PleaceOfInspection, SeverityOfImpact, VehicleStatusOnInspection, DateOfVehicleInspection, TyreCondition, TreadDepthLHF, TreadDepthLHR, TreadDepthRHF, TreadDepthRHR, ShopID, CustomerID, CompanyName, CustomerName, CustomerSurname, Address, City, PostalCode, PhoneNumber, FaxNumber, CellNumber, SendViaCell " & _
"FROM [Quotation Details] WHERE TrackID = " & Me.TrackID & ";"
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
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
