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

Kamis, 06 Oktober 2011

RE: [MS_AccessPros] Re: Item not found in this collection

Connie-

What's the Record Source of the LookingEvent form? It sounds like there's an
unresolved parameter reference somewhere, but why the query would be referring
to the form - which isn't open yet - is beyond me.

It also sounds like the form failed to open, so the frm object is probably
empty.

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 11:07 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Item not found in this collection

Forgot to include changed code.

'Loop through in order to find all instances of LookingID's with no end date in
LookingContacts for this Contact

' 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![LookingEvent.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.
clnLookingEvent.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

--- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@...> wrote:
>
> That did the trick! Now the next bunch of msgs.
> Enter Parameter: Forms!LookingEvent!LookingID
> I have to click cancel several times. After clicking Cancel the first time
the first form in the collection shows. After several clicks the next msg
comes:
> Error 424 "Object Required".
> Debugging indicates the line:
> clnLookingEvent.Add Item:=frm, Key:=CStr(frm.Hwnd)
> And when I hover over the page "Nothing = Nothing" shows in pink.
>
> Any thoughts?
> Thanks a million,
> Connie
>
> ps. Paris and sipping wine. Sounds delightful!
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > 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
> >
>

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

Yahoo! Groups Links