ACCESS FEED: RE: [MS_AccessPros] ParseSQL question

Senin, 19 Agustus 2013

RE: [MS_AccessPros] ParseSQL question

Here is the main function that changes the SQL of a querydef. Note there is no error handling in the event the query name doesn't exist:

Function fChangeSQL(pstrQueryName As String, strSQL As String) As String
'=============================================================
' basQueryDefs.ChangeSQL
'----------------------------------------------------------
' Purpose : update the SQL property of a saved query
' Copyright: Duane Hookom
' Author : Duane Hookom
' Notes :
'----------------------------------------------------------
' Parameters
'-----------
' pstrQueryName (String) Name of saved query
' strSQL (String) SQL Statement
'----------------------------------------------------------
' Returns: the previous SQL statement
'----------------------------------------------------------
' Revision History
'----------------------------------------------------------
' 07-09-2001 DKH:
'=============================================================
' End Code Header block
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(pstrQueryName)
fChangeSQL = qd.SQL
qd.SQL = strSQL
Set qd = Nothing
Set db = Nothing
End Function

Duane Hookom MVP
MS Access

----------------------------------------
> To: MS_Access_Professionals@yahoogroups.com
> From: liz_ravenwood@beaerospace.com
> Date: Mon, 19 Aug 2013 16:06:39 +0000
> Subject: RE: [MS_AccessPros] ParseSQL question
>
> Hey Duane, I'm interested in seeing what you have, but when I go out to the web site, the code doesn't have any carriage returns so it stretches loooooonnnnnn ways. :-)
>
> Respectfully,
> Liz Ravenwood
> Programmer / Analyst
> B/E Aerospace | Super First Class Environments
>
> 1851 S Pantano Road | Tucson, Arizona 85710
> Office +1.520.239.4808 |
> beaerospace.com
> Passion to Innovate. Power to Deliver
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane Hookom
> Sent: Sunday, August 18, 2013 7:40 PM
> To: Access Professionals Yahoo Group
> Subject: RE: [MS_AccessPros] ParseSQL question
>
> I use a generic function for changing the SQL of saved queries http://www.tek-tips.com/faqs.cfm?fid=7433.
>
> Duane Hookom MVP
> MS Access
>
> ----------------------------------------
>> To: MS_Access_Professionals@yahoogroups.com
>> From: desertscroller@cox.net
>> Date: Mon, 19 Aug 2013 02:04:56 +0000
>> Subject: Re: [MS_AccessPros] ParseSQL question
>>
>> You are right, I was trying to reuse a defined query. I think I will just in-code the queries into the code. thanks for the reality check.
>> Rod
>>
>> --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>>>
>>> Rod,
>>> I believe all you need to do is change the the Row Source property of the combo box. There is no need to change querydefs. For instance, if the first combo box selects a numeric product category ID you could add code to the after update event of the combo box like:
>>>
>>> Dim strSQL as String
>>> strSQL = "SELECT ProductID, ProductName " & _ "FROM Products WHERE
>>> CategoryID = " & Me.cboProdCategory & _ " ORDER BY ProductName"
>>> Me.cboProduct.RowSource = strSQL
>>>
>>> Duane Hookom, MVP
>>> MS Access
>>>
>>> ----------------------------------------
>>>> To: MS_Access_Professionals@yahoogroups.com
>>>> From: desertscroller@...
>>>> Date: Mon, 19 Aug 2013 00:39:28 +0000
>>>> Subject: [MS_AccessPros] ParseSQL question
>>>>
>>>> I have been trying to redefine the data source for a combobox based
>>>> on the selection from either a checkbox or another combobox. Reading
>>>> the queryDef members of the database makes a lot of sense. However,
>>>> the examples I have found make a call to a subroutine which I can not seem to find. The call is to a procedure Call ParseSQL(strSQL, strSELECT, strWhere, strOrderBy, _ strGROUPBY, strHAVING) which breaks the SQL statement down into the various segments. I know I can development something similar but thought since Microsoft was using it in there literature that it might already exist.
>>>>
>>>> Does anyone know if it does or should I bite the bullet? Just trying to change the WHERE clause for filtering.
>>>>
>>>> Rod
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> ------------------------------------
>>>>
>>>> Yahoo! Groups Links
>>>>
>>>>
>>>>
>>>
>>
>>
>>
>>
>> ------------------------------------
>>
>> Yahoo! Groups Links
>>
>>
>>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
>
>
> This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>