Insert Listbox values into a table (1 Viewer)

Access_Help · Jan 20, 2019

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 · Jan 20, 2019

Replace what you've with the below:

Code:

..
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 · Jan 20, 2019

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

Code:

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

as needed to get the values you are looking for.

JHB · Jan 20, 2019

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

Code:

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

as needed to get the values you are looking for.

Change where?

NauticalGent · Jan 20, 2019

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 · Jan 20, 2019

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 · Jan 20, 2019

NauticalGent said:
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 · Jan 20, 2019

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 · Jan 20, 2019

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

JHB · Jan 20, 2019

Access_Help said:
.. 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 · Jan 20, 2019

NauticalGent said:
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 · Jan 20, 2019

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

Access_Help · Jan 20, 2019

JHB said:
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 · Jan 20, 2019

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

Access_Help · Jan 20, 2019

JHB said:
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 · Jan 28, 2019

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

JHB · Jan 28, 2019

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?

Access_Help said:
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

Insert Listbox values into a table (1 Viewer)

Access_Help

Registered User.

JHB

Have been here a while

NauticalGent

Ignore List Poster Boy

JHB

Have been here a while

NauticalGent

Ignore List Poster Boy

NauticalGent

Ignore List Poster Boy

JHB

Have been here a while

Access_Help

Registered User.

NauticalGent

Ignore List Poster Boy

JHB

Have been here a while

Access_Help

Registered User.

NauticalGent

Ignore List Poster Boy

Access_Help

Registered User.

JHB

Have been here a while

Access_Help

Registered User.

JHB

Have been here a while

JHB

Have been here a while

Similar threads

Users who are viewing this thread