Good evening all! I have found myself in a jam that has me beating my head against the proverbial wall, and has led me down numerous research rabbit holes to no avail. Now my head proverbially hurts, and I can no longer see the light of day I've been so deep into these tunnels...so I decided to finally throw it out here in the hopes someone can help me refocus and move past this. I have searched the forums, but either no one has had this problem before or my search Boolean efforts are missing the mark...probably the latter.
Apologies up front for the somewhat lengthy post, but I wanted to provide as much information regarding the method to my madness up front in order to avoid wasting people's time by having an endless back-and-forth volley of questions. However, if you want to skip to the Reader's Digest version then go to the section "THE PROBLEM". Otherwise, here is the back story...
BACK STORY: I'm developing a database that, among other things, tracks personas which are assigned to specific operations. To this end, I have:
1) A form that I am working from named "frm115_ManageOpsAssignment". This form is bound to a table called "tbl23_SupportedOps", which manages all data points related to each individual registered operation record. This form allows the user to manage the information relating to the operation, including the assignment of a supporting persona. The user selects the desired operation via a combo box that pulls information from "tbl23_SupportedOps" and supplies that info to various text box controls on the form.
For the purpose of this post, let's assume I have an operation named BURNING VIKING and that is the operation that has been selected.
Relevant to my problem, this form contains three controls.
THE PROBLEM: Based on the code I included below, when the user selects the command button that ultimately fires the UpdateTable function, I want the code to update a record in a table not bound to this form. This update will add the operation name (as drawn from the hidden textbox control "txtBox_OpsName") and push it to the persona record's SupportedOpsList field only if that record's CommonName matches the name(s) contained within the current form's hidden textbox control "txtBox_HiddenPersona".
This works beautifully if there is only one persona name in the persona selected textbox. So, if I have BURNING VIKING open for editing and I select only Ragnar Lothbrok as a supporting persona, the name BURNING VIKING is successfully added to tbl11_PersonaTable in the SupportedOpsList field within the Ragnar Lothbrok persona record. Yeah!
However, if there are multiple names (each separated by a comma in the persona selected textbox), the event fires and presents no errors, but the operation name is not added to any of the desired persona records in that table. So, if both Ragnar Lothbrok and Lagertha Lothbrok are both selected as a supporting persona, neither record has the name BURNING VIKING added to its record.
I have at least come to the conclusion that it is the presence of commas or possibly multiple names that is throwing this off...but I can't figure out how to fix this. I can only assume that if the textbox control the WHERE statement is focusing contains "Ragnar Lothbrok, Lagertha Lothbrok" and there is no record's CommonName field that actually contains that exact string then that record doesn't exist, as far as the code is concerned, and so no updates are effected. If the field contains only one of the names, the update occurs and the operation name is added to that record's SupportedOpsList field. Other than that, no Access love for me.
Again, I apologize for the lengthy post, but if I've nonetheless managed to maintain anyone's interest thus far I'm really hoping I can get this problem licked. I am not necessarily married to this solution, so if there is something else I'm not considering that would achieve my desired effect, I'm all ears...
Thank you all!
~Eric
Apologies up front for the somewhat lengthy post, but I wanted to provide as much information regarding the method to my madness up front in order to avoid wasting people's time by having an endless back-and-forth volley of questions. However, if you want to skip to the Reader's Digest version then go to the section "THE PROBLEM". Otherwise, here is the back story...
BACK STORY: I'm developing a database that, among other things, tracks personas which are assigned to specific operations. To this end, I have:
1) A form that I am working from named "frm115_ManageOpsAssignment". This form is bound to a table called "tbl23_SupportedOps", which manages all data points related to each individual registered operation record. This form allows the user to manage the information relating to the operation, including the assignment of a supporting persona. The user selects the desired operation via a combo box that pulls information from "tbl23_SupportedOps" and supplies that info to various text box controls on the form.
For the purpose of this post, let's assume I have an operation named BURNING VIKING and that is the operation that has been selected.
Relevant to my problem, this form contains three controls.
- An UNBOUND list box ("lstBox_SupportingPersona") which presents a list of current personas registered in the database. This list is derived from a table called "tbl11_PersonaTable" and permits multiple selections. It primarily feeds a BOUND text box ("txtBox_HiddenPersona") that ultimately pushes the selected persona(s) into the "tbl23_SupportedOps" this form is bound to. Just background here, as this process does what it is supposed to do.
- An UNBOUND textbox, "txtBox_HiddenPersona". If multiple personas are selected, they are separated by commas. This is the beginning of my problem. The purpose of this unbound (and invisible on the form) control is to provide an otherwise uncommitted place from which a subsequent WHERE criteria action can use when deciding which persona record is to be updated with the currently selected operation.
- An UNBOUND text box control, "txtBox_OpsName", which is populated with the name of the currently selected operation. Again, this is an invisible control where the subsequent UPDATE action will draw the operation name from when updating the persona record.
- CommonName contains the name of the persona in readable form. (Personas are mainly tracked by a unique numeric ID that is separate from the PK, but the record also has a field that contains a readable name associated to that ID. e.g. PersID = 19-0001, CommonName = Ragnar Lothbrok.)(Yeah, in case you hadn't surmised...I'm a History Channel Vikings fan )
- SupportedOpsList contains the name of any operation this persona has been assigned to. My plan is to be able to have multiple operation names maintained in this field, if the persona has been assigned to more than one.. So in order to pre-address any questions about why I am looking to populate this field with multiple entries, it is because this information will ultimately feed a separate "quick look" form that allows a user to quickly ascertain all the operations any given persona is supporting. It is not used to drive any other queries or relationships...just show the information for "one stop viewing."
THE PROBLEM: Based on the code I included below, when the user selects the command button that ultimately fires the UpdateTable function, I want the code to update a record in a table not bound to this form. This update will add the operation name (as drawn from the hidden textbox control "txtBox_OpsName") and push it to the persona record's SupportedOpsList field only if that record's CommonName matches the name(s) contained within the current form's hidden textbox control "txtBox_HiddenPersona".
This works beautifully if there is only one persona name in the persona selected textbox. So, if I have BURNING VIKING open for editing and I select only Ragnar Lothbrok as a supporting persona, the name BURNING VIKING is successfully added to tbl11_PersonaTable in the SupportedOpsList field within the Ragnar Lothbrok persona record. Yeah!
However, if there are multiple names (each separated by a comma in the persona selected textbox), the event fires and presents no errors, but the operation name is not added to any of the desired persona records in that table. So, if both Ragnar Lothbrok and Lagertha Lothbrok are both selected as a supporting persona, neither record has the name BURNING VIKING added to its record.
I have at least come to the conclusion that it is the presence of commas or possibly multiple names that is throwing this off...but I can't figure out how to fix this. I can only assume that if the textbox control the WHERE statement is focusing contains "Ragnar Lothbrok, Lagertha Lothbrok" and there is no record's CommonName field that actually contains that exact string then that record doesn't exist, as far as the code is concerned, and so no updates are effected. If the field contains only one of the names, the update occurs and the operation name is added to that record's SupportedOpsList field. Other than that, no Access love for me.
Again, I apologize for the lengthy post, but if I've nonetheless managed to maintain anyone's interest thus far I'm really hoping I can get this problem licked. I am not necessarily married to this solution, so if there is something else I'm not considering that would achieve my desired effect, I'm all ears...
Thank you all!
~Eric
Code:
Private Function UpdateTable()
' Add Operation Name(s) to the Persona's record if that Persona Name appears in the
' textbox "txtBox_HiddenPersona"
' Since we are trying to save data to a table that this form is
' NOT bound to, declare the database and recordset
Dim db1 As DAO.Database
Dim rst1 As DAO.Recordset
' Declare string variables to help reduce code length
Dim strOpsName As String
Dim strCommonName As String
Dim insertSQL As String
' Identify what the strings are to represent from the form
strOpsName = txtBox_OpsName
strCommonName = txtBox_HiddenPersona.Value
' Assign references
Set db1 = CurrentDb
Set rst1 = db1.OpenRecordset("tbl11_PersonaTable", dbOpenDynaset)
' The SQL statement that will actually update the referenced
' table and field with required data from this form
insertSQL = "UPDATE tbl11_PersonaTable SET SupportedOpsList = '" & strOpsName & "'" & _
"WHERE CommonName = '" & strCommonName & "'"
db1.Execute insertSQL
' Clean everything up
rst1.Close
db1.Close
Set rst1 = Nothing
Set db1 = Nothing
' Save the record updates made to the table
DoCmd.RunCommand acCmdSaveRecord
' Requery (update the table) to reflect any changes made
Me.Requery
Me.Recordset.MoveLast
' Open the next form
DoCmd.OpenForm "frm114_OperationAssignment"
' Close this form
DoCmd.Close acForm, Me.Name
End Function