Page 2 of 5 FirstFirst 12345 LastLast
Results 16 to 30 of 68
  1. #16
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I think that I know why this is happening. The following link provides a good clue:

    https://access-programmers.co.uk/for...d.php?t=157920

    It gives an explanation, but not a solution to the problem. Obviously it is a variable/code scope problem.

    The module with Reopen in its VBA code is a form module and not code module, thus its code has a restricted scope.

    That i believe will explain it, but I still need to know how to fix it.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  2. #17
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I really need answer t this question. My macro translated to VBA code will not compile because

    "Ropen SUB or Function is not defined"

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  3. #18
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    It is not recognizing the name of Function/Procedure you are trying to call. Try moving the code to a Standard Module.
    In the VBA Editor, in the Project Explorer, right-click on the Database name, then select Insert -> Module.
    Then move your VBA code that you are trying to call to this new Module.
    Make sure that the word "Private" does not exist before the word "Function" or "Sub" on the first line of your code. If it does, remove that word.

  4. #19
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I will follow our suggestion. However, since this was a macro translated to VBA code shouldn't it have done this automatically?

    Also, you said to move the code module to the standard module, that I will do. Is it okay to copy it from its original position and put it in newly created standard module and erase old form code module?

    What should I name the standard code module?

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  5. #20
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    However, since this was a macro translated to VBA code shouldn't it have done this automatically?
    If you did not move it out of the "Converted..." module it created to do this, then yes. This would be a Standard module.

    What should I name the standard code module?
    It doesn't really matter.

    If this still is not working for you, please post the Converted Code that you are now using, and show us how you have the new Macro to run this set up.

  6. #21
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    How do I move the code from mcrRestore to a newly created standard module? I have tried copying the VBA icode from mcrRestore into the newly created standard module then giving the standard module a name and deeleting the old mcrRestore module. That created an error when i opened the db after saving it.

    So how do I do it?

    Respectfully,

    Lou Reed

  7. #22
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    As I said in the previous post, if you are simply converting the Macros to Visual Basic and editing it there in the Module it has created, you shouldn't need to move anything.
    That location is fine.

  8. #23
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    The new modified db is attached. It has a standard module names standard-module-mcrestore; the old module converted Macro-mcrestore is now gone.

    It does give the same error. Reopen SUB or Function not defined. This time it is in the standard-module-mcrestore. The module changed the error did not.

    Of course, standard-module-mcrestore is a standard module.

    I am not sure what to do now. Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed
    Attached Files Attached Files

  9. #24
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    As I only have 32 Bit Access 2007 at home, it doesn't appear that I am able to open the database.
    So the only way I am going to be able to help you is if you do what I asked of you previously:
    If this still is not working for you, please post the Converted Code that you are now using, and show us how you have the new Macro to run this set up.
    You should be able to copy/paste the VBA code here.
    And either include a screen print of your Macro settings, or describe what Action and Values you are using in them.

  10. #25
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There is nothing wrong with the macro converted to code.

    This is the macro
    Click image for larger version. 

