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

Kamis, 06 Oktober 2011

[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
> >
>