Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / February 2008

Tip: Looking for answers? Try searching our database.

Rerun an open query in Access 2007

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
DanR - 16 Jan 2008 17:57 GMT
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?

Signature

Dan R

Allen Browne - 17 Jan 2008 00:32 GMT
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/clintcovington/archive/2006/12/08/msdn-article-on-creating
-custom-ribbons-in-access-2007.aspx

   http://accessjunkie.com/faq_30.aspx

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi,
>
[quoted text clipped - 9 lines]
> Is there any way in Access 2007 to place a "RUN button" on the "Home" tab
> toolbar?
DanR - 17 Jan 2008 01:49 GMT
Allen,

Thanks for the helpful information, including the links.

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

Dan

Signature

Dan R

> Changing the ribbon is quite a bit more work, but it's easy to customize the
> Quick Access Toolbar.
[quoted text clipped - 17 lines]
> > Is there any way in Access 2007 to place a "RUN button" on the "Home" tab
> > toolbar?
DanR - 05 Feb 2008 21:19 GMT
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
Signature

Dan R

> Changing the ribbon is quite a bit more work, but it's easy to customize the
> Quick Access Toolbar.
[quoted text clipped - 17 lines]
> > Is there any way in Access 2007 to place a "RUN button" on the "Home" tab
> > toolbar?
Allen Browne - 06 Feb 2008 12:23 GMT
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.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen,
>
[quoted text clipped - 60 lines]
>> > tab
>> > toolbar?
DanR - 06 Feb 2008 20:22 GMT
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...

Signature

Dan R

> Sorry, Dan: I can't tell you specifics like that.
>
[quoted text clipped - 68 lines]
> >> > tab
> >> > toolbar?
DanR - 06 Feb 2008 22:14 GMT
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.

Signature

Dan R

> Hi, Allen.
>
[quoted text clipped - 119 lines]
> > >> > tab
> > >> > toolbar?
DanR - 06 Feb 2008 22:33 GMT
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

Signature

Dan R

> 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
[quoted text clipped - 130 lines]
> > > >> > tab
> > > >> > toolbar?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.