Support renaming of Access objects such as controls on Access forms

[hackgrid]

Rubberduck version information

Version 2.4.1.4627 OS: Microsoft Windows NT 10.0.17763.0, x64 Host Product: Microsoft Office x86 Host Version: 16.0.11901.20218 Host Executable: MSACCESS.EXE

Description

I am looking for a tool that can help me refactor controls on an Access form. There is no Rubberduck menu when I right-click on a control on a Access to rename it. (I've seen a screenshot like this for an word/excel userform though).

If I right-click on a control like this in the vba editor, Rubberduck only recognizes the current form and offers me to rename the current form or the current procedure.

Me![Text123] = ""

If I right-click on code like this, the "Rename"-command is just greyed-out.

Text123 = ""

"Text123" is a standard textbox control on a Access form.

To Reproduce

1. Use Rubberduck with Microsoft Access
2. Create a form with an textbox control on it
3. Try to refactor/rename the control with Rubberduck.

Expected behavior I thought Rubberduck could help me with refactoring my Access project. Does Rubberduck only support Userforms in VBA Excel/Word projects and not Access forms? Did I do something wrong?

Screenshots Does not apply.

Logfile Does not apply.

Additional context Does not apply.

Thanks for your help!

[bclothier]

Thank you for reporting! You raise up great questions.

1) Rubberduck is a VBE add-in and is meant to be host-agnostic. As such it does not directly deal with host. Access forms, reports and controls are a part of Access, not a pat of VBE, and to do something like rename requires interaction with the host, not with VBE -- renaming the contrls in the VBA code only would not be enough.

2) The ability to recognize a control as a control is on our todo list. In particular see this project. We already have the capability to know that so and so is something, we just need to actually integrate it. We welcome any help and PRs toward that goal.

3) Once this is integrated, we can definitely consider PR that helps refactoring such as Rename to handle forms/reports/controls. Though I said earlier we are a VBE add-in, rather than add-in (e.g. an Access add-in), we do have some provisions for handling some host-specific quirks. You can see how that is done in the AccessApp class. However, note that access must be carefully controlled to avoid memory leak (which is why we wrap all COM objects in a SafeComWrapper of some kind. Again, we welcome any help and PRs toward this goal.

With all that in mind, I'm going to label this as an enhancement request, rather than bug and change the title to be more specific with the intention.

[hackgrid]

Thanks for your fast reply and the thorough explaination! Sorry that I missinterpreted Rubberduck, but it is great that a feature like this may be possible in the future :-)

Do you know by chance of a Access add-in which would allow me to refactor Form controls like I intended?

Thanks and regards

[bclothier]

Unfortunately, no. I know there are add-ins that helps with renaming controls in userforms (e.g. MZ-Tools) but I am not aware of any Access add-ins that helps with the refactoring from Access.

However, I can give you an advice that might help in discovering them -- It looks to me that we have expressions like Me![Text123] which makes the code late-bound and therefore unverifiable. If you convert all Me! into Me., and strip away the brackets so that it becomes Me.Test123, then you can rename the controls and use VBA compiler to find the issues.

I would probably focus on cleaning the expressions up so that you end up with verifiable code first before I would try refactoring it. There is no sense in refactoring code if you can't verify that the code will continue to work as before. Several Access books usually consels to use the bang operator (!) based on a long-held myth that it's for collection access, which ends up preventing the compiler from knowing what types they are.

A common sin is to refer to other forms or subforms with expressions like Forms!SomeForm!SomeControl or Me!SomeSubForm.Form!SomeControl -- converting them into the long hand expressions such as:

Dim frm As Form_SomeForm
Set frm = Forms("SomeForm")
.... frm.SomeControl ...

or

Dim frm As Form_SomeForm
Set frm = Me.SomeSubform.Form
.... frm.SomeControl ...

means all references are now early-bound and thus can be verified by the compiler. That also means that when you rename your control, this will become a compiler error and thus much easier to manage.

I know this isn't easy but you could write something to automate the replacement of bang operator with dot operator and stripping away the brackets (esp. if they are not required). Doing that will make your refactoring much easier. You can then use Rubberduck to help with the actual renaming as most of reference will already be in VBA.

Best of luck!

[hackgrid]

Thanks for your reply!

I recently inherited a very old and big codebase which unfortunately uses the bang operator and the default generated control names a lot - so there are LOTS of controls named like text1 - text999 on ONE form.

A automatic refactoring process would have been great for that, but I guess I have to manually fix it step for step over time.

Thanks and regards

[A9G-Data-Droid]

The current behavior of RD today is that the rename feature is available from the Code Explorer window on Access Form objects. You can even try to perform a rename action on an Access Form. It will silently fail. I was recently surprised by this. Until we have the ability to rename Access form objects we should either disallow the rename feature or at least pop an error explaining that we can't rename this type of object. The current behavior leads the user to believe that they should be able to rename and it's just not working.

I do see this relevant error in the log. This would be the place where we would want to catch and notify the user that this operation is not supported:

2021-06-01 10:45:46.9243;INFO-2.5.2.5906;Rubberduck.VBEditor.SafeComWrappers.SafeComWrapper`1;Failed to get host document -redacted-;System.Runtime.InteropServices.InvalidComObjectException: COM object that has been separated from its underlying RCW cannot be used.
   at System.StubHelpers.StubHelpers.GetCOMIPFromRCW(Object objSrc, IntPtr pCPCMD, IntPtr& ppTarget, Boolean& pfNeedsRelease)
   at Microsoft.Office.Interop.Access._Application.get_CurrentProject()
   at Rubberduck.VBEditor.SafeComWrappers.VBA.AccessApp.GetDocument(QualifiedModuleName moduleName) in C:\projects\rubberduck\Rubberduck.VBEditor.VBA\SafeComWrappers\Application\AccessApp.cs:line 37