Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Error Using List Box with Simple Multi Select

  1. #1
    MichaelA is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    92

    Error Using List Box with Simple Multi Select

    I have a table with a list box whose multi Select Property is set to Simple. When I try to select an item from the list, I get an error message that says "You must enter a value in the 'tblCourseEnrollment.CourseID_FK' field. If I change the Multi Select property to None, the form works just fine. What am I missing?



    Thank you!

  2. #2
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    380
    You have a FORM with a listbox. Is the listbox bound or unbound? Do you have any code that runs after update?
    Are there fields that are required? Any referential integrity?

  3. #3
    MichaelA is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    92
    Yes, a form. Sorry. (Extreme novice) I'm not sure what "bound" means. There is no after update code. I don't believe I set any fields to required but they should be. I set all referential integrity to yes.

    UPDATE: The fields are required.

  4. #4
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    380
    A multiselect listbox has a value of null as opposed to a regular listbox which will have the value of the one selected item.
    To get the values of a multiselect listbox you must iterate through the .itemsSelected collection of the listbox.

    I would guess that your listbox is bound to a field that is required. Bound meaning the control source of the list is set to a field in your table or query.
    You're getting an error because the required field if missing (null).

    Could you describe your table and fields and what you're trying to do?
    I would guess your trying to set a number of courses to a student. Are you using a junction table such as tblStudentCourse with fields like StudentCourseID, StudentID and CourseID?

  5. #5
    MichaelA is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    92
    Quote Originally Posted by moke123 View Post
    A multiselect listbox has a value of null as opposed to a regular listbox which will have the value of the one selected item.
    To get the values of a multiselect listbox you must iterate through the .itemsSelected collection of the listbox.

    I would guess that your listbox is bound to a field that is required. Bound meaning the control source of the list is set to a field in your table or query.
    You're getting an error because the required field if missing (null).

    Could you describe your table and fields and what you're trying to do?
    I would guess your trying to set a number of courses to a student. Are you using a junction table such as tblStudentCourse with fields like StudentCourseID, StudentID and CourseID?
    I am trying to set up a table where a teacher could "enroll" students into classes. Once enrolled, the gradebook would allow grades for individual assignments for the courses they are enrolled in to be recorded. You are correct, I am using a junction table tblCourseEnrollment with fields StudentID_FK and CourseID_FK.

    What do you mean "iterate through the .itemsSelected collection of the listbox?"

    Thank you!

  6. #6
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    380
    What do you mean "iterate through the .itemsSelected collection of the listbox?"
    Basically you loop through the listbox and grab the selected items.

    I use my own function for this as I tend to use a lot of list boxes.



    Code:
    Public Function getLBX(lbx As ListBox, Optional intColumn As Variant = 0, Optional Seperator As String = ",", _
                           Optional Delim As Variant = Null) As String
    
    
    'Iterates thru the multiselect listbox and constructs an array of the selected items
    'Arguments:
    'Lbx is Listbox Object ie.Me.MyListbox
    'intColumn is the column # to be returned
    'Seperator is the character seperating items in array returned
    'Delim is optional delimiter to be return in array ie. #1/1/2001#,#12/25/2015#
    
    
        Dim strList As String
        Dim varSelected As Variant
    
    
        'On Error GoTo getLBX_Error
    
    
        If lbx.ItemsSelected.Count = 0 Then
            'MsgBox "Nothing selected"
        Else
    
    
            For Each varSelected In lbx.ItemsSelected
    
    
                If Nz(lbx.Column(intColumn, (varSelected)), "") <> "" Then
    
    
                    strList = strList & Delim & lbx.Column(intColumn, (varSelected)) & Delim & Seperator
    
    
                Else
    
    
                    strList = strList
    
    
                End If
    
    
            Next varSelected
    
    
            If Nz(strList, "") <> "" Then
    
    
                strList = Left$(strList, Len(strList) - 1)  'remove trailing comma
    
    
            End If
        End If
    
    
        getLBX = strList
    
    
        On Error GoTo 0
        Exit Function
    
    
    getLBX_Error:
    
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getLBX of Module modLBX"
    
    
    End Function
    The getLBX code will return a comma delimited string of the selected items like - 1,2,5,9

    you can use something like

    Code:
         dim strSql as String
         dim varItm as variant
    
         varItm = split(getLBX,Me.MyListBoxName),",") 
    
         for i = 0 to ubound(varItm)
              strSql = "Insert into tblCourseEnrollment (StudentID_FK, CourseID_FK) values (" & studentID & "," & varItm(i) & ")"
              CurrentDb.Execute strSql,dbfailOnError
         next i
    obviously insert your listbox name, fields and such into the above.

  7. #7
    MichaelA is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    92
    Where do I place the two pieces of code.

  8. #8
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    380
    the getLBX function would go in a standard module.
    the second code would go in your form module perhaps under a command button.
    the value for your studentid should be changed to your forms control that holds the studentid.

  9. #9
    MichaelA is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    92
    OK, I finally got around to entering the code. (I've been away at a wedding.)

    I created module modLBX. And inserted the other code into the code for the form and changed "MyListBoxName" to "lbxCourseID_FK". When I open the form, I get the following error message.

    The expression On Load you entered as the event property setting produced the following error: Expected: end of statement.

    Below is the full code for the form:



    Dim strSql As String
    Dim varItm As Variant


    varItm = split(getLBX,Me.lbxCourseID_FK),",")


    For i = 0 To UBound(varItm)
    strSql = "Insert into tblCourseEnrollment (StudentID_FK, CourseID_FK) values (" & StudentID & "," & varItm(i) & ")"
    CurrentDb.Execute strSql, dbFailOnError
    Next i


    Private Sub Form_Load()
    DoCmd.GoToRecord , , acNewRec
    End Sub


    Private Sub StudentID_FK_Enter()
    Me.StudentID_FK.Dropdown
    End Sub



    Quote Originally Posted by moke123 View Post
    Basically you loop through the listbox and grab the selected items.

    I use my own function for this as I tend to use a lot of list boxes.



    Code:
    Public Function getLBX(lbx As ListBox, Optional intColumn As Variant = 0, Optional Seperator As String = ",", _
                           Optional Delim As Variant = Null) As String
    
    
    'Iterates thru the multiselect listbox and constructs an array of the selected items
    'Arguments:
    'Lbx is Listbox Object ie.Me.MyListbox
    'intColumn is the column # to be returned
    'Seperator is the character seperating items in array returned
    'Delim is optional delimiter to be return in array ie. #1/1/2001#,#12/25/2015#
    
    
        Dim strList As String
        Dim varSelected As Variant
    
    
        'On Error GoTo getLBX_Error
    
    
        If lbx.ItemsSelected.Count = 0 Then
            'MsgBox "Nothing selected"
        Else
    
    
            For Each varSelected In lbx.ItemsSelected
    
    
                If Nz(lbx.Column(intColumn, (varSelected)), "") <> "" Then
    
    
                    strList = strList & Delim & lbx.Column(intColumn, (varSelected)) & Delim & Seperator
    
    
                Else
    
    
                    strList = strList
    
    
                End If
    
    
            Next varSelected
    
    
            If Nz(strList, "") <> "" Then
    
    
                strList = Left$(strList, Len(strList) - 1)  'remove trailing comma
    
    
            End If
        End If
    
    
        getLBX = strList
    
    
        On Error GoTo 0
        Exit Function
    
    
    getLBX_Error:
    
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getLBX of Module modLBX"
    
    
    End Function
    The getLBX code will return a comma delimited string of the selected items like - 1,2,5,9

    you can use something like

    Code:
         dim strSql as String
         dim varItm as variant
    
         varItm = split(getLBX,Me.MyListBoxName),",") 
    
         for i = 0 to ubound(varItm)
              strSql = "Insert into tblCourseEnrollment (StudentID_FK, CourseID_FK) values (" & studentID & "," & varItm(i) & ")"
              CurrentDb.Execute strSql,dbfailOnError
         next i
    obviously insert your listbox name, fields and such into the above.

  10. #10
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    380
    Form load is too early an event as you have not made any selections in the list.
    Use a command button.

  11. #11
    MichaelA is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    92
    But the Form Load is just setting the form to a new record, is it not?

    Quote Originally Posted by moke123 View Post
    Form load is too early an event as you have not made any selections in the list.
    Use a command button.

  12. #12
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    380
    where are you running this code?

    Code:
    Dim strSql As String
    Dim varItm As Variant
    
    
    varItm = split(getLBX,Me.lbxCourseID_FK),",")
    
    
    For i = 0 To UBound(varItm)
    strSql = "Insert into tblCourseEnrollment (StudentID_FK, CourseID_FK) values (" & StudentID & "," & varItm(i) & ")"
    CurrentDb.Execute strSql, dbFailOnError
    Next i

  13. #13
    MichaelA is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    92
    It is in the form code


    Quote Originally Posted by moke123 View Post
    where are you running this code?

    Code:
    Dim strSql As String
    Dim varItm As Variant
    
    
    varItm = split(getLBX,Me.lbxCourseID_FK),",")
    
    
    For i = 0 To UBound(varItm)
    strSql = "Insert into tblCourseEnrollment (StudentID_FK, CourseID_FK) values (" & StudentID & "," & varItm(i) & ")"
    CurrentDb.Execute strSql, dbFailOnError
    Next i

  14. #14
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    380
    It needs to be in an event.
    Put it under a command button on click event.

  15. #15
    MichaelA is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    92
    OK, I'm confused. What goes on the click event for the button, the Form Load or the code you made that begins, "Dim strSql As String"

    And where does the button go? I tried making a button on the form and put the Form Load code on the click event for it and the code wound up in the same place.

    ahhhh!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-21-2014, 01:16 PM
  2. Replies: 15
    Last Post: 11-20-2013, 04:30 PM
  3. Multi Select Simple Returning No Results
    By jsimard in forum Programming
    Replies: 11
    Last Post: 01-24-2012, 10:28 PM
  4. Replies: 1
    Last Post: 10-22-2010, 10:11 AM
  5. Replies: 1
    Last Post: 02-25-2009, 07:29 PM

Posting Permissions

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