[AccessD] Item Not Found In This Collection

Gustav:

Yes.  I inserted this code:

Set rsPODetail = db.OpenRecordset("Select fldPODRevision FROM tblPODetail
WHERE fldPOHID = " & Me.fldPOHID)

If rsPODetail.BOF = False And rsPODetail.EOF = False Then
rsPODetail.MoveLast
MsgBox rsPODetail.RecordCount

    Do While rsPODetail.EOF = False
	MsgBox rsPODetail!fldPODRevision

and the record count comes up with 3 - that's correct.  And the MsgBox
displayed rsPODetail!fldPODRevision without a problem.

Any clues there?

TIA

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, June 08, 2010 7:49 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Item Not Found In This Collection

Hi Rocky

Will this return a record:

Set rsPODetail = db.OpenRecordset("Select fldPODRevision FROM tblPODetail
WHERE fldPOHID = " & Me.fldPOHID)

Further, why not just open the table once:

Set rsPODetailNew = db.OpenRecordset("Select * FROM tblPODetail WHERE
fldPOHID = " & Me.fldPOHID) Set rsPODetail = rsPODetailNew.Clone

/gustav

>>> rockysmolin at bchacc.com 08-06-2010 16:14 >>>
Dear List:

I am cloning a purchase order, so I'm cycling through the PO detail lines,
creating a new PO Detail record for each existing detail record.  I've
already copied the PO Header record and have the PK available.  All's well
until it comes to one specific field in the PODetail copy code and then I
get the message 'Item Not Found In This Collection'.

The code to copy is:

        rsPODetailNew.AddNew
        rsPODetailNew!fldPOHID = rsPOHeader!fldPOHID
        rsPODetailNew!fldPODLineNumber = rsPODetail!fldPODLineNumber
        rsPODetailNew!fldPODPartNumber = rsPODetail!fldPODPartNumber
        rsPODetailNew!fldPODVendorPart = rsPODetail!fldPODVendorPart
        rsPODetailNew!fldPODPartDescription =
rsPODetail!fldPODPartDescription
        rsPODetailNew!fldPODUOM = rsPODetail!fldPODUOM
        rsPODetailNew!fldPODUnitCost = rsPODetail!fldPODUnitCost
        rsPODetailNew!fldPODDateRequired = rsPODetail!fldPODDateRequired
        rsPODetailNew!fldPODQuantityOrdered =
rsPODetail!fldPODQuantityOrdered
        rsPODetailNew!fldPODQuantityReceived =
rsPODetail!fldPODQuantityReceived
        rsPODetailNew!fldPODTaxable = rsPODetail!fldPODTaxable
        rsPODetailNew!fldPODPosted = rsPODetail!fldPODPosted
        rsPODetailNew!fldPODRevision = rsPODetail!fldPODRevision
        rsPODetailNew.Update

and the offending line is rsPODetailNew!fldPODRevision =
rsPODetail!fldPODRevision.  Where fldPOHID is the FK to the Header.

Both rsPODetailNew!fldPODRevision and  rsPODetail!fldPODRevision show as not
being in the collection when I hover over them with the pointer.  So it's
common to both recordsets.

The two recordsets are opened thusly:

Set rsPODetail = db.OpenRecordset("Select * FROM tblPODetail WHERE fldPOHID
= " & Me.fldPOHID) Set rsPODetailNew = db.OpenRecordset("Select * FROM
tblPODetail")

where Me.fldPOHID is the PK of the PO to be copied.

Very straightforward.

The Field fldPODRevision is in the table - I did a cut and poaste to avoid
typos.  All the other fields copy fine.  When I comment out the line with
the problem, the PO copies perfectly. 

Usually this kind of intractable problem can be solved by writing out the
description to the list and in the process I see where the error is and
never send the email.  In this case, here I am at the end of the post and
still can't see why that one field should no be in the collection.

Ay speculations welcome.

MTIA

Rocky Smolin

Beach Access Software

858-259-4334


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com