Multiselect Listbox - Capture Values

[Bombshell Disaster]

Thanks VBAInt for your previous help on another post that has got me this far. I have an appointments table and I now have set up a many to many table for the multiple medical instructions for each appointment. On the main appointments form there is a subform that allows the user to select from a listbox multiple medical instructions for the appointment.

Three questions please?

1. When I select say two items in the list box and then check my many to many table it has written 3 records, 2 that are correct and an additional with just the ApptID populated. My thinking that it was something to do with the inital tab into the listbox?
2. Is the best method of writing the records to the table by using the cmdMedIns_Click() event? I have played with the other events on lost_focus, exit etc. Though not getting the result required. It seems long winded to select the options in the list box and then click on the button
3. After the user has selected the options in the medical instructions list box, as well as writing the values to the table, I want them choices to be saved whenever that record is viewed. Please can someone point me in the right direction there.

Many thanks...

Private Sub cmdMedIns_Click()
    Dim varItm As Variant, db As DAO.Database
    
    'DoCmd.RunCommand acCmdSaveRecord
    
    Set db = CurrentDb

    With Me.lstMedIns
        For Each varItm In .ItemsSelected
            db.Execute "INSERT INTO tblMedicalDetails (ApptID, MedInsID) " & _
                       "VALUES (" & Me.ApptID & ", " & .ItemData(varItm) & ");"
'            DoEvents    ' <-- It seems to require a little delay
        Next varItm
        
'        If .ItemsSelected.Count > 0 Then
'            .Value = Null   ' <-- For Extended, this deselects all selected values
'
'            Me.List_CurrentOrder.Requery
'        End If
    End With
    
    Set db = Nothing
End Sub

 

[Bombshell Disaster]

DOH! It's amazing what some sleep can do for you...

I have stopped trying to recreate the wheel and added check boxes..

All learning is worthwhile even if you spend countless hours trying when working to a deadline - tongue in cheek!