First Published 31 July 2023
It is often very useful to change query views using code.
For example, when opening a query from the navigation pane or using code, it would often be useful to go directly to SQL view.
However, Access only allows you to open the query in datasheet view or design view.
Navigation Pane Context Menu
|
Once the query is open in design view, you can then switch to SQL view e.g. from the ribbon or a right click context menu. . .
Ribbon Menu
|
Query Design View Context menu
|
However, doing that repeatedly soon becomes annoying and has been a long term irritation of Access for me
Luckily, it is easy to do this in one step by utilising the command bars used in the various query view right click shortcut menus
The only problem is knowing the correct code to use in each case. This article provides all you need to know!
1. Design view to SQL view
CommandBars("Query").Controls(1).Execute
Typical Usage:
'open a selected query in design view
DoCmd.OpenQuery "YourQueryNameHere", acViewDesign
'change to SQL view
CommandBars("Query").Controls(1).Execute
2. Design view to Datasheet view
CommandBars("Query").Controls(2).Execute
How does the code work?
a) The command bars named in each code line above are the name of the built-in right click context menu - in this case 'Query'
b) The Controls.(Number).Execute tells Access to run the numbered item in that menu: (1) = SQL view; (2) = Datasheet view
Similar code works for the other view changes using the relevant CommandBars context menu:
3. Datasheet to design view
CommandBars("Query Design Datasheet").Controls(1).Execute
4. Datasheet to SQL view
CommandBars("Query Design Datasheet").Controls(2).Execute
5. SQL view to design view
CommandBars("Query SQLTitleBar").Controls(1).Execute
6. SQL view to datasheet view
CommandBars("Query SQLTitleBar").Controls(2).Execute
I use all the above code in several of my applications including the Query Metadata Viewer
This allows you to select any of the 3 views for selected queries at the click of a button:
You can even use code to toggle through the various views in sequence
CODE:
Sub QueryViewLoop()
Dim i As Integer
For i = 1 To 2
'loop twice though query views at 3 second intervals
'open in design view and wait 3 seconds
DoCmd.OpenQuery "YourQueryNameHere", acViewDesign
DoEvents
Wait 3
'change to SQL view and wait 3 seconds
CommandBars("Query").Controls(1).Execute
DoEvents
Wait 3
'change to datasheet view and wait 3 seconds
CommandBars("Query SQLTitleBar").Controls(2).Execute
DoEvents
Wait 3
Next
'close without changing the saved view
DoCmd.Close acQuery, "YourQueryNameHere", acSaveNo
End Sub
You also need to save the following code in a standard module to set the wait time in seconds
Option Compare Database
Option Explicit
'========================================
'API declarations
#If VBA7 Then 'A2010 or later (32/64-bit)
Declare PtrSafe Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#Else 'A2007 or earlier
Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#End If
'========================================
Sub Wait(N As Integer)
'creates a delay while other code executes
'N = number of seconds to wait
Dim i As Integer
'loop using sleep function from api library file
For i = 1 To N
'pause 1 second (1000 milliseconds)
Sleep 1000
Next
DoEvents
End Sub
Feedback
Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 31 July 2023
|
Return to Code Samples Page
|
Return to Top
|