Add Record to Subform using a Listbox

[yator]

I have a Form with a Subform. The Form contains a multi-select List Box for
selection of records to add to the Subform. I can’t seem to get the code
right to accomplish this. Any help would be very much appreciated.

FrmEvents (contains List Box: “lstDefs†& subform: “frmSubEventDefâ€)
Record Source: q_frm_event
Primary key: EventNo

lstDefs (Unbound)
Field 1: DefNo
Field 2: Defintion
Multi-Select: Extended

frmSubEventDef
Record Source: q_frm_event_defs
Field 1: EventNo
Field 2: DefNo
Link Child Fields: EventNo
Link Master Fields: EventNo

On selection of specific row(s) in lstDefs, the “DefNo†should be added to
the Subform: “frmSubEventDefâ€

 

[n00b]

Add a button to the form that can trigger code similar to the
following in the click event (the user should click the button after
all selections are made):

(This code assumes q_frm_event_defs is updatable.)


Private Sub Button1_Click()

With lstDefs

If .ItemsSelected.Count > 0 Then

For I = 0 To .ItemsSelected.Count - 1

intSelectedIndex = .ItemsSelected.ITEM(I)

CurrentDb.Execute "INSERT INTO q_frm_event_defs
(EventNo, DefNo) VALUES (" & Me.EventNo & ","
& .ItemData(intSelectedIndex) & ")"

Next I

frmSubEventDef.Form.Requery

End If

End With

End Sub

 

[NetworkTrade]

in the ListBox 'AfterUpdate' event put:

Me.SubFormName.Form!ControlName=Me.ListBox

 

[Dale Fye]

Yator,

I don't generally do this in the click event of the listbox, but via command
button next to it (cmd_Add_Selected). In order to do this, you will need to
append the records to the table that the subform uses as its Record Source.
My guess is that there is more than one field involved, so I'll assume you
have a combo box or some other control on your form that contains the EventNo
data. So the code might look like:

Private sub cmd_Add_Selected_Click

Dim varItem as variant
Dim strSQL as string

For each varItem in me.lstDefs.Itemsselected

strSQL = "INSERT INTO tbl_SomeTableName (EventNo, DefNo) " _
& "Values(" & me.cbo_EventNo & ", " _
& me.lstDefs.Columns(0, varItem) & ")"
currentdb.execute strsql, dbFailOnError
Next
me.subformControlName.Form.Requery

End Sub

HTH
Dale

 

[yator]

Dale,
I tried this but receive a Compile Error: "Method or data member not
found" at
Me.EventNo ? If I eliminate the EventNo field I receive the error at
Me.LstDefs.Columns.

Also, I somewhat oversimplifie the scenario here. I actually have 2 List
Boxes that add different levels of "definitions" to the SubForm. Would I just
add a second loop to the code for LstDefs2 starting with " For Each varItem
In Me.LstDefs_2.ItemsSelected...." ?
thanks for the help

Private Sub cmdAdd_Selected_Click()
On Error GoTo Err_cmdAdd_Selected_Click
Dim varItem As Variant
Dim strSQL As String

For Each varItem In Me.LstDefs.ItemsSelected

strSQL = " INSERT INTO tbl_IDQ_event_defs (EventNo, DefNo) " _
& "VALUES (" & Me.EventNo & "," _
& Me.LstDefs.Columns(0, varItem) & ")"
CurrentDb.Execute strSQL, dbFailOnError
Next
Me.frmHAI_sub_event_defs.Form.Requery

Exit_cmdAdd_Selected_Click:
Exit Sub

Err_cmdAdd_Selected_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Selected_Click

End Sub