[AccessD] Recordset based on query

Charlotte

I have changed the code to:

     Dim rsPopulateQtyBoxes As Recordset
     Set rsPopulateQtyBoxes = New ADODB.Recordset

     rsPopulateQtyBoxes.Open "SELECT tblHires.HireID, tblHires.member, 
tblHires.title, tblHires.outDate," _
     & "tblHires.dueDate, tblHires.over, tblHires.RentalFee, 
tblTitles.name, IIf((Date()-tblHires!dueDate)>1," _
     & "(Date()-tblHires!dueDate)*tblHires!RentalFee,0) AS ChargesDue, 
tblHires.DateReturned, tblHires.Renew" _
     & "FROM tblTitles INNER JOIN tblHires ON tblTitles.tapeNumber = 
tblHires.title" _
     & "WHERE ((tblHires.DateReturned) Is Null) And ((tblHires.Renew) = 
True))" _
     & "ORDER BY tblHires.outDate DESC;", , adOpenKeyset, adLockOptimistic, 
adCmdUnknown

Leaving out all conditions referring to the form (if I understood you 
correctly) but I still get the same error message. What am I missing?

TIA

David


At 08:54 AM 27/06/2006, you wrote:
>ADO also doesn't know about Access forms, so you can't use them as
>criteria like this.  You'll have to build the SQL and concatenate the
>value from the control with any necessary delimiters instead of letting
>the query engine handle it.
>
>
>Charlotte Foust
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David &
>Joanne Gould
>Sent: Monday, June 26, 2006 3:37 PM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] Recordset based on query
>
>Thanks, Michael
>
>I have tried that. The query is this -
>
>SELECT tblHires.HireID, tblHires.member, tblHires.title,
>tblHires.outDate, tblHires.dueDate, tblHires.over, tblHires.RentalFee,
>tblTitles.name,
>IIf((Date()-tblHires!dueDate)>1,(Date()-tblHires!dueDate)*tblHires!Renta
>lFee,0)
>AS ChargesDue, tblHires.DateReturned, tblHires.Renew FROM tblTitles
>INNER JOIN tblHires ON tblTitles.tapeNumber = tblHires.title WHERE
>(((tblHires.member)=[Forms]![frmHire_New_Members_Extend]![cboSelectMembe
>r])
>AND ((tblHires.DateReturned) Is Null) AND ((tblHires.Renew)=True)) ORDER
>BY tblHires.outDate DESC;
>
>I put the code in as:
>
>      Dim rsPopulateQtyBoxes As Recordset
>      Set rsPopulateQtyBoxes = New ADODB.Recordset
>
>      rsPopulateQtyBoxes.Open "SELECT tblHires.HireID, tblHires.member,
>tblHires.title, tblHires.outDate," _
>      & "tblHires.dueDate, tblHires.over, tblHires.RentalFee,
>tblTitles.name, IIf((Date()-tblHires!dueDate)>1," _
>      & "(Date()-tblHires!dueDate)*tblHires!RentalFee,0) AS ChargesDue,
>tblHires.DateReturned, tblHires.Renew" _
>      & "FROM tblTitles INNER JOIN tblHires ON tblTitles.tapeNumber =
>tblHires.title" _
>      & "WHERE (((tblHires.member) =
>[Forms]![frmHire_New_Members_Extend]![cboSelectMember]) And" _
>      & "((tblHires.DateReturned) Is Null) And ((tblHires.Renew) =
>True))" _
>      & "ORDER BY tblHires.outDate DESC;", , adOpenKeyset,
>adLockOptimistic, adCmdUnknown
>
>When I tried to run it I got this message:
>      Runtime Error '3709';
>       The connection cannot be used to perform this operation. It is
>either closed or invalid in this contect.
>
>I even tried splitting each part of the query into separate sections,
>but that didn't help either. If anyone can explain what I have done
>wrong and/or how I can fix it so I can cycle through the query recordset
>I would be very thankful.
>
>TIA
>
>David
>
>At 05:22 PM 26/06/2006, you wrote:
> >David,
> >
> >I don't think ADO knows about saved queries.
> >I think you need to pass the sql string as the argument.
> >
> >I could well be wrong...
> >
> >cheers
> >
> >
> >Michael M
> >
> >-----Original Message-----
> >From: accessd-bounces at databaseadvisors.com
> >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David &
> >Joanne Gould
> >Sent: Monday, 26 June 2006 4:21 PM
> >To: Access Developers discussion and problem solving
> >Subject: [AccessD] Recordset based on query
> >
> >I keep thinking this should be fairly simple.
> >
> >I need to check through the results in a query and calculate results
> >from them. The code I am using starts with:
> >
> >Dim rsPopulateQtyBoxes As Recordset
> >      Set rsPopulateQtyBoxes = New ADODB.Recordset
> >      rsPopulateQtyBoxes.ActiveConnection = CurrentProject.Connection
> >
> >      rsPopulateQtyBoxes.Open qryHire_New_Rentals_Extend, ,
> >adOpenKeyset, adLockOptimistic, adCmdUnknown
> >
> >When I try to run the code I get this error message:
> >
> >Thanks in advance for any help offered
> >
> >David
> >
> >--
> >AccessD mailing list
> >AccessD at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/accessd
> >Website: http://www.databaseadvisors.com
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com