[AccessD] Recordset based on query

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