MS Access - best way to modularise/decouple forms for refactoring

Question

So I've been working with a large access database recently and I've been thinking about the best way to begin refactoring it. One of the primary difficulties in refactoring, is most of the forms rely on data in other forms.

The way it is currently done is like so;

Form1 has a button on it called Cmd1 that opens up a new form (DoCmd.OpenForm "Form2") and pre-populates some controls on the form using Forms!Form2.Control = Me.Control. When closing Form2, data is returned to Form1 by calling Forms!Form1.Control = Me.Control.

This presents a problem if I want to change either forms' names, or which form opens the popup form. It also requires both forms to be open, which you can't really rely on when you can't use modal popup forms because users want to be able to swap between forms.

I see 5 methods of passing values back and forth between forms, each with their problems, and I was wondering what was recommended.

1. The method already in place as explained above.
2. Using OpenArgs - makes lots of values difficult as you have to parse a string and is uni-directional.
3. Using global variables - This would mean tons of variables in a random module and presents a scoping nightmare.
4. Storing values to be passed between forms in a temporary table.

5. Maintaining a reference to the first form using

   Public master As Form
   Private Sub Form_Load()
       Set master = Screen.ActiveForm
       Me.Control = master.Control
   End Sub
   

   Which is kind of nice in that you can reference grandparent forms like master.master.control but could cause some major problems if a control on a parent form disappears in future.

So yeah, is there a recommended method?

Answer 1

This is such a critical question for developing a professional, maintainable Access application.

First, some comments on your list of methods:

• I agree with your assessment of the drawbacks of methods #1 and #2
• #3 and #4 share the same problem: a global table is just as global as a global variable. You can't fundamentally limit access to either.
• #5 will inevitably get you into problems. The extra long-term reference to the form will, in certain circumstances, keep it from closing.

I would propose moving all of your business logic into separate modules, broken out by form. This lets you avoid the global issues with #3 and #4, because modules can have private module-level variables and functions.

For instance, a form called frmFoo would get a matching module named modFrmFoo. The module would contain all of the public methods for using frmFoo:

Here is a simplified modFrmFoo:

Private mvID As Variant

' read-only (no Let)
Public Property Get frmFoo_ID() As Variant
    frmFoo_ID = mvID
End Property

Public Sub frmFoo_Show(ID As Variant, Name As Variant)
    ' note use of temporary object reference
    Dim rFoo As Form_frmFoo
    mvID = ID
    DoCmd.Open acForm, "frmFoo"
    Set rFoo = Forms("frmFoo")
    rFoo.ShowName Name
End Sub

The frmFoo form module needs one public method:

' only frmFoo should refer to its internal controls and code
Public Sub ShowName(Name As Variant)
    lblName.Caption = Name
End Sub

And, the query for frmFoo:

SELECT ID, [other stuff] FROM tblFoo WHERE tblFoo.ID = frmFoo_ID() 

The query syncs the results to the ID value returned by the property frmFoo_ID().

Showing frmFoo from another form is easy:

' frmBar
Private Sub cmdShowFoo_Click() 
    frmFoo_Show 123, "your name"
End Sub

Advantages of this method:

1. Defines a public interface for all public operations on a form. External forms call the module, never the form directly.
2. Keeps all form's private business private -- form controls can be renamed or deleted without upsetting all other forms that depend on it.
3. Defines in one call what is needed for each method to work -- for instance, frmFoo_Show() requires ID and Name.

This is along the lines of how I would do a new application from scratch.

Whether it is practical for retrofitting an old, tangled app is your call.

Answer 2

This might be of help:

You can open forms and control them Without using Open Args

You do this by opening another form as if it were a class

Create a module called GlobalVars which includes this line:

Public Form_MyFormNameToOpen as Form_MyFormNameToOpen

(The form you open will remain open until this variable "dies", so make it global to keep it alive)

MyFormNameToOpen is the name of the form you want to open and has Form_ in front of it in some of my example code. This tells access to get a "form class" ie one of the forms you created.

Then in code that you want to open a form use:

' Opens the form using it as it were a class
Set GlobalVars.Form_MyFormNameToOpen = New Form_MyFormNameToOpen

' The modify the form you have just opened however you want to.
With Form_MyFormNameToOpen
    .Visible = True

    ' This relies ont he control names never changing
    .Controls("ProviderID") = 10
    .Controls("ProviderFileID") = 11


    ' it's better to use properties created on the called form
    ' eg
    .MyLetProviderID = 10
    .MyProviderFileID = 11
End With

I hope this helps.

I find using open args a real pain except for the most basic of things. Using forms like this make life much easier.

(perhaps you can use a similar technique for reports, subforms etc...)