[AccessD] Is it possible to update VBA code with VBA?

Drew Wutka DWUTKA at Marlow.com
Thu Jan 13 17:14:11 CST 2011


Yep, you can edit VBA with VBA.

Here's a routine I created to put a call to a function that records
report usage in the OnOpen event of every report of the database.
Doesn't do exactly what you are trying to do, but it should be a good
start as an example of how to go about doing what you are trying.

Dim rpt As Report
Dim dbs As DAO.Database
Dim ctr As Container
Dim dc As Document
Dim i As Long
Set dbs = CurrentDb
Set ctr = dbs.Containers!Reports
Dim strCode As String
Dim strCodeFix As String
strCode = "Private Sub Report_Open(Cancel As Integer)" & vbCrLf & _
"RecordReportUsage Me.NAME" & vbCrLf & _
"End Sub" & vbCrLf
strCodeFix = "Private Sub Report_Open(Cancel As Integer)" & vbCrLf & _
"RecordReportUsage Me.NAME" & vbCrLf
Dim blClean As Boolean
For Each dc In ctr.Documents
    blClean = True
    DoCmd.OpenReport dc.NAME, acViewDesign
    Set rpt = Reports(dc.NAME)
    For i = 1 To rpt.Module.CountOfLines
        If InStr(1, rpt.Module.Lines(i, 1), "Report_Open(",
vbTextCompare) > 0 Then
            blClean = False
            Debug.Print dc.NAME
        End If
    Next i
    If blClean Then
        rpt.Module.AddFromString strCode
        DoCmd.Save acReport, dc.NAME
    Else
        blClean = True
        For i = 1 To rpt.Module.CountOfLines
            If InStr(1, rpt.Module.Lines(i, 1), "As
Integer)RecordReportUsage", vbTextCompare) > 0 Then
                rpt.Module.ReplaceLine i, strCodeFix
                DoCmd.Save acReport, dc.NAME
            End If
        Next i
    End If
    Set rpt = Nothing
    DoCmd.Close acReport, dc.NAME
Next



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Thursday, January 13, 2011 4:41 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Is it possible to update VBA code with VBA?

All,

We have an Access application that creates a number of reports and is
used by several people.  In addition, because the application is fairly
new, it is being enhanced on a regular basis.

In order to avoid confusion, we have decided to establish a simple
"Application Version ID" that is shown at the bottom of all reports (and
also on forms).  The format of this Version ID is
Day.Month/Day/Year at Hour:Minute (example Thu.1/13/2011 at 15:53).  This
works nicely when we want to see which version of the application was
used to generate a previously generated report, etc.

Currently, we have a simple routine which generates our Version ID.
When changes are made to the application by our programmer, this routine
is used to generate a new Version ID and display it in a TextBox.  We
then manually copy this generated ID into the value of a Global Constant
field which is referenced in several places in the application.  (Note
that only the Access programmer is allowed to change the application.)


This process works Okay, but it would be better if we could
programmatically update the value of the constant field when the
application is changed by the programmer.  

Is there a way to update VBA code with VBA?  In other words, can a VBA
routine update other VBA code? (A small routine to generate our Version
ID and then plug the generated ID into the value of a Global constant).

Here is our Global Constant Field with its value. 
Global Const Con_Version_ID As String = "Thu.1/13/2011 at 15:53"

This might be a really dumb question.  We just have never tried to do
this before.

Thanks for your help.
Brad

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.





More information about the AccessD mailing list