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

Kamis, 06 Oktober 2011

RE: [MS_AccessPros] Item not found in this collection

Connie-

You did a SELECT *, so you will get LookingID from BOTH tables. Rst!LookingID
doesn't exist, but both Rst![LookingContacts.LookingID] and
Rst![LookingContacts.LookingID] do!

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
Sent: Thursday, October 06, 2011 9:50 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [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."

------------------------------------

Yahoo! Groups Links