Code:
Private Sub Form_AfterUpdate()
On Error GoTo ErrHandler
If tSave <> 1 Then Exit Sub
Dim db As DAO.Database
Set db = CurrentDb
DoCmd.SetWarnings False
Dim strSQL, strSQLa, strSQLb, strSQLc As String
'=====================================================
' UPDATE tblRequests STATUS TO "Pending Request"
'=====================================================
strSQL = "Update tblRequests Set tblRequests.[cboEstimateStatus] = 'Pending Request' where tblRequests.[txtProjectNumber] = '" & Me!txtProjectNumber & "'"
Debug.Print strSQL
DoCmd.RunSQL strSQL
'=====================================================
' UPDATE tblMultipleRequests STATUS TO "Pending Request"
'=====================================================
strSQLa = "Update tblMultipleRequests Set tblMultipleRequests.[cboEstimateStatus] = 'Pending Request' where tblMultipleRequests.[txtProjectNumber] = '" & Me!txtProjectNumber & "'"
Debug.Print strSQLa
DoCmd.RunSQL strSQLa
'=====================================================
' UPDATE tblMultipleRequests FIELDS = tblRequests FIELDS
'=====================================================
strSQLb = _
"UPDATE tblMultipleRequests Set " & _
"tblMultipleRequests.txtTaskCode = [tblRequests].[txtTaskCode], tblMultipleRequests.txtQPNumber = [tblRequests].[txtQPNumber], tblMultipleRequests.cboEstimatingAction = [tblRequests].[cboEstimatingAction], tblMultipleRequests.cboEstimatePurpose = [tblRequests].[cboEstimatePurpose], " & _
"tblMultipleRequests.cboEstimateGrade = [tblRequests].[cboEstimateGrade], tblMultipleRequests.txtStatusNotes = [tblRequests].[txtStatusNotes], tblMultipleRequests.dtRequestDate = [tblRequests].[dtRequestDate], tblMultipleRequests.dtISD = [tblRequests].[dtISD], " & _
"tblMultipleRequests.dtRequestedCompletion = [tblRequests].[dtRequestedCompletion], tblMultipleRequests.RequestByPerson = [tblRequests].[RequestByPerson], tblMultipleRequests.cboRequestByOrganization = [tblRequests].[cboRequestByOrganization], tblMultipleRequests.txtReqOrg = [tblRequests].[txtReqOrg], " & _
"tblMultipleRequests.cboProjectDesignPhase = [tblRequests].[cboProjectDesignPhase], tblMultipleRequests.cboVoltageClass = [tblRequests].[cboVoltageClass], tblMultipleRequests.txtSLOther = [tblRequests].[txtSLOther], tblMultipleRequests.txtScopeDescription = [tblRequests].[txtScopeDescription], " & _
"tblMultipleRequests.txtPreviousEstimate = [tblRequests].[txtPreviousEstimate], tblMultipleRequests.txtNotes = [tblRequests].[txtNotes], tblMultipleRequests.txtProjectDeliverablesFolder = [tblRequests].[txtProjectDeliverablesFolder], tblMultipleRequests.IsProgram = [tblRequests].[IsProgram], " & _
"tblMultipleRequests.IsRelease = [tblRequests].[IsRelease], tblMultipleRequests.txtProgramEstimate = [tblRequests].[txtProgramEstimate], tblMultipleRequests.cboElectConst = [tblRequests].[cboElectConst], tblMultipleRequests.cboCivilConst = [tblRequests].[cboCivilConst], " & _
"tblMultipleRequests.txtExecutePlnNotes = [tblRequests].[txtExecutePlnNotes], tblMultipleRequests.cboSiting = [tblRequests].[cboSiting], tblMultipleRequests.IsSiting = [tblRequests].[IsSiting], tblMultipleRequests.txtSiting = [tblRequests].[txtSiting], " & _
"tblMultipleRequests.IsSCS = [tblRequests].[IsSCS], tblMultipleRequests.cboSCS = [tblRequests].[cboSCS], tblMultipleRequests.txtSCS = [tblRequests].[txtSCS], tblMultipleRequests.bFinancialConstraints = [tblRequests].[bFinancialConstraints], tblMultipleRequests.txtFinancialConstraint = [tblRequests].[txtFinancialConstraint], " & _
"tblMultipleRequests.bConstructabilityReview = [tblRequests].[bConstructabilityReview], tblMultipleRequests.bExternalCustomer = [tblRequests].[bExternalCustomer], tblMultipleRequests.txtExternalCustomer = [tblRequests].[txtExternalCustomer], tblMultipleRequests.bProjectDeliverables = [tblRequests].[bProjectDeliverables], " & _
"tblMultipleRequests.txtCCEMails = [tblRequests].[txtCCEMails], tblMultipleRequests.bMultipleEstimates = [tblRequests].[bMultipleEstimates], tblMultipleRequests.bEnvironmentalReceived = [tblRequests].[bEnvironmentalReceived], tblMultipleRequests.bPriors = [tblRequests].[bPriors], " & _
"tblMultipleRequests.Priors = [tblRequests].[Priors], tblMultipleRequests.PriorsWBS1 = [tblRequests].[PriorsWBS1], tblMultipleRequests.PriorsWBS2 = [tblRequests].[PriorsWBS2], tblMultipleRequests.PriorsWBS3 = [tblRequests].[PriorsWBS3], tblMultipleRequests.PriorsWBS4 = [tblRequests].[PriorsWBS4], tblMultipleRequests.PriorsWBS5 = [tblRequests].[PriorsWBS5], " & _
"tblMultipleRequests.PriorsWBS6 = [tblRequests].[PriorsWBS6], tblMultipleRequests.PriorsWBS7 = [tblRequests].[PriorsWBS7], tblMultipleRequests.PriorsWBS8 = [tblRequests].[PriorsWBS8], tblMultipleRequests.PriorsWBS9 = [tblRequests].[PriorsWBS9], tblMultipleRequests.PriorsWBS10 = [tblRequests].[PriorsWBS10], tblMultipleRequests.PriorsWBS11 = [tblRequests].[PriorsWBS11], " & _
"tblMultipleRequests.PriorsWBS12 = [tblRequests].[PriorsWBS12], tblMultipleRequests.PriorsWBS13 = [tblRequests].[PriorsWBS13], tblMultipleRequests.PriorsWBS14 = [tblRequests].[PriorsWBS14], tblMultipleRequests.PriorsWBS15 = [tblRequests].[PriorsWBS15], " & _
"tblMultipleRequests.PriorsWBS16 = [tblRequests].[PriorsWBS16] WHERE tblMultipleRequests.[txtProjectNumber] = '" & Me!txtProjectNumber & "'"
Debug.Print strSQLb
DoCmd.RunSQL strSQLb
'=====================================================
' APPEND tblMultipleRequests FIELDS TO tblRequests FIELDS
'=====================================================
strSQLc = _
"INSERT INTO tblRequests(txtTaskCode, txtQPNumber, cboEstimatingAction, cboEstimatePurpose, cboEstimateGrade, txtStatusNotes, dtRequestDate, dtISD, dtRequestedCompletion, RequestByPerson, cboRequestByOrganization, txtReqOrg, cboProjectDesignPhase, cboVoltageClass, txtSLOther, txtScopeDescription, txtPreviousEstimate, " & _
"txtNotes, txtProjectDeliverablesFolder, IsProgram, IsRelease, txtProgramEstimate, cboElectConst, cboCivilConst, txtExecutePlnNotes, cboSiting, IsSiting, txtSiting, IsSCS, cboSCS, txtSCS, bFinancialConstraints, txtFinancialConstraint, bConstructabilityReview, bExternalCustomer, txtExternalCustomer, bProjectDeliverables, txtCCEMails, " & _
"bMultipleEstimates, bEnvironmentalReceived, bPriors, Priors, PriorsWBS1, PriorsWBS2, PriorsWBS3, PriorsWBS4, PriorsWBS5, PriorsWBS6, PriorsWBS7, PriorsWBS8, PriorsWBS9, PriorsWBS10, PriorsWBS11, PriorsWBS12, PriorsWBS13, PriorsWBS14, PriorsWBS15, PriorsWBS16)" & _
"SELECT tblMultipleRequests.txtTaskCode, tblMultipleRequests.txtQPNumber, tblMultipleRequests.cboEstimatingAction, tblMultipleRequests.cboEstimatePurpose, tblMultipleRequests.cboEstimateGrade, tblMultipleRequests.txtStatusNotes, tblMultipleRequests.dtRequestDate, tblMultipleRequests.dtISD, tblMultipleRequests.dtRequestedCompletion, " & _
"tblMultipleRequests.RequestByPerson, tblMultipleRequests.cboRequestByOrganization, tblMultipleRequests.txtReqOrg, tblMultipleRequests.cboProjectDesignPhase, tblMultipleRequests.cboVoltageClass, tblMultipleRequests.txtSLOther, tblMultipleRequests.txtScopeDescription, tblMultipleRequests.txtPreviousEstimate, tblMultipleRequests.txtNotes, " & _
"tblMultipleRequests.txtProjectDeliverablesFolder, tblMultipleRequests.IsProgram, tblMultipleRequests.IsRelease, tblMultipleRequests.txtProgramEstimate, tblMultipleRequests.cboElectConst, tblMultipleRequests.cboCivilConst, tblMultipleRequests.txtExecutePlnNotes, tblMultipleRequests.cboSiting, tblMultipleRequests.IsSiting, tblMultipleRequests.txtSiting, " & _
"tblMultipleRequests.IsSCS, tblMultipleRequests.cboSCS, tblMultipleRequests.txtSCS, tblMultipleRequests.bFinancialConstraints, tblMultipleRequests.txtFinancialConstraint, tblMultipleRequests.bConstructabilityReview, tblMultipleRequests.bExternalCustomer, tblMultipleRequests.txtExternalCustomer, tblMultipleRequests.bProjectDeliverables, tblMultipleRequests.txtCCEMails, " & _
"tblMultipleRequests.bMultipleEstimates, tblMultipleRequests.bEnvironmentalReceived, tblMultipleRequests.bPriors, tblMultipleRequests.Priors, tblMultipleRequests.PriorsWBS1, tblMultipleRequests.PriorsWBS2, tblMultipleRequests.PriorsWBS3, tblMultipleRequests.PriorsWBS4, tblMultipleRequests.PriorsWBS5, tblMultipleRequests.PriorsWBS6, tblMultipleRequests.PriorsWBS7, " & _
"tblMultipleRequests.PriorsWBS8, tblMultipleRequests.PriorsWBS9, tblMultipleRequests.PriorsWBS10, tblMultipleRequests.PriorsWBS11, tblMultipleRequests.PriorsWBS12, tblMultipleRequests.PriorsWBS13, tblMultipleRequests.PriorsWBS14, tblMultipleRequests.PriorsWBS15, tblMultipleRequests.PriorsWBS16 " & _
"WHERE tblMultipleRequests.[txtProjectNumber] = '" & Me!txtProjectNumber & "'"
Debug.Print strSQLc
DoCmd.RunSQL strSQLc
DoCmd.SetWarnings True
' db.Execute "Delete * From tblMultipleRequests", dbFailOnError
' DoCmd.Close acForm, "frmRequests"
' DoCmd.OpenForm "NavigationForm"
'Me.Requery
' Me.KeyPreview = False
' Me.dtRequestDate = Format(Now, "mm/dd/yyyy")
' Me.RequestByPerson.Value = ResolveCurrentUserName
ExitHandler:
'DoCmd.SetWarnings True
tSave = 0
Set db = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, , "Error #" & Err.Number
Resume ExitHandler
End Sub