[AccessD] It's About Time I Learned This

Stuart:

I'm not sure that buys me anything because you've got the SQL in the code
and you're updating the stored query with that code, modified with the
desired parameter.  

What I've got is SQL statements that run to 15 or 20 lines.  I'm pretty fast
at the copy and paste and merging the parameters into the SQL.  Once the SQL
is done, I can just set the RecordSource of the form or report to that. 

But fiddling with those lines and lines of SQL is what I'm trying to get
around.

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Thursday, October 08, 2009 2:18 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] It's About Time I Learned This

This was covered in the thread "Access 2000 - Filtering SQL Server records"
on 17 Sep 2009.  We were actaully talking about editing "pass through"
queries, but the prinicple is the same.

Here's what I posted then.

<quote>
To pass a parameter, you need to modify the querydef, so you need to do
something like this before opening the query:

Public Sub SetChequePKID(PKID As Long)
   Dim strSQL As String
   Dim qdf as DAO.QueryDef
   Dim rs as DAO.Recordset
   strSQL = "Select Cast(chequenum as int) from tblCheques where PKID" &
   PKID 
    Set qdf = CurrentDb.QueryDefs("qryGetCheckNumber") 
    qdf.SQL = strSQL 
    qdf.Close
End Sub
</quote>


On 8 Oct 2009 at 13:42, Rocky Smolin wrote:

> Dear List:
> 
> When I have a query that is the source of a form, report or recordset 
> that needs to have criteria of parameters, I always go to the SQL view 
> of the query, copy out the code, paste it into the CBF, and add the 
> parameters there - values from a text box or combo box, etc.  Always done
it that way.
> 
> But I think there's a way to do modify a stored query in code using 
> the querydef.  Pretty basic Access stuff, I think, but I've never learned
it.
> Can someone point me to a tutorial or primer or help screen explaining
this?
> 
> MTIA
> 
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
> www.bchacc.com
>  
>  
> 
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com


Stuart McLachlan

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