ACCESS FEED: [MS_AccessPros] Item not found in this collection

Kamis, 06 Oktober 2011

[MS_AccessPros] Item not found in this collection

Hi all!

The plan is to open a collection by looping through LookingContacts looking for a certain ContactID and its related LookingID, and when a ContactID is found opening the Form LookingEvent to the record with the related LookingID.

I am getting the error "Item not found in this collection."

Debugging shows the problem is the line which I've marked with ****: lngLookingID = RstL!LookingID

Thanks!
Connie

' Point to this database
Set db = CurrentDb
'Create a recordset from Looking Contacts joined with LookingEvent where the EndDate is Null and the ContactID = this contactID
Set RstL = db.OpenRecordset("SELECT * " & _
"FROM LookingEvent INNER JOIN LookingContacts ON LookingEvent.LookingID = LookingContacts.LookingID " & _
"WHERE LookingEvent.EndDate Is Null And ContactID = " & Me.ContactID)

' Check for a good record
If Not RstL.EOF Then
'Loop through the matching records until reach end of records opening separate instances of the looking Event
Do While Not RstL.EOF
'Set current LookingID to string
*****lngLookingID = RstL!LookingID
'Open a collection of instances of LookingEvent forms
'Open a new instance, show it, set a caption, and go to contact
Set frm = New Form_LookingEvent
frm.Visible = True
frm.Caption = frm.Hwnd & ", opened " & Now()
frm.Recordset.FindFirst "LookingID = " & lngLookingID
'Append it to our collection.
clnLooking.Add Item:=frm, Key:=CStr(frm.Hwnd)
Set frm = Nothing
'Move to next record in new recordset with lookingevent
If Not rstI.EOF Then
rstI.MoveNext
End If
Loop
End If
' Clean up
RstL.Close
Set RstL = Nothing
Set db = Nothing

MsgBox "If LookingEvent opens and the set of buyers are no longer looking, enter END DATE = date it sold."