Access 2007 VBA "Item not found in the collection"

The line
 ![Competition] = Me!LstStatComp.Column(1, 0)
returns a 3265 error item not found

searching every posssible syntax and unable to understand
Thanks for the help !


  Dim oRS1 As DAO.Recordset
  Dim oDb1 As DAO.Database
  Set oDb1 = CurrentDb

 Set oRS1 = oDb1.OpenRecordset("SELECT N°Serie FROM TbGraphDistAvg WHERE N°Serie =   1", dbOpenDynaset)
            With oRS1
                .Edit
                ![Competition] = Me!LstStatComp.Column(1, 0)
                .Update
            End With
oRS1.Close
oDb1.Close
paulucieAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

carsRSTCommented:
Is competition a field?  Doesn't appear your SQL has it listed to be pulled back.
0
carsRSTCommented:
Try to include "competition" in this query:
SELECT N°Serie FROM TbGraphDistAvg WHERE N°Serie =   1

So could be...
SELECT N°Serie, competition FROM TbGraphDistAvg WHERE N°Serie =   1
0
paulucieAuthor Commented:
Same error message !! Sorry
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

carsRSTCommented:
Try this code...
(could be there is no record pulled back.  But your code will execute as if there is.  I changed it to check to see if there is a record.)


  Dim oRS1 As DAO.Recordset
  Dim oDb1 As DAO.Database
  Set oDb1 = CurrentDb

 Set oRS1 = oDb1.OpenRecordset("SELECT N°Serie FROM TbGraphDistAvg WHERE N°Serie =   1", dbOpenDynaset)
 If not oRS1.eof then            
      With oRS1
            .Edit
            ![Competition] = Me!LstStatComp.Column(1, 0)
            .Update
      End With
 end if
oRS1.Close
oDb1.Close
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you certain that you need to refer to your combo's list in that way? Generally you'd just refer to the .Column(x), where "x" is the index of the Column you wish to use. Your syntax would refer to a specific Row in the combo's list as well.

I'm not sure if this is just translation, but your Table/Query is appearing here with an odd character in it. It's always a good idea to enclose your table/field names in square brackets:

Set oRS1 = oDb1.OpenRecordset("SELECT [N°Serie] FROM [TbGraphDistAvg] WHERE [N°Serie] =   1", dbOpenDynaset)

If you build a query with that SELECT statement, does it return records? To test that, build a new query, but don't select any tables. When you get to the Query Designer, click View - SQL, and past the SELECT Statement into that view. Now switch to Design view and see if you get any errors. If you do, then fix them and change your SQL to match. If you don't then see if you return any records.

IF you do return records, then again switch to SQL view and see if Access has reformatted your SELECT statement. If so, try using that syntax.







0
paulucieAuthor Commented:
The problem was in the value of the combo box
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.