• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

Import table code error 3265

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

0
davecocks
Asked:
davecocks
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
can you upload your db
0
 
Rey Obrero (Capricorn1)Commented:
check the table tblIntermediateImport if the field [speciesID]  exists
0
 
davecocksAuthor Commented:
Brilliant!!!! Far too simple. That'll teach me to mess around with whats working!!

Thanks though your help is really appreciated!!!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now