Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Obtain VBA Code of Access Forms (Office97+)

    I'd like to obtain the source code (VBA module) associated with an Access97 form , and I'd prefer to do it as a Word/VBA example. I can't find anything on the web or in the Lounge that suggests how to do it.

    I have had success in obtaining VBA code from Word and Excel. I thought with Access I'd find an "obj" and get VBcomponents etc, or at least get a very long string and have to parse it (as one does from a Word VBA Module)

    The sample code attached can locate the Form in which I am interested, but there doesn't seem to be any type of "code object' associated with the form.

    I have u/l the sample database 'Financial.MDB" to http://www.vif.com/users/cgreaves/do.../Financial.mdb

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

    Re: Obtain VBA Code of Access Forms (Office97+)

    The following function takes the full name of a database (including path) and the name of a form as arguments, and returns the code from the form module or and empty string. It uses Automation to start Access and open the database; you need to set a reference to the Microsoft Access 8.0 Object Library in Tools | References... (8.0 is for Access 97, it would be 9.0, 10.0 or 11.0 for Access 2000, 2002 or 2003. respectively.)

    Function GetCode(DatabaseName As String, FormName As String) As String
    Dim objAcc As Access.Application
    Dim objFrm As Access.Form
    Dim objMod As Access.Module
    Dim lngCount As Long
    Dim strResult As String

    On Error GoTo ErrHandler

    Set objAcc = CreateObject("Access.Application")
    objAcc.OpenCurrentDatabase DatabaseName
    objAcc.DoCmd.OpenForm FormName, acDesign
    Set objFrm = objAcc.Forms(FormName)
    If objFrm.HasModule Then
    Set objMod = objFrm.Module
    lngCount = objMod.CountOfLines
    strResult = strResult & objMod.Lines(1, lngCount)
    End If

    ExitHandler:
    GetCode = strResult
    On Error Resume Next
    Set objMod = Nothing
    objAcc.DoCmd.Close acForm, FormName, acSaveNo
    Set objFrm = Nothing
    objAcc.Quit acQuitSaveNone
    Set objAcc = Nothing
    Exit Function

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Function

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Obtain VBA Code of Access Forms (Office97+)

    Brilliant! And it worked first time. Thanks Hans.

    Now I'm going to print it and study it on the subway home ........

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Obtain VBA Code of Access Forms (Office97+)

    I see the error of my ways:

    I was opening the database as a collection of data, as would and end-user.

    Instead I have to get to it by the Access object, to whom, of course, the very structure of the database is data; and it is the data structure I want, not the data content.

    (Trots off to explore the Access object in more detail, .......)

Posting Permissions

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