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?
 
carsRSTConnect With a Mentor Commented:
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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
paulucieAuthor Commented:
Same error message !! Sorry
0
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.