Moving items between listboxes and populating table

[ScubaSteve]

I don't know if this is possible, but it sure does seem it!

First let me explain what I am looking for. I want a combobox that
when an item is selected, it populates a listbox with items from a
table. When someone dblclicks an item in that listbox, I want it to
move to another list box and then save to a table. There should be
multiple selections from multiple combobox subdivisions - so each item
won't be from solely one combobox selection. The selection then saves
per record.

Here are the tables I have:
Tables: t_Damnit: Fields: DamnitID, DamnitName, Description. And
t_Diagnosis: Fields: DiagnosisID, DiagnosisName, Description, DamnitID
(linked to t_damnit!damnitID), Selected.

Combobox1: combo63 (the damnit list); List: combo65 (pulls data from
diagnosis id according to that chosen on damnit - sorry, it used to be
a combobox), and lstproblem.

Here is the code I am trying to run. While I can get items to move
between listboxes, it is not saving to any record, so there's no
saving. Wondering where to make a table to assign these values to a
specific index key (CaseID)? Also, the Selected field is default to
yes, but once I run this code once; the chosen items change to No and
remain that way.



Option Explicit
Private Sub cmdAdd_Click()

Dim conn As ADODB.Connection
Dim MyRS As ADODB.Recordset
Dim SelItem As Control

' set SelItem = to the selected item.
Set SelItem = Me.Combo65

If IsNull(SelItem) Then
MsgBox "Please select an item from the list."
Else
' set up the connection and recordset.
Set conn = CurrentProject.Connection
Set MyRS = New ADODB.Recordset

' open the recordset.
MyRS.Open "t_diagnoses", conn, adOpenDynamic, adLockOptimistic

With MyRS
' find the record for the selected item.
.Find "DiagnosisID = '" & SelItem & "'"
' set the Selected of the selected record to "No".
.Fields("Selected").Value = "No"
.Update
End With

Set MyRS = Nothing
Set conn = Nothing

' make sure the list boxes have the current values.
Me.Combo65.Requery
Me.lstproblem.Requery
End If

End Sub

Private Sub cmdClear_Click()

Dim conn As ADODB.Connection
Dim MyRS As ADODB.Recordset

' set up the connection and recordset.
Set conn = CurrentProject.Connection
Set MyRS = New ADODB.Recordset

' open the recordset.
MyRS.Open "t_diagnoses", conn, adOpenDynamic, adLockOptimistic

With MyRS
' loop through all records and set the Selected to "Yes".
Do While Not .EOF
.Fields("Selected").Value = "Yes"
.Update
.MoveNext
Loop
End With

Set MyRS = Nothing
Set conn = Nothing

' make sure the list boxes have the current values.
Me.Combo65.Requery
Me.lstproblem.Requery

End Sub
Private Sub cmdDel_Click()

Dim conn As ADODB.Connection
Dim MyRS As ADODB.Recordset
Dim SelItem As Control

' set SelItem = to the selected item.
Set SelItem = Me.lstproblem

If IsNull(SelItem) Then
MsgBox "Please select an item from the list."
Else
' set up the connection and recordset.
Set conn = CurrentProject.Connection
Set MyRS = New ADODB.Recordset

' open the recordset.
MyRS.Open "t_diagnoses", conn, adOpenDynamic, adLockOptimistic

With MyRS
' find the record for the selected item.
.Find "DiagnosisID = '" & SelItem & "'"
' set the Selected of the selected record to "Yes".
.Fields("Selected").Value = "Yes"
.Update
End With

Set MyRS = Nothing
Set conn = Nothing

' make sure the list boxes have the current values.
Me.lstproblem.Requery
Me.Combo65.Requery
End If

End Sub
Private Sub Combo65_DblClick(Cancel As Integer)
' run the code behind the command button cmdAdd
cmdAdd_Click
End Sub

Private Sub lstproblem_DblClick(Cancel As Integer)
' run the code behind the command button cmdDel
cmdDel_Click
End Sub


Private Sub Combo63_AfterUpdate()
Me.Combo65 = Null
Me.Combo65.Requery
Me.Combo65 = Me.Combo65.ItemData(0)
End Sub

Private Sub Form_Current()
Me.Combo65.Requery
End Sub

I hope my explanation makes sense!