Rerun an open query in Access 2007

[DanR]

Hi,

In Access 2003 I was able to place the "exclamation point" RUN button on my
Access toolbar so that I could easily re-run a query that I already had open
(rather than try to find it in a list of a few hundred queries). The queries
I run usually ask for user input, so by re-running them I am able to give
different input.

Is there any way in Access 2007 to place a "RUN button" on the "Home" tab
toolbar?

 

[Allen Browne]

Changing the ribbon is quite a bit more work, but it's easy to customize the
Quick Access Toolbar.

If you really want to customize the ribbon, here's some leads:
http://www.accessribbon.de/en/?Access_-_Ribbons
http://blogs.msdn.com/clintcovingto...n-creating-custom-ribbons-in-access-2007.aspx
http://accessjunkie.com/faq_30.aspx

 

[DanR]

Allen,

Thanks for the helpful information, including the links.

I think they are enough to help me plan my next step.

Dan

 

[DanR]

Allen,

I read the articles to which you referred, and now am able to have a new Tab
on the Access Ribbon using the XML shown here:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="false">
<tabs>
<tab id="dbCustomTab" label="RK Custom Tab" visible="true">
<group id="grpReRun" label="ReRun">
<button id="cmdQueryRunQuery" label="Run" imageMso="QueryRunQuery"
size="large" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

However, I don't know what events are available for "onAction" to tell
Access to re-run the Query that is "on top".

Do you happen to know what the "onAction" name is that Access 2007 uses for
"Run Active Query"?

I thought that the "Run" button would, by default, perform its usual
function, unless I specified some other function. However, nothing seems to
happen when I press it.

Your help is appreciated.

Thanks.

Dan

 

[Allen Browne]

Sorry, Dan: I can't tell you specifics like that.

I have found that not all buttons work in all contexts.

Beyond that, the links I gave are the best leads I can suggest.

Or perhaps someone else will jump in who has attempted that.

 

[DanR]

Hi, Allen.

I think I'm making progress. I learned how I can get the name of the Active
Datasheet, so I wrote the small VBA function. Now I just have to figure out
how I can get the function to be called when the user presses the "ReRun"
button. For the button I have set

onAction="=modRerunActiveQuery.RerunActiveQuery"

where "modRerunActiveQuery" is the VBA module in which I have placed the
following Function:

************ Begin Function **********

Option Compare Database

Function RerunActiveQuery()
' This procedure will re-run the query associated
' with the Active Datasheet.
' DGR 2008 02 06

Dim objDatasheet As Object
Dim strActiveQuery As String
Const conNoActiveDatasheet = 2484

On Error GoTo RerunQuery_Err

strActiveQuery = Screen.ActiveDatasheet.Name
DoCmd.OpenQuery strActiveQuery, acViewLayout, acReadOnly

RerunActiveQuery_Bye:
Exit Function
RerunQuery_Err:
If Err = conNoActiveDatasheet Then
MsgBox "No data sheet is active.", vbExclamation
Resume RerunActiveQuery_Bye
End If

End Function

************* End Function ************

I seem to be getting an error that it cannot find the Function.

The error message is:
Microsoft Office Access can't find the name "modRerunActiveQuery" you
entered in the expression. You may have specified a control that wasn't on
the current object without specifying the correct form or report context.

Now I'll try to figure out how to get around this...

 

[DanR]

I also learned how to put my new button on the "Home" tab provided by Access
(rather than create my own tab). I learned this from website

http://www.accessribbon.de/en/index.php?FAQ:9

I'm still trying to figure out how to get my code to do what I want. I got
rid of the "cannot find" the onAction VBA code by using VBA : Tools :
References and checking the box for "Microsoft Office 12.0 Object Library."

I'll try to keep this thread updated with my progress.

 

[DanR]

Ahhhh, sweet success!

Here is the XML code I finally ended up using, to put the ReRun button on
the Home tab:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="false">
<tabs>
<tab idMso="TabHomeAccess">
<group id="grpReRun" label="ReRun">
<button id="cmdQueryRunQuery" label="Run" imageMso="QueryRunQuery"
size="large" onAction="RerunActiveQuery" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

And here is the VBA script I needed to close the Active Query and rerun it:

Option Compare Database

Public Sub RerunActiveQuery(ctl As IRibbonControl)
' This procedure will re-run the query associated
' with the Active Datasheet.

Dim objDatasheet As Object
Dim strActiveQuery As String
Const conNoActiveDatasheet = 2484

On Error GoTo RerunQuery_Err

strActiveQuery = Screen.ActiveDatasheet.Name
' Close active window
DoCmd.Close acQuery, strActiveQuery, acSavePrompt
DoCmd.OpenQuery strActiveQuery, acViewNormal, acEdit

RerunActiveQuery_Bye:
Exit Sub
RerunQuery_Err:
If Err = conNoActiveDatasheet Then
MsgBox "No data sheet is active.", vbExclamation
Resume RerunActiveQuery_Bye
End If

End Sub