Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Automation of code writing, maybe?

    Hi

    Iím not holding out much hope on this one but here goes:

    Iíve inherited an Access database reporting on data stored in a 3rd party SQL server database. Due to a change of the backend, it will require an extensive rewrite. In order to do this efficiently, and affect users as little as possible, I wrote a few procedures that wrote to a table each time a form was opened and closed. This has proved extremely useful Ė I can now delete about 90(!!) of the forms (after backing up, of course) without affecting functionality. Iíd like to extend this now to the reports, however there are loads more reports than forms. I would prefer not to have to open each one in turn and add a call to the procedure for each forms closing and opening event. So, hereís the question Ė is there a way of programmatically parsing code and inserting lines into each reportís opening and closing event? If there is no module attached to the report, can one be created to allow the inserting of code? Alternatively, is there a way of cycling through all the reports and inserting a macro name into each ones on open and on close event? Is there any other way of achieving what I'm trying to do? In case it is useful, Iíve attached the code (called with OpenFunction(me.name)) to write the opening event into the table.

    If someone has an idea I'd be eternally grateful-

    Cheers!


    Function OpenFunction(FName As String) As Boolean ' FName is the object name
    On Error GoTo Err_OpenFunction

    Dim FOpen As Date
    Dim strSQL As String
    Dim ctl As Control

    'Sets ctl to a Session ID value calculated when switchboard opens
    Set ctl = Forms!mainmenu.txtSID

    FOpen = Now

    'StartDate and EndDate are public constants that define the time period the code should run
    If FOpen >= StartDate And FOpen <= EndDate And Right(Application.CurrentDb.Name, 4) <> ".mdb" Then

    strSQL = "Insert into DBusage (sessionid, DateIn, formname) values ('" & ctl & "', #" & FOpen & "#, '" & FName & "')"

    CurrentDb.Execute strSQL, dbFailOnError

    OpenFunction = True

    End If

    Set ctl = Nothing

    Exit_OpenFunction:
    Exit Function

    Err_OpenFunction:

    If StartupErrHandler("OpenFunction", "SessionLookup", Err.Number, Err.Description, Err.Source) = False Then
    MsgBox Err.Number & ", " & Err.Description & ", " & Err.Source & vbCrLf & "OpenFunction"
    End If

    OpenFunction = False

    Resume Exit_OpenFunction

    End Function
    Last edited by pootle flump; 04-22-04 at 13:25.

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: Automation of code writing, maybe?

    Write a procedure that does this general methodology:

    For each Rpt in Reports

    ' DO YOUR BEFORE OPEN REPORT CODE HERE

    'OPEN REPORT HERE

    'CLOSE REPORT HERE

    Next
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Re: Automation of code writing, maybe?

    Thanks for responding

    I think I was a bit ambiguous earlier- the writing to the table occurs every time a user opens a Report, therefore I get an idea of usage (for example my table containing Form usage data has about 500 entries for the switchboard, so if I know that if I deleted it it would be missed! Some other forms, however, have been opened once in two months and so I feel confident in getting rid). It's basically a simple auditing tool that I want to attach to each report and form retrospectively. Something that would have been nice to have had in the form and report templates from the beginning...

    Does the code above loop through the reports collection and run the function for each of them, writing their name to the table? This would provide a sort of inventory? or have I missed something?

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Re: Automation of code writing, maybe?

    Originally posted by pootle flump
    Hi

    Iím not holding out much hope on this one but here goes:

    Iíve inherited an Access database reporting on data stored in a 3rd party SQL server database. Due to a change of the backend, it will require an extensive rewrite. In order to do this efficiently, and affect users as little as possible, I wrote a few procedures that wrote to a table each time a form was opened and closed. This has proved extremely useful Ė I can now delete about 90(!!) of the forms (after backing up, of course) without affecting functionality. Iíd like to extend this now to the reports, however there are loads more reports than forms. I would prefer not to have to open each one in turn and add a call to the procedure for each forms closing and opening event. So, hereís the question Ė is there a way of programmatically parsing code and inserting lines into each reportís opening and closing event? If there is no module attached to the report, can one be created to allow the inserting of code? Alternatively, is there a way of cycling through all the reports and inserting a macro name into each ones on open and on close event? Is there any other way of achieving what I'm trying to do? In case it is useful, Iíve attached the code (called with OpenFunction(me.name)) to write the opening event into the table.

    If someone has an idea I'd be eternally grateful-

    Cheers!


    Function OpenFunction(FName As String) As Boolean ' FName is the object name
    On Error GoTo Err_OpenFunction

    Dim FOpen As Date
    Dim strSQL As String
    Dim ctl As Control

    'Sets ctl to a Session ID value calculated when switchboard opens
    Set ctl = Forms!mainmenu.txtSID

    FOpen = Now

    'StartDate and EndDate are public constants that define the time period the code should run
    If FOpen >= StartDate And FOpen <= EndDate And Right(Application.CurrentDb.Name, 4) <> ".mdb" Then

    strSQL = "Insert into DBusage (sessionid, DateIn, formname) values ('" & ctl & "', #" & FOpen & "#, '" & FName & "')"

    CurrentDb.Execute strSQL, dbFailOnError

    OpenFunction = True

    End If

    Set ctl = Nothing

    Exit_OpenFunction:
    Exit Function

    Err_OpenFunction:

    If StartupErrHandler("OpenFunction", "SessionLookup", Err.Number, Err.Description, Err.Source) = False Then
    MsgBox Err.Number & ", " & Err.Description & ", " & Err.Source & vbCrLf & "OpenFunction"
    End If

    OpenFunction = False

    Resume Exit_OpenFunction

    End Function
    I haven't worked with this except for experimentation, but in Access 2002 anyway, there is a modules collection and a module object that lets you work with code in modules. Check out the object browser for methods and properties if this sounds like something you would like to use.

    Hope this helps.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  5. #5
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Automation of code writing, maybe?

    Originally posted by pootle flump
    Hi

    Iím not holding out much hope on this one but here goes:

    Iíve inherited an Access database reporting on data stored in a 3rd party SQL server database. Due to a change of the backend, it will require an extensive rewrite. In order to do this efficiently, and affect users as little as possible, I wrote a few procedures that wrote to a table each time a form was opened and closed. This has proved extremely useful Ė I can now delete about 90(!!) of the forms (after backing up, of course) without affecting functionality. Iíd like to extend this now to the reports, however there are loads more reports than forms. I would prefer not to have to open each one in turn and add a call to the procedure for each forms closing and opening event. So, hereís the question Ė is there a way of programmatically parsing code and inserting lines into each reportís opening and closing event? If there is no module attached to the report, can one be created to allow the inserting of code? Alternatively, is there a way of cycling through all the reports and inserting a macro name into each ones on open and on close event? Is there any other way of achieving what I'm trying to do? In case it is useful, Iíve attached the code (called with OpenFunction(me.name)) to write the opening event into the table.

    If someone has an idea I'd be eternally grateful-

    Cheers!


    Function OpenFunction(FName As String) As Boolean ' FName is the object name
    On Error GoTo Err_OpenFunction

    Dim FOpen As Date
    Dim strSQL As String
    Dim ctl As Control

    'Sets ctl to a Session ID value calculated when switchboard opens
    Set ctl = Forms!mainmenu.txtSID

    FOpen = Now

    'StartDate and EndDate are public constants that define the time period the code should run
    If FOpen >= StartDate And FOpen <= EndDate And Right(Application.CurrentDb.Name, 4) <> ".mdb" Then

    strSQL = "Insert into DBusage (sessionid, DateIn, formname) values ('" & ctl & "', #" & FOpen & "#, '" & FName & "')"

    CurrentDb.Execute strSQL, dbFailOnError

    OpenFunction = True

    End If

    Set ctl = Nothing

    Exit_OpenFunction:
    Exit Function

    Err_OpenFunction:

    If StartupErrHandler("OpenFunction", "SessionLookup", Err.Number, Err.Description, Err.Source) = False Then
    MsgBox Err.Number & ", " & Err.Description & ", " & Err.Source & vbCrLf & "OpenFunction"
    End If

    OpenFunction = False

    Resume Exit_OpenFunction

    End Function
    Just some additional experimentation I did.

    Private Sub Command45_Click()
    Dim frm As Form, ctl As Control, strName As String, prp As Property
    strName = "frmEvents"
    DoCmd.OpenForm strName, acDesign, , , acFormEdit, acHidden
    Set frm = Forms!frmEvents
    With frm
    Set ctl = frm.cmdClose
    For Each prp In ctl.Properties

    If prp.Name = "OnClick" Then

    If prp.Value = "" Then
    prp.Value = "[Event Procedure]"
    MsgBox prp.Value
    Else
    prp.Value = ""
    MsgBox prp.Value
    End If
    End If
    Next
    DoCmd.Close acForm, "frmEvents, , acSaveYes"
    End With



    End Sub

    The event properties were read/write and if you combined that with code insertion using the methods of the Module object or entered the name of a macro, I think it would work.

    If you try this, let me know how it turns out.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Re: Automation of code writing, maybe?

    Originally posted by basicmek
    I haven't worked with this except for experimentation, but in Access 2002 anyway, there is a modules collection and a module object that lets you work with code in modules. Check out the object browser for methods and properties if this sounds like something you would like to use.

    Hope this helps.
    Oustanding - cheers. I'll install ASAP (using A2K at the mo). Does this collection contain all modules regardless of whether or not they are bound to an object (um, do I mean class modules here)?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Re: Automation of code writing, maybe?

    Originally posted by basicmek
    I haven't worked with this except for experimentation, but in Access 2002 anyway, there is a modules collection and a module object that lets you work with code in modules. Check out the object browser for methods and properties if this sounds like something you would like to use.

    Hope this helps.
    Oustanding - cheers. I'll install ASAP (using A2K at the mo). Does this collection contain all modules regardless of whether or not they are bound to an object (um, do I mean class modules here)?

  8. #8
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Automation of code writing, maybe?

    Originally posted by pootle flump
    Oustanding - cheers. I'll install ASAP (using A2K at the mo). Does this collection contain all modules regardless of whether or not they are bound to an object (um, do I mean class modules here)?
    Here's a bit from the help topic.

    Microsoft Access includes class modules that are not associated with any object, and form modules and report modules, which are associated with a form or report.

    To determine whether a Module object represents a standard module or a class module from code, check the Module object's Type property.

    The Modules collection contains all open Module objects, regardless of their type. Modules in the Modules collection can be compiled or uncompiled.

    To return a reference to a particular standard or class Module object in the Modules collection, use any of the following syntax forms.

    Syntax Description
    Modules!modulename The modulename argument is the name of the Module object.
    Modules("modulename") The modulename argument is the name of the Module object.
    Modules(index) The index argument is the numeric position of the object within the collection.


    The following example returns a reference to a standard Module object and assigns it to an object variable:

    Dim mdl As Module
    Set mdl = Modules![Utility Functions]
    Note that the brackets enclosing the name of the Module object are necessary only if the name of the Module includes spaces.

    The next example returns a reference to a form Module object and assigns it to an object variable:

    Dim mdl As Module
    Set mdl = Modules!Form_Employees
    To refer to a specific form or report module, you can also use the Form or Report object's Module property:

    Forms!formname.Module

    The following example also returns a reference to the Module object associated with an Employees form and assigns it to an object variable:

    Dim mdl As Module
    Set mdl = Forms!Employees.Module
    Once you've returned a reference to a Module object, you can set or read its properties and apply its methods.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Re: Automation of code writing, maybe?

    Thanks again - time to play, I think.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Gregg

    Thanks again for your help - I'd never have thought of looking at 2002 to see if there was an answer. In case you are interested, the code I used is below.
    Not the most elegant code in the world and could be tidied (only just noticed I nested With statements - I doubt you find that in many Good Practice essays). Anyway, seemed to do the job although I need to check the database more thoroughly. I have had compiling errors and on first look it seems the VBE thinks that my Form modules are actually Report modules (me. results in GroupHeader0, 1, 2... options in the drop down). Not sure if I have made a mess of the indexes or something.

    Anyhoo, I'm sure there'll be a simple answer

    Cheers

    -----------------------------------------------------------------
    Function fAddAlterMdl()

    Dim i As Integer
    Dim bolSave As Boolean
    Dim strOpenCall As String
    Dim strCloseCall As String
    Dim rpt As Report
    Dim lngOpRet As Long
    Dim lngClRet As Long
    Dim lngStLine As Long
    Dim lngEndLine As Long
    Dim lngStCol As Long
    Dim lngEndCol As Long

    strOpenCall = "OpenFunction (Me.Name)"
    strCloseCall = "Call CloseFunction(Me.Name, 5)"

    For i = 1 To Application.CurrentDb.Containers(5).Documents.Coun t - 1

    DoCmd.OpenReport Application.CurrentDb.Containers(5).Documents(i).N ame, acDesign

    bolSave = False

    Set rpt = Reports(Application.CurrentDb.Containers(5).Docume nts(i).Name)

    With rpt

    If .HasModule = False Then

    .HasModule = True

    bolSave = True

    End If

    With .Module

    If .Find(strOpenCall, lngStLine, lngStCol, lngEndLine, lngEndCol, True, True) = False Then

    If .Find("Report_Open", lngStLine, lngStCol, lngEndLine, lngEndCol, True, True) = True Then

    .InsertLines lngStLine + 1, vbCrLf & vbTab & strOpenCall

    Else

    lngOpRet = .CreateEventProc("Open", "Report")
    .InsertLines lngOpRet + 1, vbCrLf & vbTab & strOpenCall

    End If

    bolSave = True

    End If

    lngStLine = 0
    lngStCol = 0
    lngEndLine = 0
    lngEndCol = 0

    If .Find(strCloseCall, lngStLine, lngStCol, lngEndLine, lngEndCol, True, True) = False Then

    If .Find("Report_Close", lngStLine, lngStCol, lngEndLine, lngEndCol, True, True) = True Then

    .InsertLines lngStLine + 1, vbCrLf & vbTab & strCloseCall

    Else

    lngClRet = .CreateEventProc("Close", "Report")
    .InsertLines lngClRet + 1, vbCrLf & vbTab & strCloseCall

    End If

    bolSave = True

    End If

    lngStLine = 0
    lngStCol = 0
    lngEndLine = 0
    lngEndCol = 0

    End With

    End With

    If bolSave = False Then

    DoCmd.Close acReport, rpt.Name, acSaveNo

    Else

    DoCmd.Close acReport, rpt.Name, acSaveYes

    End If

    Set rpt = Nothing

    Next i

    MsgBox "Completed adding/updating " & i & " modules."

    End Function
    -----------------------------------------------------------

  11. #11
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Thumbs up You done good.

    I played around with some of it also but since I didn't have a special reason I simply created, in code, a form with a command button.Then I inserted an event with some lines for the command buttons click event.

    I think I'll have to create a few reasons of my own to implement this process in some of my own databases.

    Glad things worked out.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Cheers - only noticed your code after I'd finished; somehow missed it when you posted originally. Much nicer way of checking if an event procedure exists. But since it's a "run once" sort of thing I doubt I'll return to it. Not until I want to remove the 300 or so modules I've created anyway. Let me know if you unearth anything interesting

    Thanks again

  13. #13
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Thought that with a macro.....

    In the code you were refering to, I was thinking along the lines of using a macro or calling a public function by placing the function name in the box where "[Event Procedure]" would go. Thought it might simplify some of the processing. Sounds like you got things rolling though.

    Best of luck.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

Posting Permissions

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