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

Senin, 10 Oktober 2011

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

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