'Item not found in this collection' error ( recordset ) /

[liamfitz]

Here's my code ...

If Me.cboRefSource.Text = "" Then
    frm_close
Else
    With Forms!frmAfAISLIVE!sfrmReferrals.Form.Recordset
        .MoveLast
        .Edit
        !Referral_Source_ID = Me.cboRefSource.Value
        .Update
    End With
End If

When it gets to the line, '!Referral_Source_ID = Me.cboRefSource.Value' It halts with Run-time error '3265' - 'Item Not found in this collection' i.e. Referral_Source_ID

But it definitely is ??? Anyone come across this before ?

 

[pbaldy]

Are there perhaps spaces instead of underscores in the field name?

 

[boblarson]

I'm pretty sure that Paul has the answer there.

I just did want to ask one thing. For your recordset are you using a query or SQL Statement with an ORDER BY clause? If not, you really need to because if you don't, the last record is going to essentially be meaningless as the data is not stored in any particular order (oh Access will somewhat try to rearrange it into PK order when compact and repair is run, but it won't last long). So the only way to know for certain that the right order is there is to use a query with an ORDER BY applied to it.

 

[liamfitz]

I'm using an SQL statement, and yes, it does contain an ORDER BY clause. I'll just check again, the exact spelling of the field name, ( but I don't think that's the issue ) thanks.

 

[liamfitz]

I should point out - that two other fields ( in similar Sub Proc's ) are generating the same error, during the .Edit and .Update routine. This might be significant, but I can't think why at the moment.

 

[boblarson]

Can you post the entire procedure here so we can see the rest of it?

 

[liamfitz]

Certainly. her it is.

Private Sub Form_Unload(Cancel As Integer)
Dim ctlSource As Control
Dim CurrentItem As Integer
Me.cboRefSource.SetFocus
If Me.cboRefSource.Text = "" Then
    frm_close
Else
    With Forms!frmAfAISLIVE!sfrmReferrals.Form.Recordset
        .MoveLast
        .Edit
        !Referral_Source_ID = Me.cboRefSource.Value
        .Update
    End With
End If
Me.cboProjects.SetFocus
If Me.cboProjects.Text = "" Then
    frm_close
Else
    With Forms!frmAfAISLIVE!sfrmReferrals.Form.Recordset
        .Edit
        !Project_ID = Me.cboProjects.Value
        .Update
    End With
End If
Set ctlSource = Me.lstIssue
For CurrentItem = 0 To Me.lstIssue.ListCount - 1
    If ctlSource.Selected(CurrentItem) = True Then
        ' Process selected values
        With Forms!frmAfAISLIVE!sfrmReferrals.Form.Recordset
            .Edit
            !Issue_ID = ctlSource.Column(0, CurrentItem)
            .Update
        End With
    End If
Next CurrentItem
End Sub

As you will probably 'glean' from the code, this Form_Unload event only occurs, once the required selections of values have been made ( 1 List Box and 2 Combo Boxes ), which in turn, are written to a recordset.

 

[boblarson]

So is this code in the Unload event of the form you are referring to? (Forms!frmAfAISLIVE!sfrmReferrals.Form.Recordset) or is that a different form completely? And if it is a different form, are these both subforms on the same parent form? Or what?
How does this all go together and what is it you are really trying to accomplish?

 

[liamfitz]

No. This code is in the Unload event of a form which was opened by the subform and its recordset referred to i.e. 'Forms!frmAfAISLIVE!sfrmReferrals.Form.Recordset'
here's the logic.
1. An 'After_Update' event of a field on the subform ( sfrmReferals ) opens a form.
2. This newly opened form has 3 boxes ( list and combos )
3. The unload event ( above code ) of this newly opened form, checks values have been entered for all 3 boxes, before it allows the user to close the form. That works fine.
4. 3 fields ( see code ) SHOWN on the original subform ( sfrmReferrals ) are then updated, based on these values.
5. The subform is then REQUERIED, now showing the 3 new values, that weren't there before ! Well that's the theory. Any ideas.

P.S. The subfrom recordset IS updateable ( it's not a composite recordset for example,which can cause difficulties updating ) In fact, I have another form which does EXACTLY the same i.e opens on the same event for a field, and on a user selected item in a combo box, updates the Recordset.

 

[liamfitz]

Fret not my erudite and helpful friend. I have seen what is causing this difficulty. The sub-routine which opens the form I refer to, also edits and updates a couple of fields, before doing so. I then set the recordsource AGAIN of my subform, and requery ( to reflect the update ) However, this SQL SELECT statement does NOT include the fields I am trying to edit/update subsequently - hence the 'Items Not found .... ' etc. Many thanks for your interest and support.