Concatenated fields and writing to original table

[June7]

First of all, what is the 'main' table - [Change Request]? That's the only table provided.



Second, what data needs to be saved to 'main' table and into what fields?

Third, why do you open the popup form for editing a single record in [Change Request] table then run procedure that looks like it would loop through ALL records of the [AORB_EMail] table (also not provided) and send email for each record? I don't get the connection.

[Thompyt]

Change request is the main table. The data needed to be updated in the table would be the priority and hour fields. The email vba was gleaned off the internet and changed to meet what I thought I needed. I am no programmer & learning as I go. I could do without the loop. I don't really need it, and as it is it doesn't email all the records in the aorb_email qry anyways. If it would make an email from the record just updated or defined by the popup selection, I'd go with that. That was the next step for me, but leery about changing the vba.

[Thompyt]

June7, et al,
I appreciate the assistance you have done so far. I'm still fighting this one:

In my pop-up I cannot get the selected criteria to write to the correct record.

POPup: AORB_Email

CR_Numbers = CR_Number + (Sub_Number*.01)
CR_Number (ComoBox) (Unbound) Numbers before the decimal
Sub_Number (ComoBox) Numbers after the decimal
Priority (ComoBox) Flash, High, Med, Low
HRS (ComoBox) Selection: 8, 12, 24, 48, 72

Buttons: Cancel - Closes the Popup
Send - Saves the record and closes the Popup
Email_AORB Query Has three records filtered into it.

CR_Number
Sub_Number
Priority
HRS

I want to be able to use the CR_Number/Sub_Number to select the correct CR: June7 has provided me this part with a VBA filter. I want this part to be the select and not write into the record in EMail_AORB Query. The Priority & Hrs Fields I want to write to the selected row as designated by the CR_Number ComboBox. This then should be the record referenced in the email sent out when I select send. To this point it only saves to the first record in the EMail_AORB query, and the first record is the only one to show up in the email.

If the first record is the CR selected, then the correct CR Number, Priority and Hrs shows up in the email. The Priority and Hrs fields are filled in in the Email_AORB query.
If I select any other record, the first record CR number shows up in the email, but the priority and Hrs are blank. The Priority and Hrs fields are blank in in the Email_AORB query. The first row Priority and Hrs is not changed either.

I have slimmed down the extra code not needed in the email: (I hope at least correctly.) and is as below. I know this references the query and where to get the information, but where would it allow the selected CR_Number to be shown (vice the first record only)? I would be happy with the first record only, wher I knoew there would always be only one record, but there are times where there will be more than one.

Please remember I am a novice at this.

Private Sub Send_AORB_OOB_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, "Email_AORB"
On Error GoTo Error
Dim db As Database
Dim rstChange_Request As Recordset
Dim strSQL As String
Dim strSubject, strBody, strAddresses As String
Set db = CurrentDb()
strSQL = "SELECT * FROM AORB_EMail;"
Set rstChange_Request = db.OpenRecordset(strSQL, dbOpenDynaset)
strBody = "Action Officers," & vbCrLf & "This is a follow-on from today's ERB discussion on CR " & rstChange_Request("CR_Numbers") & ". If needed, please back-brief your O6 for SA, and let me know if there is any issues or concerns. The Change Request priority is " & rstChange_Request("Priority") & " with " & rstChange_Request("Hrs") & " hours until CR is automatically approved. Please provide your votes NLT " & rstChange_Request("Time") & ". Please call if you have any questions or issues." & vbCrLf & vbCrLf & vbCrLf
strBody = strBody & "Priority: " & Chr(9) & Chr(9) & Chr(9) & Chr(9) & rstChange_Request("Priority") & vbCrLf
strBody = strBody & "CR Number: " & Chr(9) & Chr(9) & Chr(9) & Chr(9) & rstChange_Request("CR_Numbers") & vbCrLf
strBody = strBody & "Date Issue Identified: " & Chr(9) & Chr(9) & Chr(9) & rstChange_Request("Dates") & vbCrLf & vbCrLf
strBody = strBody & "Change Requested: " & Chr(9) & rstChange_Request("Change Requested") & vbCrLf & vbCrLf
strBody = strBody & "Unit & Section: " & Chr(9) & Chr(9) & Chr(9) & rstChange_Request("Units") & vbCrLf
strBody = strBody & "MTOE Para & Bumper Number: " & Chr(9) & rstChange_Request("MTOE Paras") & vbCrLf & vbCrLf
strBody = strBody & "Rationale: " & Chr(9) & Chr(9) & rstChange_Request("Rationale") & vbCrLf & vbCrLf
strBody = strBody & "Notes: " & Chr(9) & Chr(9) & Chr(9) & rstChange_Request("Notes") & vbCrLf
strBody = strBody & "Action Items: " & Chr(9) & Chr(9) & rstChange_Request("Action_Items") & vbCrLf
strSubject = rstChange_Request("Priority") & " OOB AORB Change Request Number " & rstChange_Request("CR_Numbers") & " - " & rstChange_Request("Change Requested")
strAddresses = ""
DoCmd.SendObject acSendNoObject, , acFormatTXT, strAddresses, , , strSubject, strBody, True
Exit Sub