Name:	Macro1.jpg 
Views:	26 
Size:	19.0 KB 
ID:	29345
    This is the converted code
    Code:
    '------------------------------------------------------------
    ' mcrRestore
    '------------------------------------------------------------
    Function mcrRestore()
    On Error GoTo mcrRestore_Err
    
        Call fAccessWindow(3)
        Reopen
    
    
    mcrRestore_Exit:
        Exit Function
    
    mcrRestore_Err:
        MsgBox Error$
        Resume mcrRestore_Exit
    
    End Function
    The macro first executes the VBA function "fAccessWindow()" (what the macro command 'RunCode' does).
    Then there is the 'RunCode' command to execute the function 'Reopen'.

    BUT, nowhere in the code is a function (or Sub) named 'Reopen'. This was one of the problems with macros.... there was no error handling! Since there is no function/sub 'RunCode', the macro just skipped over the error. (or so it appears to me)

    So now that the macro is converted to code, there is an error because the "Reopen' code is missing.
    Don't know what the 'RunCode' code was supposed to do, so delete the line.




    When converting macros to code, you have to first understand WHAT the macro was trying to do, then edit the converted code to clean up the converted code. (as in Post #14 - the "FormHeading" code)





    BTW, the function "fAccessWindow()" in your code module "Common" appears to be taken from http://access.mvps.org/access/api/api0019.htm written by Dev Ashish. But the copyright notice was removed.

  11. #26
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Quote Originally Posted by ssanfu View Post
    There is nothing wrong with the macro converted to code.

    This is the macro
    Click image for larger version. 

Name:	Macro1.jpg 
Views:	26 
Size:	19.0 KB 
ID:	29345
    This is the converted code
    Code:
    '------------------------------------------------------------
    ' mcrRestore
    '------------------------------------------------------------
    Function mcrRestore()
    On Error GoTo mcrRestore_Err
    
        Call fAccessWindow(3)
        Reopen
    
    
    mcrRestore_Exit:
        Exit Function
    
    mcrRestore_Err:
        MsgBox Error$
        Resume mcrRestore_Exit
    
    End Function
    The macro first executes the VBA function "fAccessWindow()" (what the macro command 'RunCode' does).
    Then there is the 'RunCode' command to execute the function 'Reopen'.

    BUT, nowhere in the code is a function (or Sub) named 'Reopen'. This was one of the problems with macros.... there was no error handling! Since there is no function/sub 'RunCode', the macro just skipped over the error. (or so it appears to me)

    So now that the macro is converted to code, there is an error because the "Reopen' code is missing.
    Don't know what the 'RunCode' code was supposed to do, so delete the line.




    When converting macros to code, you have to first understand WHAT the macro was trying to do, then edit the converted code to clean up the converted code. (as in Post #14 - the "FormHeading" code)





    BTW, the function "fAccessWindow()" in your code module "Common" appears to be taken from http://access.mvps.org/access/api/api0019.htm written by Dev Ashish. But the copyright notice was removed.
    I am very concerned about the last issue. I must say in my defense that I inherited this db and all the macro that came with it including mcrRestore. I am creating this in a non-profit seeking organization. It will never be sold.

    In order to get right with the copyrighted code what must I do? Is it a question of simple copyright and all I need to do is replace the removed copyright?

    I can do that. I just do not want to be in copyright violation.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  12. #27
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    The way to fix this error is to remove the code line in question:

    Code:
     Call fAccessWindow(3)
     Reopen
    As I said I inherited this code and that includes the macro mcrRestore. I doubt even if I had run the macro that it would have run to the line in question. So the error in the macro was never seen.

    However, when I translated the code to VBA the error translated along with it. Then when I compiled the error - boom - it was now shown.

    I will just delete the Reopen code line and see what happens.

    Thanks for your reply.

    Respectfully

    Lou Reed

  13. #28
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    In the attached file I took out the Reopen code line. It now crashes on a different line, but it still crashes.

    I am including a screenshot of the source code where it crashes.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed
    Attached Files Attached Files

  14. #29
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    "Me.IsActive = No"

    Access doesn't know what "No" means, as it is not a declared variable name or constant. Replace "No" with "False".

  15. #30
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I have changed the No to False in the code. It now crashes on the next line. I know that sfrm.telework and sfrm.telework1 exist. It crashes on sfrm.telework1.

    How do I fix this?

    Any help appreciated.Thanks in advance.


    Respectfully,


    Lou Reed
    Attached Files Attached Files

Page 2 of 5 FirstFirst 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Converting Macros To VBA in 2013 Office
    By data808 in forum Access
    Replies: 8
    Last Post: 06-17-2014, 02:37 AM
  2. Wizard - Create code Code vs. Macros?
    By runbear in forum Forms
    Replies: 3
    Last Post: 01-08-2014, 12:52 PM
  3. VBA code for macros
    By marcvanderpeet12 in forum Access
    Replies: 1
    Last Post: 10-31-2013, 06:24 AM
  4. Converting SQL to VBA code
    By Davidyam in forum Access
    Replies: 3
    Last Post: 04-18-2012, 10:29 AM
  5. Replies: 9
    Last Post: 01-11-2012, 01:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums