UPDATE statement using multiple, comma separated value, as criteria (1 Viewer)

Big E

Diamond Supporter
Local time
Today, 04:59
Joined
Jun 28, 2019
Messages
12
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.

  1. 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.
  2. 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.
  3. 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.
2) A table named "tbl11_PersonaTable". This table contains one record for each persona that has been created. This table has many fields containing various data points relating to each persona record, but the two that are relevant for my current problem are CommonName and SupportedOpsList.

  • 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! :D



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. :mad:



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
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:59
Joined
Feb 19, 2013
Messages
16,553
a lot to take in - and thought I understood what was required until I got to the code

can you highlight the bit of code that does this

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".

it is also not clear to me why you are are opening a recordset, then closing it again without apparently doing anything with it.

edit: on further digging, I suspect this line

"WHERE CommonName = '" & strCommonName & "'"

should be

"WHERE CommonName IN (" & strCommonName & ")"

but common name will need to look like

"'Ragnar Lothbrok', 'Lagertha Lothbrok'"
 

Micron

AWF VIP
Local time
Today, 04:59
Joined
Oct 20, 2018
Messages
3,476
Agree with the prior post.

Suggest you forget the textbox and instead, loop through the listbox selected items, delimiting as you go (e.g. Micron becomes 'Micron',) then remove the trailing comma that you would otherwise have and then construct your sql using IN operator as suggested, OR
loop through and write to table 1 by 1 - assuming these are individual records. I too got lost in the explanation, so if you are appending csv data in one field, I missed the point and the IN thing is of no use.
 

Big E

Diamond Supporter
Local time
Today, 04:59
Joined
Jun 28, 2019
Messages
12
Thanks for sticking with my verbose post, CJ. I appreciate that.


To clarify the bit of code you would like highlighted:



If only one persona name is selected from the list box, let's say "Ragnar Lothbrok", then that name is captured in the form's txtBox_HiddenPersona. From there, the code determines if that name is present in any of the records in the table tbl11_PersonaTable in the CommonName field (this table is not bound to this particular form). If it is, then the operation name (e.g. BURNING VIKING, or whatever operation name is actually present in the control txtBox_OpsName) is "pushed" (via the UPDATE) to the record where that criteria is true. So, in the record maintained in the table tbl11_PersonaTable that contains "Ragnar Lothbrok" in the CommonName field, the operation name BURNING VIKING will be added to that persona's SupportedOpsList field. None of the other persona records in that table would be updated to include the operation BURNING VIKING. This part works great.


It is when I have more than one persona name in the form's txtBox_HiddenPersona that things stop working. If the user selected two names (e.g. "Ragnar" and "Lagertha") from the list box, they then both appear in txtBox_HiddenPersona as "Ragnar Lothbrok, Lagertha Lothbrok" (multiple names separated by a comma). At this point, I'm assuming the code can't find a record in the table with that particular string (in other words, no CommonName fields contain the exact string "Ragnar Lothbrok, Lagertha Lothbrok" even though they both exist as separate records in that table) to match against and therefore no records get the operation name updated to them.


As far as why I'm opening the recordset, well I'm certainly no VBA guru :eek:. But, because the table's record I am trying to update with the operation name is not bound to this form (the form is bound to a different table and doing something else entirely), I thought I had to open it first in order to update with the operation name. If I'm wrong here, well...it wouldn't be the first time. That said, I do now realize I should have put the SaveRecord command BEFORE I close that recordset. That has been changed.


