Meeting with one company but notes pertaining to a number of sale opportunities

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

 

[Bobp3114]

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.

 

[Bobp3114]

Thanks Ken, this looks the way to go.
I have attached a PDF of of my form
I am unsure about your "StudentCourses" as to how it applies to my form. tblOpportunities or tblNotes

 

[arnelgp]

you attached a real db to better understand what entities you have on that form.

 

[GPGeorge]

Thanks Ken, this looks the way to go.
I have attached a PDF of of my form
I am unsure about your "StudentCourses" as to how it applies to my form. tblOpportunities or tblNotes

Is it not possible to share an accdb instead of a PDF so that people can get a better insight into the structures and functions? If you can share that accdb, remove and confidential data.

 

[Ken Sheridan]

Thanks Ken, this looks the way to go.
I have attached a PDF of of my form
I am unsure about your "StudentCourses" as to how it applies to my form. tblOpportunities or tblNotes

As my colleagues have said we 'd really need you to post a copy of your file, stripped of personal or otherwise sensitive data, to be able to comment confidently. At the very least we'd need to see the database's physical model, which can be done most easily by posting an image of its relationships window, ensuring that it is set out clearly to show all tables and all of their columns, along with the relationships between tables. The image below of my StudentCourses file's model is a simple example.

Whether you use code like mine or MadPiet's depends on how you intend you use the list box. If you just want it for selecting multiple items and inserting rows into whatever table models the many-to-many relationship, then MadPiet's code should be your model. If, on the other hand, you want the listbox to display the selected items when you move the form to a record, then mine would be your model.

However, using a multi-select list box in either of the above scenarios depends on whether there are any non-key columns in the table into which rows are to be inserted. StatusID in StudentCourses is an example. If this is the case you'd either have to assign a default value to such a column, as I do with StatusID, or use the conventional interface of a form with an embedded subform bound to the table which models the many-to-many relationship. Even if it can be done with a list box, I would generally use a form/subform.

 

[Gasman]

You can use this to obscure any sensitive data

 

[DHookom]

Is it not possible to share an accdb instead of a PDF so that people can get a better insight into the structures and functions? If you can share that accdb, remove and confidential data.

I’m not sure which file types are allowed as attachments prior to making 100 posts. Zipping the file and changing the file extension might work.