[AccessD] Recordset based on query

David & Joanne Gould dajomigo at tpg.com.au
Mon Jun 26 23:22:31 CDT 2006


Michael

I got the same result when I did this and no response to the debug.print. 
Is there a limit to the length of an sql string and if so is this too long 
and therefore causing the error?

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





More information about the AccessD mailing list