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

Thanks John. The event where a person is in more than one Looking Event is so rare, that I decided to do it a different way. Open up a report that shows what Looking Events are hooked to the Contact. Then have a button to open the Contact form

Thanks for ALL you help!
Connie

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Connie-
>
> Is there a combo box on the Looking Event form that filters on LookingID? It
> could be a timing problem, so try putting a DoEvents right after you open the
> form object to give it a chance to fully load. As for your Object Required
> error, that probably happens because of one of the errors - your variables are
> getting reset during the error.
>
> 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: Monday, October 10, 2011 11:20 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Item not found in this collection
>
> John,
>
> The record source for the LookingEvent form is SELECT LookingEvent.*
> FROM LookingEvent;
>
> The LookingEvent form has two subforms each having the LookingID as the Master
> and Child.
>
> The LookingEvent's Load event tells it to go to a new record.
>
> Doubt that all the above is necessary but just in case ....
>
> Thanks again!
> Connie
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
>
> > 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
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>