Lastly, I changed the WHERE to mimic what you suggested (WHERE CommonName IN(" & strCommonName & ")" and I receive run-time error '3075', Syntax error (missing operator) in query expression.


Thanks again, CJ, for taking the time to look at this.


~Eric
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:59
Joined
Oct 29, 2018
Messages
21,357
Hi. It would also be nice to see the code for populating txtBox_HiddenPersona, if any. Cheers!
 

Big E

Diamond Supporter
Local time
Today, 04:59
Joined
Jun 28, 2019
Messages
12
DB, thank you also for taking a look. As requested...



Code:
'**************** B E G I N   C O D E ****************
Private Sub lstBox_SupportingPersona_Click()
' List box selection that populates the Selected Persona text box

    Dim persStr As String
    Dim persVar As Variant
    Dim persItems As Control
    Dim persList As String
    
    Set persItems = Me.lstBox_SupportingPersona
    
    With Me.lstBox_SupportingPersona
        For Each persVar In persItems.ItemsSelected
            If persStr > "" Then
                persStr = persStr & ", " & persItems.ItemData(persVar)
            Else
                persStr = persItems.ItemData(persVar)
            End If
          
        Next persVar
        
        Me.txtBox_PersonaSelected = persStr
        
    End With
    
    Me.txtBox_HiddenPersona = Me.txtBox_PersonaSelected
            
End Sub
'****************** E N D   C O D E ******************
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:59
Joined
Oct 29, 2018
Messages
21,357
DB, thank you also for taking a look. As requested...
Hi. Thanks. So, your code works for single selections because your WHERE clause adds the single quote delimiters. If you want to allow for multiple criteria selections, you'll have to add the delimiters in this code and remove it from your WHERE clause. Also, you will have to follow CJ's suggestion of changing it from an equality comparison to using the IN() clause instead.
 

Big E

Diamond Supporter
Local time
Today, 04:59
Joined
Jun 28, 2019
Messages
12
Thank you very much for looking at this (verbose) post ;-)


To answer your question (which I did incorrectly earlier so it never showed up):


"...push it to the persona record's SupportedOpsList field..." means that I only want the operation name to be added to the to the SupportedOpsList field in the persona's record (which is maintained in a table that is not bound to this form) only if the persona's name CommonName matches what is currently in the form's textbox control txtBox_HiddenPersona (which would be whatever the user selected). If there is no match, then do not add anything to that persona's record.


I open the recordset because the information I am trying to update is not contained within the table that the current form is bound to. I (possibly incorrectly) assumed that if you were sending data to a table that is not bound to the form you are working on, you have to open that recordset.


I will try your recommendations re: using the IN clause. Will advise how it works out.
 

Big E

Diamond Supporter
Local time
Today, 04:59
Joined
Jun 28, 2019
Messages
12
I just wanted to offer a big "thank you" to CJ, DBguy and Micron. While doing the IN clause has generated a '3075' error (Missing Operator in query), all of your inputs have helped to at least narrow things down a bit and I'm sure I can power through this new issue. Sometimes a bit of focus is all I need :) I am also going to try using Micron's suggestions re: looping through the list box selected items.


Unfortunately, it is now time for me to start being domestically responsible and get the night's grilling started. So, assuming the Boss does not have an unrealistic "Man Task" list for the remainder of the week, I will work on these suggestions over the weekend.


Thank you all again!


~Eric
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:59
Joined
Oct 29, 2018
Messages
21,357
I just wanted to offer a big "thank you" to CJ, DBguy and Micron. While doing the IN clause has generated a '3075' error (Missing Operator in query), all of your inputs have helped to at least narrow things down a bit and I'm sure I can power through this new issue. Sometimes a bit of focus is all I need :) I am also going to try using Micron's suggestions re: looping through the list box selected items.


Unfortunately, it is now time for me to start being domestically responsible and get the night's grilling started. So, assuming the Boss does not have an unrealistic "Man Task" list for the remainder of the week, I will work on these suggestions over the weekend.


Thank you all again!


~Eric
Hi Eric. Happy 4th of July! Enjoy the fireworks!
 

Big E

Diamond Supporter
Local time
Today, 04:59
Joined
Jun 28, 2019
Messages
12
WAIT! Hold the presses! It worked!! Ok, so I did two things wrong. First, I fat fingered the string following the IN clause, which generated the '3075' error. Second, while trying to implement CJ's recommendation that the CommonName would need to look like "'Ragnar Lothbrok', 'Lagertha Lothbrok'" I had to play around but eventually got it right (I told you I'm not an Access guru...but hanging out with the likes of you all I'll get there eventually!).


Asking myself why DBguy would want to see the code that populates the txtBox_HiddenPersona textbox got me to playing with that for a moment. Once I made the changes, the whole thing works perfectly now!!!! Below is the "new and improved" code that accomplishes that population. I can now add the operation name to any persona record whose CommonName appears in the txtBox_HiddenPersona control, no matter how many there are. Whew!


Now, to figure out how to allow multiple "operation names" to appear in each record's SupportedOpsList.


