Solved

Opening Queries using the "Run Code" in Switchboard.

Posted on 2004-09-09
11
2,746 Views
Last Modified: 2007-12-19
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.AllQueries(strQuery)) = "Nothing") Then
        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 !!!
0
Comment
Question by:nickwilton
[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
11 Comments
 
LVL 84
ID: 12015218
Your code should work ... assuming the name is correct, and the query returns records.

What line does your code error on?
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 12015222
Hi

The problem is that if the query does not exist then CurrentData.AllQueries(strQuery))  returns an error

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(strQuery).FullName

  DoCmd.OpenQuery strQuery
   
  Exit Sub
 
err_handler:
  MsgBox "Query Doesn't Exist: " & strQuery

End Sub

HTH

Scott
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12015261
You cannot use parameters in the switchboard...

Public Sub OpenQuery(strQuery As String)
    If (TypeName(CurrentData.AllQueries(strQuery)) = "Nothing") Then
        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
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:nickwilton
ID: 12015264
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.
0
 

Author Comment

by:nickwilton
ID: 12015296
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 ???
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12015416
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
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12015433
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
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 12015516
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12015520
PS: the above code needs to be place in the Switchboard form's code of function HandleButtonClick()
0
 

Author Comment

by:nickwilton
ID: 12015618
Thanks angelIII. Your solution works perfectly and gives me exactly the functionality I wanted. Full points to you.
0
 
LVL 4

Expert Comment

by:szacks
ID: 12015643
just a thought, I've never used the switchboard before...

Can you reference the switchboard in code the way you reference a normal form? i.e. Forms!frm_name
if so you can grab the queryname right off the form.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question