[June7]

I thought you wanted to send multiple emails because of the original looping code. And I see you still have a variable for strAddresses. So what do you want? Do you want the data for a single record sent to multiple addresses?

The popup form is allowing edit of record in [Change Request] table. I still don't understand what data is not being saved. It works for me after the edits already described.

If you want this code to send a single email with the data of the current record on the popup form, can simply set the popup form RecordSource to the AORB_Email query (without the static parameters) and the VBA doesn't even need to open a recordset, just reference the fields of the form RecordSource and set the email address argument of SendObject with an address.

Private Sub Send_AORB_OOB_Click()
On Error GoTo Error
Dim strSubject, strBody, strAddresses As String
DoCmd.RunCommand acCmdSaveRecord
With Me
If Not IsNull(.cbxNumber) And Not IsNull(.Priority) And Not IsNull(.Hours) Then
    strBody = "Action Officers," & vbCrLf & "This is a follow-on from today's 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 " & !Hours & " hours until CR is automatically approved.  " _
        & "Please provide your votes NLT " & !Time & ". Please call if you have any questions or issues." & vbCrLf & vbCrLf & vbCrLf
    strBody = strBody & "Priority: " & Chr(9) & Chr(9) & Chr(9) & Chr(9) & !Priority & vbCrLf
    strBody = strBody & "CR Number: " & Chr(9) & Chr(9) & Chr(9) & Chr(9) & !CR_Numbers & vbCrLf
    strBody = strBody & "Date Issue Identified: " & Chr(9) & Chr(9) & Chr(9) & !Dates & vbCrLf & vbCrLf
    strBody = strBody & "Change Requested: " & Chr(9) & ![Change Requested] & vbCrLf & vbCrLf
    strBody = strBody & "Unit & Section: " & Chr(9) & Chr(9) & Chr(9) & !UNITS & vbCrLf
    strBody = strBody & "MTOE Para & Bumper Number: " & Chr(9) & ![MTOE Paras] & vbCrLf & vbCrLf
    strBody = strBody & "Rationale: " & Chr(9) & Chr(9) & !Rationale & vbCrLf & vbCrLf
    strBody = strBody & "Notes: " & Chr(9) & Chr(9) & Chr(9) & !NOTES & vbCrLf
    strBody = strBody & "Action Items: " & Chr(9) & Chr(9) & !Action_Items & vbCrLf & vbCrLf & vbCrLf & vbCrLf
    strBody = strBody & "V/R" & vbCrLf & vbCrLf
    strBody = strBody & "Configuration Management" & vbCrLf
    strBody = strBody & "Brigade Modenerzation Command (BMC)" & vbCrLf
    strBody = strBody & "Network Integration Division (NID)" & vbCrLf
    strBody = strBody & "BLDG 2, Sheridan Road" & vbCrLf
    strBody = strBody & "Ft Bliss, TX 79916" & vbCrLf
    strBody = strBody & "Amanda Fallin" & Chr(9) & "(915)568-8442" & Chr(9) & "address" & vbCrLf
    strBody = strBody & "Kathy Gordon" & Chr(9) & "(915)569-5167" & Chr(9) & "address" & vbCrLf
    strSubject = !Priority & " OOB AORB Change Request Number " & !CR_Numbers & " - " & ![Change Requested]
    'If you want to send this same record data to multiple addresses, this is where the looping code is needed to build address string.
    'And this is what a recordset object will be needed for.
    Dim rstCR As Recordset
    Set rstCR = CurrentDb.OpenRecordset("SELECT [email address field] FROM [some table/query with email addresses] WHERE some criteria here;", dbOpenDynaset)
    While Not rstCR.EOF
        strAddresses = strAddresses & [email address field] & ","
        rstCR.MoveNext
    Wend
    strAddresses = Left(strAddresses, Len(strAddresses) - 1)
    DoCmd.SendObject acSendNoObject, , acFormatTXT, strAddresses, , , strSubject, strBody, True
