Results 1 to 6 of 6
  1. #1
    lowells is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    8

    Saving Listbox selection to table on next record action

    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.

    Code:
    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

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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

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

  3. #3
    lowells is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    8
    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.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  5. #5
    lowells is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    8
    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.
    Attached Thumbnails Attached Thumbnails access.jpg  

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by lowells View Post

    ...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)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-08-2013, 02:07 PM
  2. Replies: 21
    Last Post: 10-05-2012, 11:36 AM
  3. Replies: 1
    Last Post: 09-10-2012, 11:21 PM
  4. Replies: 1
    Last Post: 02-01-2012, 11:27 PM
  5. saving Combo Box selection to a table
    By rmiell in forum Access
    Replies: 1
    Last Post: 10-04-2011, 11:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums