Import table code error 3265

Posted on 2009-02-12
Last Modified: 2013-11-27

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.


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


    For i = 0 To rs.Fields.Count - 1

        ReDim Preserve fldArr(i)

        fldArr(i) = rs.Fields(i).name


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


                ![traitID] = rs("traitName")

                ![charID] = rs("charName")

                ![value1] = rs.Fields(fldArr(j))

                ![speciesID] = rs.Fields(fldArr(j)).name

                ![charFull] = rs("charFull")


            End With


       ' End If





'Exit Function

End Function

Open in new window

Question by:davecocks
    LVL 119

    Expert Comment

    by:Rey Obrero
    can you upload your db
    LVL 119

    Accepted Solution

    check the table tblIntermediateImport if the field [speciesID]  exists
    LVL 1

    Author Closing Comment

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

    Thanks though your help is really appreciated!!!!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now