Concatenated fields and writing to original table

[Thompyt]

I concatenated 2 fields CR_Numbers: = CR_Number & [Sub Number] to display 12.01: from 12 and 1. I am now wanting to lookup the CR_Numbers to edit or add information into other fields.

I want to use a pop up form to edit/add data without having the form add an entire row to the table. How do I reference the Table ID so that it will go to the equivalent row to add the data in?

Main Table = Change Request
Email Table = AORB_Email
Fields in Form!AORB_EMail: Change Request (CR_Numbers), Priority (Pri_Name), Hours (Hour)



I plan on using the Priority and Hours in the email to set some dates and expiration times as well as in the subject of the email. I have figured out the email setup, and if I manually fill in the Priority and Hours fields everything works as it should.

[June7]

Why do you need to save this concatenated value? It can be calculated whenever needed.

[Thompyt]

I don't need to save it. I have it for the users view. I don't want to save the concatented value. I want to save the Priority & hour calue in the same record row as what the concatenated value derived from.

I can have multiple values for the CR_Number, and use the [Sub Number] field to differentiate. I know it is a wierd way of doing this, but that is how the users developed thier numbering system.

[June7]

Don't really understand the table structure (field names) for each table.

You want users to edit data on the popup form?

If you just want to save/edit data and user input is not needed, can be done without opening a popup form with an UPDATE or INSERT sql action.

[Thompyt]

I have to set up the priority of the email and the hours the user decides for that priority. This fills in some statements for the email automatically. The pop-up sets those parameters. The email is not an everytine use for the database, but on certain occasions where certain criteria are met.

It is a GUI for the user to set that criteria. It is not on any other form. Thus I need to add data in 2 fields for that specific record selected by the user.

[June7]

I still don't fully understand the issue. Open bound form filtered to the desired record.

DoCmd.OpenForm "form name" , , , "some field='" & Me.textbox & "'"

The WHERE condition string can be several parameters or concatenated fields and criteria.

[Thompyt]

June

I gather I am not explaining it all too well. Attached is the Db I am working on.

CRs.zip

Open the AORB EMail form and try to input the two latter fields into the Change Request Table in the correct record row. This is all I am trying to do. I don't want to rewite the the CR_Number, but only use it as a reference. Since this db is a learning experience for me, I expect you'l see a lot of odd ways of doing the dB.

Thanks

[June7]

You need to open the popup filtered to required record or else filter it after opening. For the latter case, the CR_Number combobox must be UNBOUND to use as criteria input. Then use the input to apply filter.

I don't see need for the combobox RowSource to be a GROUP BY query. There are 8 fields in the RowSource but the ColumnCount is set for 1 column and no ColumnWidths. Really only need one column.

Change the popup RecordSource to:
SELECT [CR_Number]+([Sub Number]*0.01) AS CR_Numbers, Priority, [Hour] FROM [Change Request];

Change combobox BoundColumn to 1, Name to cbxNumber, and Row Source to:
SELECT [CR_Number]+([Sub Number]*0.01) AS CR_Numbers FROM [Change Request] WHERE [Change Requested]<>"Do not delete" AND [AO_Recommendation_-_Vote] Is Null AND [O6_Recommendation_-_Vote] Is Null AND Action_Complete=False;

Combobox code:
Private Sub cbxNumber_AfterUpdate()
Me.Filter = "CR_Numbers=" & Me.cbxNumber
Me.FilterOn = True
End Sub

BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Also, Hour is a poor field name. Hour is an intrinsic function. It is not listed as a reserved word but I suggest not using as a name anyway.

[Thompyt]

Thanks June7,
I'll do some renaming. I did try your codes and it will work for the most part. But only for the first record in the CR_Numbers combobox. You can see the other numbers when you select the drop down arrow, but cannot select them. When selecting you get a warning at the bottom of Access in the status bar "Field 'CR_Numbers' is based on an expression cannot be edited".

[June7]

Did you make the combobox UNBOUND? Note the first line in my previous post.

[Thompyt]

I've got it running to a point. I can select the CR_Numbers but it does not update to the Main table as designed. What I am doing wrong here is that I still have hair on my head...

I did not have the combobox unbound at first. After changing to unbound, I could "Select" the record, but it doesn't update the Main Table.

I put this as the first item on the VBA code for the form itself (I think that was your intention:
Private Sub cbxNumber_AfterUpdate()
Me.Filter = "CR_Numbers=" & Me.cbxNumber
Me.FilterOn = True
End Sub

I had the multiple columns in the Record source so I could filter the number of records. I can see where the same can be accomplished in the row source. Why does the code only update the first record?

[June7]

That code is not updating any record. It is just filtering the form recordset. Then user can input values into Priority and Hour comboboxes. This will edit the one record.

Record entry/edit is committed to table when:

1. form closes

2. move to another record

3. run code

Before data entered by users is available for other processes, must commit the record. So in the code for the Send button, first save record.

DoCmd.RunCommand acCmdSaveRecord

[Thompyt]

I put in the do.cmd in the following manner:

Private Sub Send_AORB_OOB_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, "Email_AORB"
On Error GoTo Error
Dim db As Database.......

Prior to the start of the email VBA code.

It still doesn't work in saving the fields to the appropriate record. Only the first one.
Could it have something to do with the rest of the code? Doubtful, but there is an off chance.

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)
If Not rstChange_Request.EOF Then
strBody = "Action Officers," & vbCrLf & "This is a follow-on from today's ERB discussion on CR " & rstChange_Request("CR_Numbers") & ". 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 & vbCrLf & vbCrLf & vbCrLf
strBody = strBody & "V/R" & vbCrLf
rstChange_Request.MoveFirst
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
Do While Not rstChange_Request.EOF
rstChange_Request.MoveNext
Loop
End If
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

[June7]

This line:

strSQL = "SELECT * FROM AORB_EMail;"

Is opening an unfiltered recordset.

If you want the code to do something with each record then move the code that is currently between the If Then End If into the Do While Loop.

However, nothing in this procedure is saving anything to any record.

[Thompyt]

I guessed right about the VBA code as not saving anything to a record. Even with all the coding you gave me doesn't save anything to a record in the main table. I think the issue is where I don't see anything that would directly correlate to the main table to save. Since the record we are looking at is concatenated it doesn't know what to use as a reference since CR_Number can be duplicate numbers in the Main Table. In this I think the only way to reference the concatenated field is by using the CR_ID number. Although I don't know how to reference a field to know what one to update. Do I put in the CR_ID field in the combobox row source?