Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.
Solved
Opening Queries using the "Run Code" in Switchboard.
Posted on 2004-09-09
Firstly, I understand that the Switchboard feature is the work of the devil and should be avoided, but I am helping out a client and I can't redo all his Switchboard menus for him or ask my client to scrap them.
However, on the positive side the Switchboard menus do seem quite stable in this database.
Anyway, my client wants to be able to use Switchboard to open queries directly, Switchboard has a Open Report and Open Form feature but not Open Query.
I've decided to use the Run Code feature of Switchboard, however despite my best efforts I can't get it to work.
Here's what I did. I added a new Module to the database, called CustomMod.
In CustomMod I added the subroutine:
Public Sub OpenQuery(strQuery As String)
If (TypeName(CurrentData.AllQ
MsgBox "Query Doesn't Exist: " & strQuery
Else
DoCmd.OpenQuery strQuery
End If
End Sub
Then I added a new Switchboard menu item that runs the exact code:
OpenQuery "Test"
But when activated that gives the error "There was an error executing the command.". What gives??? Running a sub without a parameter works fine.
BTW I am doing this all on Access 2000.
PLEASE HELP !!!
However, on the positive side the Switchboard menus do seem quite stable in this database.
Anyway, my client wants to be able to use Switchboard to open queries directly, Switchboard has a Open Report and Open Form feature but not Open Query.
I've decided to use the Run Code feature of Switchboard, however despite my best efforts I can't get it to work.
Here's what I did. I added a new Module to the database, called CustomMod.
In CustomMod I added the subroutine:
Public Sub OpenQuery(strQuery As String)
If (TypeName(CurrentData.AllQ
MsgBox "Query Doesn't Exist: " & strQuery
Else
DoCmd.OpenQuery strQuery
End If
End Sub
Then I added a new Switchboard menu item that runs the exact code:
OpenQuery "Test"
But when activated that gives the error "There was an error executing the command.". What gives??? Running a sub without a parameter works fine.
BTW I am doing this all on Access 2000.
PLEASE HELP !!!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3-
3
- +4
11 Comments
Active today
Your code should work ... assuming the name is correct, and the query returns records.
What line does your code error on?
What line does your code error on?
Hi
The problem is that if the query does not exist then CurrentData.AllQueries(str
try this instead. The code captures the error and displays a message to the user if the query does not exist or opens the query if it does
Public Sub OpenQuery(strQuery As String)
On Error GoTo err_handler
temp = CurrentData.AllQueries(str
DoCmd.OpenQuery strQuery
Exit Sub
err_handler:
MsgBox "Query Doesn't Exist: " & strQuery
End Sub
HTH
Scott
The problem is that if the query does not exist then CurrentData.AllQueries(str
try this instead. The code captures the error and displays a message to the user if the query does not exist or opens the query if it does
Public Sub OpenQuery(strQuery As String)
On Error GoTo err_handler
temp = CurrentData.AllQueries(str
DoCmd.OpenQuery strQuery
Exit Sub
err_handler:
MsgBox "Query Doesn't Exist: " & strQuery
End Sub
HTH
Scott
You cannot use parameters in the switchboard...
Public Sub OpenQuery(strQuery As String)
If (TypeName(CurrentData.AllQ
MsgBox "Query Doesn't Exist: " & strQuery
Else
DoCmd.OpenQuery strQuery
End If
End Sub
Public Sub OpenQueryX()
OpenQuery "test"
End Sub
ANd you Switchboard menu get's the command
OpenQueryX
You might do the same using a macro instead of code, but the idea and limitations are the same
CHeers
Public Sub OpenQuery(strQuery As String)
If (TypeName(CurrentData.AllQ
MsgBox "Query Doesn't Exist: " & strQuery
Else
DoCmd.OpenQuery strQuery
End If
End Sub
Public Sub OpenQueryX()
OpenQuery "test"
End Sub
ANd you Switchboard menu get's the command
OpenQueryX
You might do the same using a macro instead of code, but the idea and limitations are the same
CHeers
Sorry Scott. Your code gives the same error.
To answer the first question. My code doesn't error on any line at all, it doesn't even try to run the code (i.e. if I put a breakpoint on the first line of the subroutine it doesn't break there at all.).
The error seems to be related to the Switchboard command...maybe Switchboard doesn't accept code with parameters, however this would be a fairly severe limitation.
If you have any other ideas about how I could run a query from Switchboard, let me know.
To answer the first question. My code doesn't error on any line at all, it doesn't even try to run the code (i.e. if I put a breakpoint on the first line of the subroutine it doesn't break there at all.).
The error seems to be related to the Switchboard command...maybe Switchboard doesn't accept code with parameters, however this would be a fairly severe limitation.
If you have any other ideas about how I could run a query from Switchboard, let me know.
Thanks AngelIII. I was hoping that wasn't the case, because the client will go bananas if he sees any code, and macros are just plain messy.
Is there any other suggestions to opening Queries within Switchboard ???
Is there any other suggestions to opening Queries within Switchboard ???
You'd have to modify the switchboard code to allow arguments to be passed. Have a look here:
http://www.experts-exchange.com/Databases/MS_Access/Q_21116625.html
http://www.experts-exchange.com/Databases/MS_Access/Q_21116625.html
nickwilton,
(Just to explain the reason behind why it won't work) The Switchboard uses the "Application.Run" method to run the command wich would require your command to be typed like this: "OpenQuery" , "YourQueryName" including the quotes, however when it gets the command from a variable (or recordset) it screws up the format and the run command cannot read it. If you wanted to have some fun you can add your own switchboard category for DoCmd.OpenQuery (I can give details). Otherwise I think you are stuck using a MACRO or hard coding the query name into your sub.
God bless!
Sam
(Just to explain the reason behind why it won't work) The Switchboard uses the "Application.Run" method to run the command wich would require your command to be typed like this: "OpenQuery" , "YourQueryName" including the quotes, however when it gets the command from a variable (or recordset) it screws up the format and the run command cannot read it. If you wanted to have some fun you can add your own switchboard category for DoCmd.OpenQuery (I can give details). Otherwise I think you are stuck using a MACRO or hard coding the query name into your sub.
God bless!
Sam
Here a possible suggestion to support 1 argument, based on the SPACE as separator
' Run code.
Case conCmdRunCode
If InStr(rs![Argument], " ") > 0 Then
Application.Run Left(rs![Argument], InStr(rs![Argument], " ") - 1), Mid(rs![Argument], 1 + InStr(rs![Argument], " "))
Else
Application.Run rs![Argument]
End If
The Switchboard menu item that runs the exact code (note to remove the double quotes)
OpenQuery Test
CHeers
' Run code.
Case conCmdRunCode
If InStr(rs![Argument], " ") > 0 Then
Application.Run Left(rs![Argument], InStr(rs![Argument], " ") - 1), Mid(rs![Argument], 1 + InStr(rs![Argument], " "))
Else
Application.Run rs![Argument]
End If
The Switchboard menu item that runs the exact code (note to remove the double quotes)
OpenQuery Test
CHeers
Thanks angelIII. Your solution works perfectly and gives me exactly the functionality I wanted. Full points to you.
Featured Post
This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening. This article shows how to create and use them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month6 days, 4 hours left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.