Results 1 to 7 of 7
  1. #1
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    CurrentObjectName for Module (A2K SP-2)

    I'm trying to write a procedure that inserts error-handling code into a module (with a customized reference to the module and procedure, which is why I'm not just using "Insert File"). I got this to work in Access 2.0 (best version) and Access 97, but I'm stuck in Access 2000.

    My code works fine if the module I have open (and want to insert the text into) was opened from the Database Window, but not if I opened it from within the Visual Basic window. The reason is that I'm using Application.CurrentObjectName to return the name of the target module, and if I move from module to module within the Visual Basic window, Application.CurrentObjectName stays stuck at the module that I initially opened from the Database Window.

    Is there an alternative property I can refer to that will give me the name of the module that's actually active when the code is run (assuming the module was opened from within the Visual Basic window)?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CurrentObjectName for Module (A2K SP-2)

    I presume that you already have a reference to Microsoft Visual Basic for Applications Extensibility 5.3 if you're manipulating code in code. If not, set this reference. You can then use

    Application.VBE.ActiveCodePane.CodeModule.Parent.N ame

  3. #3
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: CurrentObjectName for Module (A2K SP-2)

    That works! (You knew that.) Thanks.

    2 follow-up questions:

    My error-handler insertion procedure is actually 2 procedures. Procedure 1 simply inserts some dummy text ('InsertErrorHandler) prior to the line where the cursor is (using SendKeys). Procedure 2 then finds that dummy text and uses it to orient the placement of the error-handling lines at the start and end of the procedure.

    The reason I split the procedure in two (which I didn't have to do in Access 2.0, but did have to do in Access97) is that the Find method doesn't find text that wasn't already in the target module when the finding procedure begins to execute. (Note that I've tried having the code save the target module in between SendKeys and Find, but that doesn't help. Also note that I can't use InsertLines instead of SendKeys -- if that would help -- because I don't know the name of the target procedure and so can't calculate the appropriate line in the module to be the insertion point.) So my first question is:

    1. Is there a way to use SendKeys in a module, then save the module (if that would help), and then do further module manipulation (using mdl.Find to find the text you sent with SendKeys, and hence find the target procedure), all in a single procedure?

    After Procedure 1 plants the line 'InsertErrorHandler in the target procedure, Procedure 2 finds the target procedure like this:

    If mdl.Find("'InsertErrorHandler", lngSLine, lngSCol, lngELine, lngECol) Then
    strProcedure = mdl.ProcOfLine(lngSLine, vbext_pk_Proc)
    End If

    It occurs to me that, if there was a way to refer to the name of the target procedure in the ActiveCodePane where the cursor is, not only wouldn't I need this Find routine, I could avoid the initial SendKeys as well. So my 2nd question is:

    2. Is there a way to refer to (or for the code to otherwise discover) the name of the particular procedure in the ActiveCodePane where the cursor is?

    ================================================== ========================
    APPENDICES
    ================================================== ========================

    Hopefully you won't need/want to see any of the more specific code I'm working with, but just in case you do ...

    (I should note that some of this code probably comes from the Access Developer's Handbook and is therefore copyrighted, all rights reserved, etc.)

    Here's a combined-procedure series of lines that doesn't work:

    Dim strModule As String
    Dim strProcedure As String
    Dim strProcType As String

    Dim mdl As Module
    Dim lngSLine As Long, lngSCol As Long
    Dim lngELine As Long, lngECol As Long

    Dim lngStartLine As Long, lngLineCount As Long
    Dim bytExtraLines As Byte
    Dim bytLinesMovedDown As Byte

    SendKeys "{Up}{End}"
    SendKeys "{Enter}"
    SendKeys "'InsertErrorHandler"

    strModule = Application.VBE.ActiveCodePane.CodeModule.Parent.N ame

    Set mdl = Modules(strModule)

    ' DoCmd.Save acModule, strModule

    If mdl.Find("'InsertErrorHandler", lngSLine, lngSCol, lngELine, lngECol) Then
    strProcedure = mdl.ProcOfLine(lngSLine, vbext_pk_Proc)
    mdl.ReplaceLine lngSLine, "On Error GoTo ErrorHandler"
    Else
    Debug.Print "Find unsuccessful."
    GoTo ExitLabel
    End If

    The Find is unsuccessful. Activating the commented Save line makes no difference. Saving, closing, reopening the module (and resetting mdl) also doesn't help, because it doesn't actually save and reopen the module (although there's no error message) -- which I know because if I then exit the database and reopen, the 'InsertErrorHandler line isn't there.

    On the other hand, as I've said, if I insert the 'InsertErrorHandler line using Procedure 1, and then run the above code (minus the SendKeys) as Procedure 2, it works fine -- and, interestingly, it works with no need to save the module in between running Procedure 1 (which only has SendKeys lines, and doesn't save the module) and running Procedure 2.

    In case anyone cares (or wants to use this), I've attached the rest of Procedure 2 as a text file.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CurrentObjectName for Module (A2K SP-2)

    You're reinventing the wheel, you know. There are tools out there that are very inexpensive that will do this for you. Take a look at MZ-Tools, which is what I use to insert my error handlers.
    Charlotte

  5. #5
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: CurrentObjectName for Module (A2K SP-2)

    Thanks for the tip. I've just looked at M-Z Tools and expect I'll install it.

    As I mentioned in my original post, the original version of my wheel dates back to Access 2.0, where it was a single procedure that I called from a button on a toolbox form.

    I did some substantial clean-up of my revised-for-A2K version yesterday after posting the old code, so just in case any Lounger has any use for a pair of VBA procedures to insert a customized and properly-positioned error-handler, I've attached the latest as a text file. You can invoke the new version without worrying about where the cursor is (as long as it's somewhere within the target procedure).

    Pending any further solutions from Hans (in response to my 2nd post), it's still 2 procedures, rather than one.

    And just in case it's news to any other Lounger (as it recently was to me), you can run code in the VBE window by double-clicking on the procedure in the Tools --> Macros list (event though it's not a Macro).
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CurrentObjectName for Module (A2K SP-2)

    Before the 2000 version, Access had its own variety of the Visual Basic Editor, and "macro" was only used for items in the Macros tab of the database window. Now, Access shares the Visual Basic Editor with the other Office apps, where "macro" means a procedure (sub) without arguments.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CurrentObjectName for Module (A2K SP-2)

    And Acess 2.0 used AccessBasic, which is not the same thing as VBA at all.
    Charlotte

Posting Permissions

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