David & Joanne Gould
dajomigo at tpg.com.au
Mon Jun 26 21:23:23 CDT 2006
Thanks, Michael I'll try that David At 11:54 AM 27/06/2006, you wrote: >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 >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com