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 ************************