All I did was change some naming conventions: Below is the original
Original Email_AORB Form
Code:
SELECT [CR_No]+([Sub_No]*0.01) AS CR_Numbers, Chr(9) & Nz([UNIT],"") & Chr(9) & Nz([Section],"") AS UNITS, Nz([MTOE_Para]," ") & Chr(9) & Nz([Bumper_Number],"") AS [MTOE Paras], [Change Request].[Change Requested], [Change Request].Rationale, [Change Request].NOTES, [Change Request].Action_Items, [Change Request].AO_Vote, [Change Request].O6_Vote, Format([Date_ID],"dddd"", ""mmm d yyyy") AS Dates, [Change Request].Priority, [Change Request].Hr, Format(Now()+([Hr]/24),"hhnn dddd"", ""mmm d yyyy") AS [Time], Format(Now()+([Hr]/24),"hhnn dddd"", ""mmm d yyyy") AS DTG
FROM [Change Request]
GROUP BY [CR_No]+([Sub_No]*0.01), Chr(9) & Nz([UNIT],"") & Chr(9) & Nz([Section],""), Nz([MTOE_Para]," ") & Chr(9) & Nz([Bumper_Number],""), [Change Request].[Change Requested], [Change Request].Rationale, [Change Request].NOTES, [Change Request].Action_Items, [Change Request].AO_Vote, [Change Request].O6_Vote, Format([Date_ID],"dddd"", ""mmm d yyyy"), [Change Request].Priority, [Change Request].Hr, Format(Now()+([Hr]/24),"hhnn dddd"", ""mmm d yyyy"), Format(Now()+([Hr]/24),"hhnn dddd"", ""mmm d yyyy"), [Change Request].Action_Complete
HAVING ((([Change Request].[Change Requested])<>"Do not delete") AND (([Change Request].O6_Vote) Is Null) AND (([Change Request].Action_Complete)=False));
Unbound Data Tab:
Code:
SELECT [CR_No]+([Sub_No]*0.01) AS CR_Numbers, Chr(9) & Nz([UNIT],"") & Chr(9) & Nz([Section],"") AS UNITS, Nz([MTOE_Para]," ") & Chr(9) & Nz([Bumper_Number],"") AS [MTOE Paras], [Change Request].[Change Requested], [Change Request].Rationale, [Change Request].NOTES, [Change Request].Action_Items, [Change Request].AO_Vote, [Change Request].O6_Vote, Format([Date_ID],"dddd"", ""mmm d yyyy") AS Dates, [Change Request].Priority, [Change Request].Hr, Format(Now()+([Hr]/24),"hhnn dddd"", ""mmm d yyyy") AS [Time], [Change Request].Unit, [Change Request].MTOE_Para, [Change Request].Bumper_Number, Format(Now()+([Hr]/24),"hhnn dddd"", ""mmm d yyyy") AS DTG
FROM [Change Request]
WHERE ((([Change Request].[Change Requested])<>"Do not delete") AND (([Change Request].O6_Vote) Is Null) AND (([Change Request].Action_Complete)=False));
Original VBA
Code:
Private Sub Cmnd_Cancel_Click()
DoCmd.Close acForm, "Email_AORB"
DoCmd.OpenForm "Start"
End Sub
Private Sub cbxNumber_AfterUpdate()
Me.Filter = "CR_Numbers=" & Me.cbxNumber
Me.FilterOn = True
End Sub
Private Sub Form_Load()
If IsNull(CR_Numbers) Then
MsgBox "There are no OOB CRs to send"
DoCmd.Close acForm, "Email_AORB"
DoCmd.OpenForm "Start"
End If
End Sub
Private Sub Send_AORB_OOB_Click()
On Error GoTo error
Dim strSubject As String
Dim strBody As String
Dim strAddresses As String
DoCmd.RunCommand acCmdSaveRecord
With Me
If Not IsNull(.cbxNumber) And Not IsNull(.Priority) And Not IsNull(.Hours) Then
strSubject = !Priority & " OOB AO Change Request Number " & !CR_Numbers & " - " & ![Change Requested] & " - " & Tod
strBody = "Action Officers," & vbCrLf & "This is a follow-on from today's AORB/ERB discussion on CR " _
& !CR_Numbers & ". If needed, please back-brief your O6 for SA, and let me know if there are any issues or concerns. " _
& "The Change Request priority is " & !Priority & " with " & !Hr & " hours until CR is automatically approved. " _
& "Please provide your votes NLT " & !DTG & ". Please call if you have any questions or issues." & vbCrLf & vbCrLf & vbCrLf _
& "Date Issue Identified: " & Chr(9) & Chr(9) & Chr(9) & !Dates & vbCrLf & vbCrLf & "Priority: " & Chr(9) & Chr(9) & Chr(9) & Chr(9) _
& !Priority & vbCrLf & "CR Number: " & Chr(9) & Chr(9) & Chr(9) & Chr(9) & !CR_Numbers & vbCrLf & "AO Recommendation: " & Chr(9) _
& Chr(9) & Chr(9) & !AO_Vote & vbCrLf & vbCrLf & "Change Requested: " & Chr(9) & ![Change Requested] & vbCrLf & vbCrLf _
& "Unit & Section: " & Chr(9) & Chr(9) & Chr(9) & !UNITS & vbCrLf & "MTOE Para & Bumper Number: " & Chr(9) & ![MTOE Paras] & vbCrLf & vbCrLf _
& "Rationale: " & Chr(9) & Chr(9) & !Rationale & vbCrLf & vbCrLf & "Notes: " & Chr(9) & Chr(9) & Chr(9) & !NOTES & vbCrLf _
& "Action Items: " & Chr(9) & Chr(9) & !Action_Items & SigBlock
DoCmd.Close acForm, "Email_AORB"
DoCmd.OpenForm "Start"
DoCmd.SendObject acSendNoObject, , acFormatTXT, strAddresses, , , strSubject, strBody, True
End If
End With
Exit Sub
Error_Handler_Exit:
Exit Sub
error:
Select Case Err.Number
Case 2501
Err.Clear
Resume Error_Handler_Exit
Case Else
MsgBox "Error No. " & Err.Number & vbCrLf & "Description: " & Err.Description, vbExclamation, "Database Error"
Err.Clear
Resume Error_Handler_Exit
End Select
Exit Sub
End Sub
In this Priority and HR are bound Control boxes and will save when it runs.