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

Mark Simms marksimms at verizon.net
Thu Jan 13 21:26:32 CST 2011


Slick..... Drew - one takeaway from this:
Internally, the VBA source strips-out the <cr><lf> pairs ?
I assumed that was the case when I saw this:
"As Integer)RecordReportUsage"

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-
> bounces at databaseadvisors.com] On Behalf Of Drew Wutka
> Sent: Thursday, January 13, 2011 6:14 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Is it possible to update VBA code with VBA?
>
> 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.
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com





More information about the AccessD mailing list