Saving Listbox selection to table on next record action

[lowells]

I will preface this question by saying I am new to Access VBA though I am quite experienced with Excel VBA.

I have added a listbox to the main form. The user can select multiple names from the list box. I have a sub that forms a comma delimited string of the selection(s) and saves directly to a table to which the form is bound. I call this sub when the form is closed and unloaded and it works just fine (code is listed below).

The problem is that if the user clicks for the next record instead of closing the form, the value is not saved to the table. I have tried adding a call to the save sub from the listbox's AfterUpdate event (and several other listbox events) but it doesn't work properly. The field in the table is empty instead of containing the selected values. Also, I get "Write Error Another user has modified the record...", I assume that is because a second attempt is being made to save the selection because I am calling the save sub too many times. How can I make this work? Thanks.

Public Sub SaveSelectedResponsibility()

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    
    Set db = CurrentDb()                                                            'set a pointer to the database
    Set qdf = db.QueryDefs("qryMultiSelect")                                        'set a pointer to the query

    For Each varItem In Me!LBResponsibility.ItemsSelected                           'loop on all selected items
       strCriteria = strCriteria & "," & Me!LBResponsibility.ItemData(varItem)      'build a string of all the selected entries
    Next varItem
    If Len(strCriteria) = 0 Then Exit Sub                                           'check to see that at least one entry was selected
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)                          'strip off the leading comma
    strCriteria = "'" & strCriteria & "'"                                           'enclose the string of names with single quotes for SQL
    
    strSQL = "Update tblActionItems set Responsibility=" & strCriteria & " where ActionItemID=" & Me!ActionItemID.Value     'build the SQL string
    
    qdf.SQL = strSQL                                                                'update the query with the SQL
    DoCmd.SetWarnings False                                                         'turn off the warnings so no popups confirming the actions appear
    DoCmd.OpenQuery "qryMultiSelect"                                                'execute the update command
    DoCmd.SetWarnings True                                                          'turn warnings back on
    
    Set db = Nothing                                                                'delete the db object
    Set qdf = Nothing                                                               'delete the qdf object
End Sub

[orange]

I'm not sure I understand your requirement.
This ...The field in the table is empty instead of containing the selected values... suggests you are using multi-valued fields.
Many will tell you to avoid such a construct.

You may have a design issue (unclear) because it seems you have a 1 to many relationship involving ActionItem.
It would be helpful if you told readers an overview of the business issue/purpose of your database in simple English.

For your benefit it might be useful to add this line

Debug.Print strSQL

right before
qdf.SQL = strSQL

This will show you how Access has interpeted/rendered your strSQL and will print it to the immediate window.

[lowells]

The database tracks action items for a project. The main table contains one action item per record. The Responsibility field in the table stores the names of the individual(s) responsible for resolving that action item. I want the user to be able to select the names from the list box who are responsible and store those names in the table in the Responsibility field. That all works. Nothing is wrong with the SQL. The problem is getting the data to save when I click the next record button. It saves when I close the form as stated previously. Am I missing something about how Access updates records to a bound table from a form? One thing I'm confused about is why all the other edit controls on the form get updated automatically when I click the next record button but not the list box values for Responsibility? Why do I have to write a function to save those values? Do I have to write a function to save those values? They weren't getting saved until I wrote the function but maybe I'm missing something.

[orange]

A Project has 1 or more ActionItems
An ActionItems is the responsibility of 1 or many/more Persons ----????

You do not store multiple Names in 1 field. (1 fact 1 field)

Can you show your table structures (jpg) of relationships window or table design?
With bound forms Access saves the current record when you move off that record.

What is the code behind the NEXT button?

In a typical case, if I had multiple people assigned to a task, I would have a junction table between People and Task. Something like tblPeopleAssignedToTask, and a separate record for each Person-Task. The PK for the table would be PersonId and TaskId (compound PK).

To populate the table, I'd have a form with Tasks, and a listbox with People/Person(s).
Allow multi select on the list box, then an Assign button. On Click of Assign, use the TaskID of current task, and iterate the selected items assigning(creating a new record in tblPeopleAssignedToTask). Each Selected Person from listbox would be in a new record in the assignment table.

[lowells]

You are focused on issues with my design. I appreciate the advice you have about the 1-name-per-field but the problem I am most concerned about right now is how to get my names stored when I click either the Next or Previous (built-in) buttons on the bottom of the form as shown below. I'll worry about the 1 name per field issue later. What I'm hoping is for someone to tell me how to override the buttons shown so I can update the table when I click them. Thanks.

Sorry, having trouble with the image of the buttons. They are the default buttons provided by MS Access at the bottom of the form.

[Missinglinq]

...Am I missing something about how Access updates records to a bound table from a form? One thing I'm confused about is why all the other edit controls on the form get updated automatically when I click the next record button but not the list box values for Responsibility? Why do I have to write a function to save those values?

What you have to understand is that when the Multi-Select Property of a Listbox is set to anything other than None...the Listbox Control has no Value Property! This is why the selections cannot be saved, automatically, like the Values of other Controls are, when you move to another Record...this is why you need a Sub Routine to save those multiple selections to a single, underlying, Field.

What you need to do is to

1. Disable the native Navigation Buttons (Properties - Format - Navigation Buttons)
2. Create custom Navigation Buttons
3. Call the SaveSelectedResponsibility sub from the OnClick event of each Navigation Buttons as you already do when the Formis Closed/Unloaded

Linq ;0)>