[AccessD] Recordset based on query

David,

Use a local variable.
     Dim rsPopulateQtyBoxes As Recordset
     Set rsPopulateQtyBoxes = New ADODB.Recordset
    Dim sql as string

    sql = "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;"

   debug.print sql
   'This will show what is wrong with your statement, copy and paste
result to a query window
   'if you cant see the problem.

     rsPopulateQtyBoxes.Open sql, , adOpenKeyset, adLockOptimistic,
adCmdUnknown
 
cheers

Michael M

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David &
Joanne Gould
Sent: Tuesday, 27 June 2006 10:50 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Recordset based on query

Charlotte

I have changed the code to:



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!Rent
>a
>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]![cboSelectMemb
>e
>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


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com