Prev: can I make access remember a last unbound number
Next: two linked subforms on an unbound main form
From: Dirk Goldgar on 8 Dec 2009 11:41
"Banana" <Banana(a)Republic> wrote in message
news:4B1E6B82.9000905(a)Republic...
> Jeff Freilich wrote:
>> Hello and thanks for the reply
>>
>> I know that you cannot actually have a query with an SQL statement -
>> but what I am trying to emulate it if you open a query in the query
>> editor = go to SQL view and delete all the SQL - when you go back to
>> the Query Editor you get an empty Editor (and then if you go back to
>> the SQL view you see: "SELECT;"
>>
>> Is that possible to emulate or is that just a feature/function of
>> Acces that puts the "SELECT;" back in to what appears to be an empty
>> SQL statement?
>>
>> Thanks
>>
>> Jeff
>
> This?
>
> http://www.accessruncommand.com/codeex/184.htm


I find that the following code, adapted from the above, works:

Sub EditQueryClearSQL(strQuery As String)
On Error Resume Next
DoCmd.OpenQuery strQuery, acViewDesign
DoCmd.RunCommand acCmdSQLView
DoCmd.SelectObject acQuery, strQuery
DoCmd.RunCommand acCmdSelectAll
DoCmd.RunCommand acCmdDelete
DoCmd.RunCommand acCmdDesignView
End Sub

However, I think it's rather chancy, because it relies on nothing else
getting the focus between the statements.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

From: Jeff Freilich on 8 Dec 2009 13:36
Hi Dirk

Thank you very much - that actually did the trick

And yes I agree it is a little chancy in that it has to keep the focus
on the query during the statements - but that is ok as this is the
only thing the user will be able to do at that moment - or I will lock
the user out from being able to do anything else

Thanks again to all who commented - very much appreciated

Jeff
From: Banana on 8 Dec 2009 15:04
Dirk Goldgar wrote:
> "Banana" <Banana(a)Republic> wrote in message
>> http://www.accessruncommand.com/codeex/184.htm
>
>
> I find that the following code, adapted from the above, works:
>
> Sub EditQueryClearSQL(strQuery As String)
> On Error Resume Next
> DoCmd.OpenQuery strQuery, acViewDesign
> DoCmd.RunCommand acCmdSQLView
> DoCmd.SelectObject acQuery, strQuery
> DoCmd.RunCommand acCmdSelectAll
> DoCmd.RunCommand acCmdDelete
> DoCmd.RunCommand acCmdDesignView
> End Sub
>
> However, I think it's rather chancy, because it relies on nothing else
> getting the focus between the statements.
>

I wonder if we inserted Echo on/off between the statements would help
matters any?
From: Dirk Goldgar on 8 Dec 2009 15:43
"Banana" <Banana(a)Republic> wrote in message
news:4B1EB143.6050303(a)Republic...
> Dirk Goldgar wrote:
>> "Banana" <Banana(a)Republic> wrote in message
>>> http://www.accessruncommand.com/codeex/184.htm
>>
>>
>> I find that the following code, adapted from the above, works:
>>
>> Sub EditQueryClearSQL(strQuery As String)
>> On Error Resume Next
>> DoCmd.OpenQuery strQuery, acViewDesign
>> DoCmd.RunCommand acCmdSQLView
>> DoCmd.SelectObject acQuery, strQuery
>> DoCmd.RunCommand acCmdSelectAll
>> DoCmd.RunCommand acCmdDelete
>> DoCmd.RunCommand acCmdDesignView
>> End Sub
>>
>> However, I think it's rather chancy, because it relies on nothing else
>> getting the focus between the statements.
>>
>
> I wonder if we inserted Echo on/off between the statements would help
> matters any?


The original code you linked to had Echo Off at the beginning, and Echo On
at the end (after "RunCommad acCmdSQL View"). But I found in testing that
the statements I added didn't work unless I removed the Echo Off. I tried a
couple of variations with no success, and then gave up.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

From: Banana on 8 Dec 2009 16:15
Dirk Goldgar wrote:
> The original code you linked to had Echo Off at the beginning, and Echo
> On at the end (after "RunCommad acCmdSQL View"). But I found in testing
> that the statements I added didn't work unless I removed the Echo Off.
> I tried a couple of variations with no success, and then gave up.
>

Ah, I see. Thinking about it bit, it does makes sense- Echo disables the
screen updating but we're replicating the UI functionality which of
course works through the screen (more or less).

I tried something and this seems to work here:

Sub EditQueryClearSQL(strQuery As String)
On Error Resume Next
Echo False
DoCmd.OpenQuery strQuery, acViewDesign
DoCmd.RunCommand acCmdSQLView
DoCmd.RunCommand acCmdDelete
Echo True
End Sub


Basically, I removed the RunCommand selecting the object & text because
by default, Access already has the SQL highlighted when you open it in
SQL View so we can just go directly to acCmdDelete.

Of course, this may be just fragility in different way as I don't know
for sure if Access _always_ highlight SQL text when we switch to SQL
view or whether this could be changed via Options and thus breaking the
code above.

BTW, the minimum acceptable SQL:

SELECT 1;


I used to have a series of template queries in one of my project where I
set several of queries' properties and re-used the template to re-crate
certain kind of query (e.g. a template for a combobox's rowsource, a
template for a form's recordsource, a template for ODBC nonscalar query
and so on) though I never tried to automate this as OP was wanting to.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: can I make access remember a last unbound number
Next: two linked subforms on an unbound main form