Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2004
    Posts
    13

    Unanswered: Run delete query from switchboard

    I have a delete query that i want to have run from a button on my main switchboard.

    When in the design mode for my switchboard i choose the command button from the toolbox and select Run Query it shows all my queries except my delete query. Why is this?

    Can someone give me a workaround so i can run it from my switchboard?
    Thanks in advance...

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    First off....make sure your query is properly built. Go to the Query section of your database window. If your DELETE Query has a Icon beside it that contains a X will a ! beside it [X!] then the query contains a error and will not be visible when trying to apply it through the button wizard or any other build wizard for that matter.

    Make sure your query is valid. Then it should work fine.

    You could also create a function to do the task for you then in the OnClick event for your button simply make a call to that function. For example:
    Code:
    Public Function myDeleteQuery()
       Dim SQLstrg As String
    [color=black]   SQLstrg = "DELETE FROM myTableName WHERE [TableColumnName] = '" & SomeTextValue & "'"
       DoCmd.SetWarnings False
       DoCmd.RunSQL SQLstrg
       DoCmd.SetWarnings True
    End Function
    In the OnClick event for the button:
    Code:
    Call myDeleteQuery
    You can also do this with a entirely different approach. Create another menu item in your Switchboard, say "Delete Query" as an example, using the SwitchBoard Manager. As a Command for the new menu item select Run Code. Then in the Function box simply enter the name of the function we created above which is myDeleteQuery().

    Now...at this point, you will get a error if you run the Switchboard and select this new board menu item. This is because the code generated when the Switchboard is created is slightly flawed and this flawed code in contained within a code function named HandleButtonClick located within the SwitchBoard form code module. In particular, the lines which appears like this:
    Code:
    ' Run code.
    Case conCmdRunCode
    	Application.Run rs![Argument]
    This doesn't work...You will need to change the above code to this:
    Code:
     
    ' Run code.
    Case conCmdRunCode
    	Eval rs![Argument]
    Run the Switchboard form and then select your new board menu item. Away you go.

    .
    Last edited by CyberLynx; 11-09-05 at 04:07.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Jun 2004
    Posts
    13
    Well I am guessing i made an error somewhere as tha X! is there....

    I have a single table. I went to Query - Design View. Selected the Table.
    Dragged the * then dragged the field i want to use for criteria.

    In criteria i had Like "i"

    I saved as "DeleteQueryCDL"

    No idea what i may have done wrong as i thought Like is a valid expression for criteria.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Does it work now? If not, post the SQL.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Switchboard

    Personally, I detest the switchboard and like designing Main forms from scratch with my own buttons. Just my 2 cents.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Personally, modems turn me off. I prefer to whistle.

































    (I agree btw. Switchboard writes ugly, inflexible code.)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Nov 2003
    Posts
    1,487
    Like the previous two gentlemen, I to reserve little faith with the Switchboard form and its managitorial companion, the Switchboard Manager. A little more effort and flexibility could have been placed into its design and overall structure. And for this reason, I also create my own menu boards should the need arise to actually utilize one.


    Never the less though, because of its applicable ease, database makers of all kind still use them and for as long as this is so, I will be more than happy to assist in any way I can.


    The real point here though is the fact that you have a faulty Query and for as long as this is so; you will never be able to utilize it. Why would you want to? You can continue to pull your hair out trying to figure out what is wrong with it or, post the query so that others may assist in solving your dilemma. You won’t go bald that way.


    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  8. #8
    Join Date
    Apr 2009
    Posts
    1

    HEEELLPP!!!!Urgentlyy neededd...pleeeasseee

    Can sum1 please help me aswel...i dont even know if the same people still exist on the forum as when this thread was made but pleaaseee help..i am having the same problem as "jpsprack" and really need this to work for my Coursework which i have to hand in soon...
    heres my problem...i also want to try and use a command button to run a delete query i have already made in my switchboard (not using switchboard manager as suggested) however it is not appearing on the query list. and i also have the [X!] next to my delete query in the query menu..here is a sort of an idea of what my query looks like and what im trying to do:
    Query Name:qdelYr11 Query Typeelete Query

    Tables:tblClass

    Field:ClassID
    Delete:Where
    Criteria:Like "*11*"
    Or:

    and when im using the method stated by "CyberLynx" with the following code:

    Public Function qdelYr11()
    Dim SQLstrg As String
    [color=black] SQLstrg = "DELETE FROM tblClass WHERE [ClassID] = '" & 11 & "'"
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQLstrg
    DoCmd.SetWarnings True
    End Function

    and then i type in on the OnClick code for the button as: Call yr11Delete.....i get an error message saying Microsoft Access could not find the macro!!...

    Pleeasseee sum1 helppp!!!...it would be greatly appreaciated!!!!

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I always lol when someone has URGENCY in an issue they are trying to resolve by forums. If it is that urgent, why not call a developer in?

    Interesting 4 year bump too ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Just out of curiosity what does [color=black] mean?

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    In VBA it means "syntax error". In a Format property it means just what it suggests.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    OK, I had an idea it was for formatting but why is it used it this code [color=black] SQLstrg = "DELETE FROM tblClass WHERE [ClassID] = '" & 11 & "'" as there is no formatting, see message 8 of this thread.

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't know why it is used in code here, as I suggested, in code all that will do is produce an error. I actually doubt it would compile.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •