Max Wanadoo
max.wanadoo at gmail.com
Fri Oct 9 01:31:12 CDT 2009
> 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