We help IT Professionals succeed at work.

Import table code error 3265

davecocks
davecocks asked
on
Medium Priority
322 Views
Last Modified: 2013-11-27
Hi,

This piece of code used to work in my database fine. Now I'm not sure why its not working.
Its returning the following error

Run time error 3265     'Item not found in this collection'

Highlights this line :25
![speciesID] = rs.Fields(fldArr(j)).name

but appears to be collecting the value of the field fine
 i.e. rs.Fields(fldArr(j)).name ="4423"

but not transfering it i.e. ![speciesID] = <item not found>

All other terms  ![traitID]  ![charID]   ![value1]   ![charFull]  work great.

Thanks,

Function transposeMarlinTbl()
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim i As Integer, j As Integer, fldArr()
 
'On Error GoTo Err_Out
 
Set rs = CurrentDb.OpenRecordset("tblMarlin")  ' change the name of the source table
Set rs1 = CurrentDb.OpenRecordset("tblIntermediateImport") 'change name of destination table
 
If rs.EOF Or rs.BOF Then
    MsgBox "no records"
    Exit Function
End If
rs.MoveFirst
    For i = 0 To rs.Fields.Count - 1
        ReDim Preserve fldArr(i)
        fldArr(i) = rs.Fields(i).name
    Next
 
Do Until rs.EOF
    For j = 3 To UBound(fldArr) ' from third column
    'get rid of this 'if' if you don't want to import empty fields
        'If Not rs(fldArr(j)).Value Then
           With rs1
                .AddNew
                ![traitID] = rs("traitName")
                ![charID] = rs("charName")
                ![value1] = rs.Fields(fldArr(j))
                ![speciesID] = rs.Fields(fldArr(j)).name
                ![charFull] = rs("charFull")
 
                .Update
            End With
            'Else
       ' End If
    Next
    rs.MoveNext
Loop
 
'Err_Out:
'Exit Function
 
End Function

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
can you upload your db
CERTIFIED EXPERT
Top Expert 2016
Commented:
check the table tblIntermediateImport if the field [speciesID]  exists

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Brilliant!!!! Far too simple. That'll teach me to mess around with whats working!!

Thanks though your help is really appreciated!!!!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.