How do I mark this question as "SOLVED"?


Thank you again. You all are incredibly helpful.



~Eric


Code:
'**************** B E G I N   C O D E ****************
Private Sub lstBox_SupportingPersona_Click()
' List box selection that populates the Selected Persona text box

    Dim persStr As String
    Dim persVar As Variant
    Dim persItems As Control
    Dim persList As String
    
    Set persItems = Me.lstBox_SupportingPersona
    
    With Me.lstBox_SupportingPersona
        For Each persVar In persItems.ItemsSelected
            If persStr > "" Then
                [COLOR=Red]persStr = "'" & persStr & "'" & "," & Space(1) & "'" & persItems.ItemData(persVar) & "'"[/COLOR]
            Else
                persStr = persItems.ItemData(persVar)
            End If
          
        Next persVar
        
        Me.txtBox_PersonaSelected = persStr
        
    End With
    
    Me.txtBox_HiddenPersona = Me.txtBox_PersonaSelected
            
End Sub
'****************** E N D   C O D E ******************
 

Big E

Diamond Supporter
Local time
Today, 04:59
Joined
Jun 28, 2019
Messages
12
Oh, thanks to the solving of this vexing problem, there will most certainly be much fireworks...and BEER!!!


You have a great 4th as well, DBguy!!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:59
Joined
Oct 29, 2018
Messages
21,357
Oh, thanks to the solving of this vexing problem, there will most certainly be much fireworks...and BEER!!!


You have a great 4th as well, DBguy!!
Hi. Glad to hear you got it sorted out. Good luck with the rest of your project.
 

isladogs

MVP / VIP
Local time
Today, 08:59
Joined
Jan 14, 2017
Messages
18,186
Post #4 was moderated. Now approved though the thread has moved on since then...
I've marked the thread as solved. For info, its in the Thread Tools dropdown menu
Anyway, posting this to trigger email notifications.
 

Micron

AWF VIP
Local time
Today, 04:59
Joined
Oct 20, 2018
Messages
3,476
BigE - just looping the listbox items is maybe like this, but not in a click or update event. Might require a button click event.
Code:
Dim persVar As Variant
Dim persStr As String, Dim sql As String

For Each persVar In Me.lstBox_SupportingPersona.ItemsSelected
  persStr = perStr & Nz(persVar,Me.lstBox_SupportingPersona.ItemData(persVar)) & "',"
Next
persStr = Left(persStr,Len(perStr)-1)

sql = "first part of the sql goes here IN('" & persStr & "')"
 debug.print sql '<< comment out when not needed
I don't completly understand your situation (e.g. why a listbox selected item would be null) but went with that anyway. The above is untested of course, and assumes you're going to construct the sql in code (also not quite understanding how you will handle that). I probably have this part wrong as I don't know or have forgotten the structure during the post
Code:
Nz(persVar,Me.lstBox_SupportingPersona.ItemData(persVar)) & "',"
I'm trying to say If persVar isnull, use selected item itemData property value. Anyway, it was just meant as a guide, not a piece of code that will necessarily work for you as is.
 

Big E

Diamond Supporter
Local time
Today, 04:59
Joined
Jun 28, 2019
Messages
12
Micron, thank you for following up. Being barely more than an "Access Hack", I am certainly not absolutely committed to the solution that seems to work at the moment (especially if other, unforeseen issues arise) so your input is greatly appreciated should I need to re-engineer.


The form this code is associated to is actually bound to a different table that manages the operation information., So, any record updates are handled through that binding. This particular update function was designed to update records in a separate, unbound table. If a user was managing the operation itself on this form, but had not yet assigned a persona to support it, they would not choose one from the list box and txtBox_HiddenPersona would be empty. The lack of data in this field was throwing an error complaining about using a Null value. So to head this error off at the pass, I did an IF statement that identifies if the textbox l is empty and, if so, to move on. No error now. I probably could've done an OnError statement, but like I said I'm fairly green with all this and am not good at those. So, I went with this.

Thank you again for all your input and assistance.
 
Last edited:

Micron

AWF VIP
Local time
Today, 04:59
Joined
Oct 20, 2018
Messages
3,476
NP. You know where we all are if needed.
Good luck with it.
 

Users who are viewing this thread

Top Bottom