[AccessD] It's About Time I Learned This

>   strSQL = "Select Cast(chequenum as int) from tblCheques where PKID" &
   PKID

Rocky, I think you are missing the point here.  The above line is where you
would take YOUR parameters from your combo boxes, list selections,  whatever
and build the above line to suit your needs.  The rest of the code
before/after is  what updates the query automatically  for you. No need to
cut-n-paste etc.

I do similar  things tons  of  times.

Ie, grab the current sql from the query using QueryDef.  Amend it based on
the  selection forms that my users are presented with (typically building a
filter for  viewing only the records they want to see), and then write it
back  to the query which the form is based on and requery the  form.

As a developer, this is all left to the user to change  the underlying query
from a simple  selection form.  Same for reports etc.

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: 09 October 2009 03:18
To: 'Access Developers discussion and problem solving'
Subject: Re: [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

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