Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2007

    Unanswered: Code works out of editor but not from switchboard

    I have some really basic VBA that does only a couple things. From the switchboard, you click exit. This calls Run Code ExitJobs
    Exitjobs is the name of a module and a function of that module (that could be my problem) that calls another sub and then runs application.close. The other job is a sub and a module named the same as each other again called Updates. Updates runs some SQL to perform and update query on a table (storing pricing info so that we have the historical cost).
    If I am in the editor and try to run it, it runs. I do get the little box that says "You are about to update X rows do you wish to do that?" but it runs and even closes the app like it should. If I run it from the switchboard I get a box that simply says "an error has occured". When I click ok, nothing happens.

    I have two theories:
    It's the fact that I have the modules and the subs with the same name. I can manually run the sub from the editor but the app gets confused when I try to call it from outside.
    It's the update query. I get a pop up (that I don't want) when I run it from within. When I try to run it from the app, the pop up doesn't get handled well.

    Any thoughts or guidance?

  2. #2
    Join Date
    May 2005
    Can you post the code from these functions you're talking about?
    Me.Geek = True

  3. #3
    Join Date
    Feb 2007
    I would be happy to:

    Public Function ExitJobs()
    End Function
    Public Function UpdateSavings()
    Dim Update, InnerJoin, SetValue, Where, Sql As String
    Update = "UPDATE tblCostCalcDC"
    InnerJoin = "INNER JOIN [qryCostCalcDC Query] ON tblCostCalcDC.idNUMBER = [qryCostCalcDC Query].idNUMBER "
    SetValue = "SET tblCostCalcDC.DCcostOfThx = [qryCostCalcDC Query]!Expr1"
    Where = "WHERE ((([tblCostCalcDC]![DCcostOfThx])=0))"
    Sql = Update & " " & InnerJoin & " " & SetValue & " " & Where & ";"
    DoCmd.RunSQL Sql
    End Function
    The sql is pulled out of a query I was using to do this same thing. I thought that if I put it in code it might made that nasty accept message go away.

  4. #4
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Yes, you've violated a cardinal rule of Access, naming the modules the same thing as the functions it holds! Before you do anything else you need to change one or the other! Obviously changing the names of the modules is the easiest; unlike the function, the modules' names aren't referrenced anywhere.

    The other question is where does the query get the pricing info you talk about storing? If this is from your form that was active before returning to the switchboard, is that form still open or has it closed?
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Dec 2004
    Madison, WI
    YES - watch your naming conventions. Also look up "Reserved Names" and AVOID naming any variables, field names, tables, functions, etc. with these names! As well as avoid spaces and characters like !@#$%^&*()+=<>?/ in your names for your tables, fields, forms, etc..!! Trust us - you'll thank yourself in the long run if you follow this rule.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    May 2007
    the other thing in the switchboard - if you look carefully it allows 7 object types

    0 is a header
    1 is a menu jump
    3 is a form in normal mode etc

    its possible that the argument and mode arent set correctly in the switchboard items table

    one enhancement ive made to the standard menu is to identify the argument, so instead of "there was an error executing the command", the message is more meaningful eg you specified form name "xxxxx" but it couldnt be found

  7. #7
    Join Date
    Feb 2007
    I fixed the name and that did it. I appreciate the help. I guess I never put any thought into it. There is a lesson learned only 1648641648789746 more to go.

    The query is pretty funky and I mayhave approached it the wrong way. I inherited this thing from a resident. It's got a few hundred records in it. It is my job to fix somethings they always wanted but never knew how to get and that sort of thing. One of the issues they had was that they savings based on an action was calculated dynamically. So if the price of something changed, then the savings changed.
    My solution was to add a field that stored the savings. I initially wrote teh query to just fill in the gaps but I thought, why not leave it in place and have it run on close to fix the prices. The actual data is pulled from an inventory table and the interventions table.

    Should I change the data to store on leaving the subform they enter it in or something like that?

Posting Permissions

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