Add Selected List Box Items to Table

[bconner]

I have a table named Tbl_Temp_PP_Edit_Name and a list box named List_Edit_Name I would like to take the items selected in the list box and add it to the table. Below is the code I have tried but it failed saying Sub or Function not defined. Can someone help?

Private Sub Command6_Click()
 

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Tbl_Temp_PP_Edit_Name", dbOpenDynaset)
row = List_Edit_Name.ListIndex
 
 
For Each row In List_Edit_Name.ItemsSelected
PP_EDIT = List_Edit_Name.Column(0, row)

With rst
.AddNew
.Fields("PP_Edit_Name") = PP_EDIT
Update
End With
Next row

rst.Close
Set rst = Nothing
 
End Sub

 

[boblarson]

A couple of issues. First declare your recordset object as DAO.Recordset and make sure you have a check box on Microsoft DAO 3.x Library (where x is either .51 or .6 depending on what you have).

Second, the way you are trying to use Row.

Private Sub Command6_Click()
 

Dim rst As [B][COLOR="Red"]DAO.[/COLOR][/B]Recordset
Dim varItm As Variant

Set rst = CurrentDb.OpenRecordset("Tbl_Temp_PP_Edit_Name", dbOpenDynaset)

 
 
For Each varItm In List_Edit_Name.ItemsSelected

PP_EDIT = List_Edit_Name.ItemData(varItm)

   With rst
      .AddNew
      .Fields("PP_Edit_Name") = PP_EDIT
      [COLOR="red"][B].[/B][/COLOR]Update
   End With

Next varItm

rst.Close
Set rst = Nothing
 
End Sub

You also needed a period before Update.

 

[boblarson]

Oh, and you should declare PP_EDIT too.

 

[bconner]

Mr. Larson,
As always thank you very much.....it worked perfectly!

 

[boblarson]

You are welcome. Glad we could assist.