Scan and Update VBA code w/VBA

[Tango]

Does anyone know if it is possible to scan all the code in all the forms and modules of an access database. Find matching string values and replace it with a user provided string?

Essentialy I want to make a "find and replace" function that runs off a button click but will scan all the VBA code and replace matches with the provided string. It just needs to run across ALL forms and modules.

 

[mdlueck]

A better design would be to refer to a FE temp table to keep track of the variable string value, and refer to the FE temp table from the spots in the code which need it.

Or maintain the value in a FE temp table spot, and use a global variable to pass it around the FE at run-time... avoiding multiple look-up's at run-time.

Given the fact that databases auto-compile when they are run, your pursuit would not only need to edit the source code, but additionally recomplie the DB on-the-fly... which disconnects the DB from external resources, resets the VBA variable pool, etc... "Changing oil in the car while on the highway driving" scenario comes to mind...

 

[Tango]

I see your point. What would be the best way to declare a global variable containing the string?

BTW the string is actually a filepath.

 

[mdlueck]

Well good for you for not hard coding the string throughout the application!

What would be the best way to declare a global variable containing the string?

If it truly is only ever going to be one string, in a separate Module define the variable as Public is the most simple way.

I like working with Class Objects. I happen to create one that tracks configuration / settings. Its makeup is roughly similar to a .INI configuration file. I store the data in a table, the class is the interface between that table and the rest of the VBA application. So in that scenrio, one would ask the Object for the variable's value by name, and get returned its current value in the database table.

P.S. BTW: I create the instance of such class objects in a separate VBA module, just like I suggested for the stand alone variable.

 

[Tango]

hehe, I'm learning from my mistakes... daily...

 

[boblarson]

As Michael said, data should be in a table (even if it is only one record) because it can change. Coding should be coded so that it doesn't have hard-coded values which can require changes. Reusabilty is the key.

 

[Tango]

I'm trying to create a public module that sets the value stored in my table. Here is what I have so far and it doesnt work at all. Is this a good time to point out I am still learning VBA? lol

Public Function srcpathlink(srcpath As String)
srcpath = CurrentDb.OpenRecordset("Select serverprefix From tblssc ")
End Function

the table with the value I need has two columns the 2nd column has the filepath and only one record in the table.

table name=tblSSC
column name=serverprefix

I'm trying to dim the path as a global variable.

Any pointers?

 

[Tango]

Public Function srcpathlink(srcpath As String)
srcpath = DLookup("serverprefix", "tblssc")
End Function

I cleaned it up some. Still doesnt work though.

 

[boblarson]

First off, you would go to a standard module and in the General Declarations Section you would put:

Public strSrcPath As String


and then your setting of that would be

Public Function SetSourcePath(strTableName As String, strColumnName As String) 
 
       strSrcPath = Nz(DLookup(strColumnName, strTableName), vbNullString)
End Function

And to call the function to set it you would use

SetSourcePath "tblSSC", "serverprefix"

 

[Tango]

when I call strSrcPath it returns null.

 

[boblarson]

when I call strSrcPath it returns null.

Did you first call the function to set it?

Also, did you make sure to stick the

Publcic strSrcPath As String

in a STANDARD module - not a form, report, or class module?

 

[Tango]

I have a module called SourceChange

Option Compare Database
Public strSrcPath As String
Public Function SetSourcePath(strTableName As String, strColumnName As String)
 
       strSrcPath = Nz(DLookup(strColumnName, strTableName), vbNullString)
End Function

Then on a form I have this..

Private Sub Command58_Click()
SetSourcePath "tblSSC", "serverprefix"
Dim strPath As String
If Nz(Me.[Control Number], "") = "" Then
MsgBox "You must select an event number before accessing the folders."
Exit Sub
Else
MsgBox "Drop all files, pictures and other documents associated with this record into the window that is about to open."
strPath = (strSrcPath & ("\QP3\other\") & (Me.[Control Number]))
    On Error GoTo MakeFolder_Err
    If Dir(strPath, vbDirectory) > vbNullString Then
    Call Shell("explorer.exe " & strPath, vbNormalFocus)
        Exit Sub
    End If
    MkDir strPath
    MakeFolder = True
    Call Shell("explorer.exe " & strPath, vbNormalFocus)
    Exit Sub
    End If
    
MakeFolder_Err:
    MsgBox "Folder """ & strPath & """ cannot be created."
    MakeFolder = False
End Sub

 

[boblarson]

Set a breakpoint in your click event at this point:

SetSourcePath "tblSSC", "serverprefix"

And step through it using F8 to see what is happening and what values are being returned for the various parts. Make sure you have a record in the table as well and that you have, if changed via a form, made sure the record has saved prior to doing this.

 

[Tango]

I found the solution. My other code wasnt behaving well with your code. I fixed it and it works like a champ now. Thank you both.