Блог

Это не мой код, но мне нужно его исправить. Я получаю сообщение об ошибке » Не удается обновить. База данных или объект доступны только для чтения «и выделяет». Редактировать». Из того, что я понимаю, одна из таблиц будет обновлена, если в другой таблице были внесены изменения. Хотя, похоже, он этого не делает. Я попытался поставить rsCH перед .Отредактируйте и rsOO, а также посмотрите, исправит ли это проблему, но это не так.

 Function DefineOrderChanges()
' this function defines the order changes for any order that is in the query OpenOrdersOnOrderImportFile
' it looks at 6 fields and captures the changes in the Changes file
' need to also develop an order change acknowledgement process.  Send out an email to each primary engineer and
' secondary engineer that is assigned to a project.  Unassigned jobs don't matter???
' there should be an attribute in the Changes table that says each change has been acknowledge done by some new form
' code to follow here.
     ' Detailer/Engineer
     ' Name vs CustName
     ' Description
     ' Order Qty vs OrderQyt
     ' Planned Start Date vs PlannedStartDate
     ' Req'd Release from Engr vs ReqReleaseFromEngr
Dim rsOO As DAO.Recordset ' open orders
Dim rsCH As DAO.Recordset ' order change history
Dim rcOO As Long ' record count in the open order recordset

Set rsOO = CurrentDb.OpenRecordset("OpenOrdersOnOrderImportFile")
Set rsCH = CurrentDb.OpenRecordset("OrderChanges")
If rsOO.RecordCount = 0 Then
    Set rsOO = Nothing
    Set rsCH = Nothing
    Exit Function
End If

With rsOO
    .MoveLast
    rcOO = .RecordCount
    .MoveFirst
    
    For i = 1 To rcOO
        
        If .Fields("OrdersRawData.Detailer/Engineer") <> .Fields("Orders.Detailer/Engineer") Then
            ' write a record to the change table
            rsCH.AddNew
            rsCH.Fields("Project") = .Fields("Project")
            rsCH.Fields("CustomizedItem") = .Fields("CustomizedItem")
            rsCH.Fields("DateAdded") = Now()
            rsCH.Fields("Field") = "Detailer/Engineer"
            rsCH.Fields("OldValue") = .Fields("Orders.Detailer/Engineer")
            rsCH.Fields("NewValue") = .Fields("OrdersRawData.Detailer/Engineer")
            rsCH.Fields("RequestedBy") = "BAAN"
            If IsNull(.Fields("DateAssigned")) Then rsCH.Fields("ChangeAck") = -1
            rsCH.Update
            ' now make the change to the existing order
            .Edit
            .Fields("Orders.Detailer/Engineer") = .Fields("OrdersRawData.Detailer/Engineer")
            .Update
            
        End If
        If .Fields("Name") <> .Fields("CustName") Then
            ' write a record to the change table
            rsCH.AddNew
            rsCH.Fields("Project") = .Fields("Project")
            rsCH.Fields("CustomizedItem") = .Fields("CustomizedItem")
            rsCH.Fields("DateAdded") = Now()
            rsCH.Fields("Field") = "CustName"
            rsCH.Fields("OldValue") = .Fields("CustName")
            rsCH.Fields("NewValue") = .Fields("Name")
            rsCH.Fields("RequestedBy") = "BAAN"
            If IsNull(.Fields("DateAssigned")) Then rsCH.Fields("ChangeAck") = -1
            rsCH.Update
            ' now make the change to the existing order
            .Edit
            .Fields("CustName") = .Fields("Name")
            .Update
        End If
        If .Fields("OrdersRawData.Description") <> .Fields("Orders.Description") Then
            ' write a record to the change table
            rsCH.AddNew
            rsCH.Fields("Project") = .Fields("Project")
            rsCH.Fields("CustomizedItem") = .Fields("CustomizedItem")
            rsCH.Fields("DateAdded") = Now()
            rsCH.Fields("Field") = "Description"
            rsCH.Fields("OldValue") = .Fields("Orders.Description")
            rsCH.Fields("NewValue") = .Fields("OrdersRawData.Description")
            rsCH.Fields("RequestedBy") = "BAAN"
            If IsNull(.Fields("DateAssigned")) Then rsCH.Fields("ChangeAck") = -1
            rsCH.Update
            ' now make the change to the existing order
            .Edit
            .Fields("Orders.Description") = .Fields("OrdersRawData.Description")
            .Update
        End If
        If .Fields("Order Qty") <> .Fields("OrderQty") Then
            ' write a record to the change table
            rsCH.AddNew
            rsCH.Fields("Project") = .Fields("Project")
            rsCH.Fields("CustomizedItem") = .Fields("CustomizedItem")
            rsCH.Fields("DateAdded") = Now()
            rsCH.Fields("Field") = "OrderQty"
            rsCH.Fields("OldValue") = .Fields("OrderQty")
            rsCH.Fields("NewValue") = .Fields("Order Qty")
            rsCH.Fields("RequestedBy") = "BAAN"
            If IsNull(.Fields("DateAssigned")) Then rsCH.Fields("ChangeAck") = -1
            rsCH.Update
            ' now make the change to the existing order
            .Edit
            .Fields("OrderQty") = .Fields("Order Qty")
            .Update
        End If
        If .Fields("Planned Start Date") <> .Fields("PlannedStartDate") Then
            ' write a record to the change table
            rsCH.AddNew
            rsCH.Fields("Project") = .Fields("Project")
            rsCH.Fields("CustomizedItem") = .Fields("CustomizedItem")
            rsCH.Fields("DateAdded") = Now()
            rsCH.Fields("Field") = "PlannedStartDate"
            rsCH.Fields("OldValue") = .Fields("PlannedStartDate")
            rsCH.Fields("NewValue") = .Fields("Planned Start Date")
            rsCH.Fields("RequestedBy") = "BAAN"
            If IsNull(.Fields("DateAssigned")) Then rsCH.Fields("ChangeAck") = -1
            rsCH.Update
            ' now make the change to the existing order
            .Edit
            .Fields("PlannedStartDate") = .Fields("Planned Start Date")
            .Update
        End If
        If .Fields("Req'd Release from Engr") <> .Fields("ReqReleaseFromEngr") Then
            ' write a record to the change table
            rsCH.AddNew
            rsCH.Fields("Project") = .Fields("Project")
            rsCH.Fields("CustomizedItem") = .Fields("CustomizedItem")
            rsCH.Fields("DateAdded") = Now()
            rsCH.Fields("Field") = "ReqReleaseFromEngr"
            rsCH.Fields("OldValue") = .Fields("ReqReleaseFromEngr")
            rsCH.Fields("NewValue") = .Fields("Req'd Release from Engr")
            rsCH.Fields("RequestedBy") = "BAAN"
            If IsNull(.Fields("DateAssigned")) Then rsCH.Fields("ChangeAck") = -1
            rsCH.Update
            ' now make the change to the existing order
            .Edit
            .Fields("ReqReleaseFromEngr") = .Fields("Req'd Release from Engr")
            .Update
        End If
        .MoveNext
    Next
End With

Set rsOO = Nothing
Set rsCH = Nothing

        
     
End Function