End If
End With
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

Is this word correctly spelled: Modenerzation
Should it be: Modernization

[Thompyt]

June7,
Thanks for the coding. It is much appreciated. I have used your code with the exception of the naming convention and spelling error changes. Now I get a compile error "Method or Data member not found" on "IsNull(.cbxNumber)" when I run the VBA.

Do I need to dim .cbxNumber?
It looks like the data is not being saved in the table, could this be the issue? Since Not isNull(.cbxNumber) is actually null, then this would error out. I tried changing the popup coding to:
Popop AORB_Email from Change Requesttable to AORB_EMail Query with fields: (this lowered the number of colums in unbound combobox also.)
CR_Numbers, Priority and Hrs.

I did the same for the unbound combobox. I get the same error.

If it can be done without saving the fields to the table, then that would suffice also.

Thanks for the formatting of the strBody. I see what you did and actually understanbd what is happening.

I tried the following:

Private Sub cbxNumber_AfterUpdate()
Me.Filter = "CR_Numbers=" & Me.cbxNumber
Me.FilterOn = True
Set Me.cbxNumber = Numbers
End Sub

If Not IsNull(.Numbers) And Not IsNull(.Priority) And Not IsNull(Hrs) Then

And we are back to square 1

[June7]

.cbxNumber is the name of the combobox (see post 8). It is not a VBA variable and does not need to be Dimmed.

I made changes to your db described in posts 8 and 20 and it works. The input on the popup is saved to the record. The code references the combobox and the popup form RecordSource fields.

This code should send multiple emails with the same data - the current record of the popup.

Time is a reserved word. Should avoid reserved words in naming.

[Thompyt]

CRs.zip
June7
I have checked and rechecked the coding and I still get stuck at the (.cbxNumber). I even downloaded the db in Post 8 and used the coding in the POPUP Record Source (changed Hours to Hr), and the unbound ComboBox row source I changed the Bound column to 1. I change Time to DTG (Date Time Group) in AORB_EMail query.

I copy & pasted your EMail and Filter VBA into the coding and added DoCmd.Close acForm, "Email_AORB" after the oCmd.RunCommand acCmdSaveRecord.

[June7]

1. popup RecordSource:
SELECT [CR_Number]+([Sub Number]*0.01) AS CR_Numbers, [Change Request].*
FROM [Change Request]
ORDER BY [CR_Number]+([Sub Number]*0.01);

2. there is no code behind the combobox
You have a procedure for cbxNumber but you did not RENAME the combobox so it is not connected to the procedure. Rename the combobox and set the AfterUpdate event property. Or use the current combobox name and fix the code to have the same name, still set the AfterUpdate event property.

3. Do not close the form until the end of the procedure. The form must remain open for the fields to be available to the code.

4. Fix the SQL statement for the email Recordset

[Thompyt]

Wohooo!!!! Gotcha, I needed to change the name of the unbound to be the same as for the filter. Duh. Thanks June7, we can consider this thread closed!