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

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