Meeting with one company but notes pertaining to a number of sale opportunities (2 Viewers)

[Bobp3114]

Rep goes to a meeting with one company, but makes notes pertaining to a number of sale opportunities with that supplier. The rep wants to make notes for that visit and link those notes (the notes are all one entry) to one selected sale opportunity or even ,say ,three sale opportunities. These notes are one entry that mentions all sale opportunities mentioned. I can use a list box to enable multi-selection but what is recorded is a string value IDs (159,2035,5698).I am looking for help in 1. the best way to record which opportunities are mentioned in the notes. and 2. How to I display that selection in say, an edit form?

 

[MadPiet]

You don't bind the field directly to the listbox. Instead, you loop through the .ItemsSelected collection of the multi-select listbox, and then write them individually to your table in code. Then if you want to edit them, they're in a properly normalized format, so you can edit/delete one of them without affecting the others at all.

'******************** Code Start ************************
'-- cribbed from whatever AccessWeb turned into
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
    Set frm = Form!frmMyForm
    Set ctl = frm!lbMultiSelectListbox

'-- this is the important part
    For Each varItem In ctl.ItemsSelected
        strSQL = "INSERT INTO MyTable(ControlValue) VALUES('" + ctl.ItemData(varItem) & "')"
        Currentdb.Execute strSQL, dbFailOnError
    Next varItem
'******************** Code end ************************

 

[Ken Sheridan]

I can use a list box to enable multi-selection but what is recorded is a string value IDs (159,2035,5698).I am looking for help in 1. the best way to record which opportunities are mentioned in the notes. and 2. How to I display that selection in say, an edit form?

The attached little demo file includes an illustration of how a multi-select list box can be used for both data insertion, deletion, and display. The demo uses the example of registering a student for multiple courses. The code for the control's AfterUpdate event procedure is as follows:

Private Sub lstCourses_AfterUpdate()

    Dim n As Integer
    Dim strCriteria As String
    Dim strSQL As String
    
    Const MESSAGE_TEXT = "A student must be entered before selecting any courses."
    
    If IsNull(Me.StudentID) Then
        MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"
        ' loop through all items in list
        ' and deselect each one
        With Me.lstCourses
            For n = 0 To .ListCount - 1
                .Selected(n) = False
            Next n
        End With
        Exit Sub
    End If

    With Me.lstCourses
        For n = .ListCount - 1 To 0 Step -1
            strCriteria = "StudentID = " & Nz(Me.StudentID, 0) & " And CourseID = " & .ItemData(n)
            If .Selected(n) = False Then
                ' if item has been deselected then delete row from table
                If Not IsNull(DLookup("StudentID", "StudentCourses", strCriteria)) Then
                   strSQL = "DELETE * FROM StudentCourses WHERE " & strCriteria
                   CurrentDb.Execute strSQL, dbFailOnError
                End If
            Else
                ' if item has been selected then insert row into table
                 If IsNull(DLookup("StudentID", "StudentCourses", strCriteria)) Then
                   strSQL = "INSERT INTO StudentCourses (StudentID, CourseID, StatusID) " & _
                        "VALUES(" & Me.StudentID & "," & .ItemData(n) & ",1)"
                   Me.Dirty = False
                   CurrentDb.Execute strSQL, dbFailOnError
                End If
            End If
        Next n
    End With
    
End Sub

The above code does not reference the control's ItemsSelected collection, but instead loops through all courses listed. This enables the deletion of a course registration in addition to the insertion of a new registration. However, using a list box in this way, or for insertion of new rows alone by iterating the ItemsSelected collection, does not allow the insertion or editing of values in other key columns or additional non-key columns in the StudentCourses table which models the ternary many-to-many relationship type between students, courses, and statuses. In this case the other key value, StatusID, is given a default value of 1 (representing 'Pending'), which might or might not correctly represent the student's status in relation to the selected course. while this method is fine in the case of a simple binary relationship type, where there are no additional non-key attributes of the relationship, I would otherwise not recommend it. The more appropriate, and conventional solution of a form with embedded subform, the latter based on the table which models the relationship type, is recommended and illustrated in the demo. The conventional interface is very easy to build and is largely code-free.

Whichever way you do it, an important point is that the two key columns in the table modelling the relationship type are included in a single unique index to prevent invalid duplication of data. In my demo this is done my making the StudentID and CourseID columns in StudentCourses the composite primary key of the table. In another context the key might be extended, e.g if a student could register for the same course on different dates, a CourseDate column would be included in the key.