Insert Listbox values into a table

[Access_Help]

The following script is being used to add two items from the listbox into a table using the double click event. I am faced with: 3265: item not found in this collection

I am using a pre-written script and modifying it to the best of my VB knowledge


The two field names are: Session and Initials
In the list box they are in the first (0) and 3rd columns (2) - how do I extract these values only and append them?

Private Sub List41_DblClick(Cancel As Integer)
Dim rst As DAO.Recordset
Dim varItm As Variant

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


For Each varItm In List41.ItemsSelected

PP_EDIT = List41.ItemData(varItm)

With rst
.AddNew
.Fields("Session, Initials") = PP_EDIT
.Update
End With

Next varItm

rst.Close
Set rst = Nothing
End Sub

 

[JHB]

Replace what you've with the below:

..
For Each varItm In List41.ItemsSelected
  With rst
    .AddNew
    ![Session] = List41.Column(0, varItm)
    ![Initials] = List41.Column(2, varItm)
    .Update
  End With
Next varItm
..

 

[NauticalGent]

I suspect the OP's original code was returning the Bound column's value which was not in the table he/she was trying to add to.

In other words, Access_Help, ListBox indexs (the numbers that correspond to the columns, are Zero-based. Simply put the counting begins at 0 and not 1).

JHB's code is what you want, however you may need to change

List41.Column([COLOR="Red"]0[/COLOR], varItm)

as needed to get the values you are looking for.

 

[JHB]

..
JHB's code is what you want, however you may need to change

List41.Column([COLOR=Red]0[/COLOR], varItm)

as needed to get the values you are looking for.

Change where?

 

[NauticalGent]

Morning JHB,

I was meaning that the Column Index, in this case "0", may not be what the OP was looking for and may have to change it - which is why I posted it in red.

Your code is perfect and will do exactly what the OP wants.

 

[NauticalGent]

Oh Christ! I just re-read post # 1 and see that I just made a complete ass of myself. Sorry for the drama folks, I'm going to go get a cup of coffee...

My signature line says it all.

 

[JHB]

Oh Christ! I just re-read post # 1 and see that I just made a complete ass of myself. Sorry for the drama folks, I'm going to go get a cup of coffee...

My signature line says it all.

Yes coffee is good!

 

[Access_Help]

Thank you so much for the help guys, I have a slight problem, it says I cannot change or add a record as it is required in the staff table, however, I have checked and the values in the list box exist in the staff table?

 

[NauticalGent]

Is your table relationships One-to-One or One-to-Many? It's about the only reason I could think of...

 

[JHB]

.. however, I have checked and the values in the list box exist in the staff table?

But even if it exist are you sure you're adding that value?

 

[Access_Help]

Is your table relationships One-to-One or One-to-Many? It's about the only reason I could think of...

One to Many

 

[NauticalGent]

Then, as JHB has stated, are you sure of the values you are inserting?

 

[Access_Help]

But even if it exist are you sure you're adding that value?

Its not adding due to the value not existing in the staff table?
but when I add it manually, it adds, but not through the VBA

 

[JHB]

Could you post your database with some sample data, (zip it) + description how to reproduce the error!

 

[Access_Help]

Could you post your database with some sample data, (zip it) + description how to reproduce the error!

OK, still getting the error...

For Each varItm In List41.ItemsSelected
With rst
.AddNew
![ExamAssign] = ![ExamAssign] = [Forms]![tbl_Exam_Assign Subform]![ExamAssign]
![Session] = List41.Column(0, varItm)
![Initials] = List41.Column(2, varItm)

.Update
End With
Next varItm

rst.Close
Set rst = Nothing

View attachment Database211.zip
Test Data:
Enter Day: Sunday
Period: 11

 

[JHB]

Could you please explain how to fill the List41 with data.

 

[JHB]

In the database you've attached, you haven't change the code to what I posted, why not?
Then you've change the table in the recordset, why?
I've put in my code and change the table in the recordset to what you posted first.
But I do not get the below error, I get another that says it is the table "tbl_Exam_Assign", and that is correct a related record doesn't exist in the "tbl_Exam_Assign".
So what now?

Its not adding due to the value not existing in the staff table?
but when I add it manually, it adds, but not through the VBA