Hello folks,

I inherited a 3rd party commercial app that uses currentuser() to gather the /wrkgrp users' names for an [updated by] and [entered by] field to audit record changes. As this app was moved to an accdb against sql server this function no longer works. (Everyone is now "Admin")

basically this is what is currently in place:


If IsNull(Me![Updated By]) Then
Me![Entered By] = CurrentUser()
Me![Date Entered] = Date
End If

Me![Updated By] = CurrentUser()
Me![Date Updated] = Date

Exit Sub

...after I (with help from this board!) created a module that will pull the logged-in-to-windows user, I wish to replace the above use of CurrentUser() with another function "fOSUserName" so the user's names are valued for these fields.

Now the tedious part...

Is there an en masse way to find/replace code strings from the many, many VBA projects?

This application is pretty big. It would take me all day to make the changes - and worse, the code is not very consistent - so I would likely miss some.

QUESTION: Is there some way I can scour the vba project and find all uses of the CurrentUser() and replace with "fOSUserName" ?

I see that I can open the project explorer and Ctrl-F through each opened project piece - but there are about two hundred or so bits, and being a whiny punk, I don't want to have to cycle through a manual find and replace effort that would take many hours.


Is there a better way to approach this? Perhaps a magical way to export out the vba to a single area where I can make these changes - that will retain form after I push the updates back?

Thoughts? Magicians? Folks who know more than me and can lend me a hand with a better approach?


Thanks!
-Matt G.

...or, perhaps is there a way to redefine the CurrentUser() function to call another function? So I don't even need to bother finding all instances of this call, but can leave them in place...

Thanks for any ideas!

-Matt G.

How many different databases do you have to make this change in?

The replace function (edit menu or ctrl + H) in the VBA IDE can replace all instances in all modules, including Form Modules and class modules.

Or, and I'm not good enough to do this, but I believe it may be worth looking into,

You could export each module, class module, form module, in all of your databases, to text, open them in a stream, edit your CurrentUser() with fOSUserName, write it back, then use the VBIDE.VBComponents.Import method to import all of your modules again. You would need to delete your modules so as not to get duplicate modules in this process. I found some code on another site.

Excel Code but VBA IDE is VBA IDE (http://www.cpearson.com/excel/vbe.aspx)

That link shows some cool stuff that you can do inside the VBA IDE.

Most of your calls to CurrentUser() are going to be on the form arent they?!?

If they were in a regular module, This code (http://bytes.com/forum/thread660106.html) I just used and it worked for me.

However, I think the time it would take to write the code needed to do all of the above would be just about as long as the time it would take you to open each database and do a replace, depending on how many databases you have. The only plus would be that writing the code wouldn't be so boring.

Just trying to help you brainstorm. I've thought of trying to write something like this in the past, for a different reason though.

a utility called vtools has a "deep search" find and replace tool.
sounds like a good time to move the function its own module so you only have to make changes in one place.

Guys, thanks for you help!

I didn't quite realize the flexibility of the VBA IDE in so far as its ability to find/replace across the whole series of a project's modules and sub parts...

I carefully searched for the use of currentUSer() (and currentuser "like" variables used to store the user name string) and was able to do this much easier than I was originally thinking...

...and I moved the function to its own module, with just the function being called when needed.

Much easier to maintain now!

Thanks again folks!