use Dim frm As Form

Posted on 2006-04-10
Medium Priority
Last Modified: 2012-06-27
I'm constantly typing out the same long form references over and over again because I am not confident I know how to use Dim whatever As Form to refer to a main forms, subforms, subsubforms. Using these examples; how could I use As Form to refer to the form in the first, the sub in the second and the subsub in the third?


I'm Ok with strings but as soon as you get to As Form or As Control I need some clarification of when and how to use them.


Question by:eliwil
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16421780
Dim frmWhatever as Form

Set frmWhatever = Forms!frmMainInd
Debug.print frmWhatever!cboWhatever

Dim ctlWhatever as Control
Set ctlWhatever = frmWhatever!cboWhatever

LVL 58

Expert Comment

ID: 16421824

A variable of type Form (or Object) can hold a pointer to any existing open form. You use it like this:

    Dim frmAny As Form

    Set frmAny = Forms!frmMainInd
    frmAny!cboWhatever.Value = Null   ' on main form

    Set frmAny = Forms!frmMainInd!fsubGrtOrg.Form
    frmAny!cboWhatever.Value = Null   ' on sub form

    Set frmAny = frmAny!fsubGrtApp.Form
    frmAny!cboWhatever.Value = Null   ' on previous' form subform...

The whole idea of objects is that you manipulate "pointers" to them. To create a new object, you use special instructions, and Access also creates new objects -- e.g. when you open a form. Form _variables_ however are merely pointers. Shortcuts if you like.

If you have a doubt about an object's type, you can ask in the immediate pane:

    ? TypeName( Forms!frmMainInd!fsubGrtOrg.Form!cboWhatever )

This means that you can create a variable of type Combobox to hold a pointer to that particular control. Some "family" objects exist, for example the type Control, that can point to any control, not just comboboxes.

If  you just want to avoid typing, you can use the "implicit variable" of the With contruct, as in:

    With Forms!frmMainInd!fsubGrtOrg.Form
        !cboWhatever.Value = Null
        .BackColor = vbRed
    End With

Every object starting with . or ! will be taken from the innermost With construct.

Does that help any?
LVL 16

Expert Comment

ID: 16423199
If you want to take this a step further, forms with code modules have their own datatype.  This allows you to see the form's controls (using the auto entry feature) and public functions (if any) in the code view.

    Dim M As Form_frmMainInd
    Dim O As Form_fsubGrtOrg
    Dim A As Form_fsubGrtApp

    Set M = Forms!frmMainInd
    Set O = M.fsubGrtOrg.Form
    Set A = M.fsubGrtApp.Form

    M.cboWhatever.Value = Null   ' on main form ' Note the absence of the bang notation.
    O.cboWhatever.Value = Null   ' on sub form
    A.cboWhatever.Value = Null   ' on the other sub form
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 16423469

thanks this is exactly what I was looking for. one question: how come the double bang in
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16425244
You were looking for a With block?

The double bang just means that even EE Access Geniuses make typos ;-)
LVL 58

Accepted Solution

harfang earned 2000 total points
ID: 16426688
Thanks, Leigh ;)

The line just above is also wrong, it uses a non-existant property (forms have no .BackColor, sections and controls do) ...

If you are looking into With/End With, consider this:

    With Forms!frmMainInd!fsubGrtOrg.Form
        ' [do stuff on the form...]
        If !cboWhatever <> !txtSomeID Then
            !cboWhatever.Value = Null
        End If
    End With

It's tempting to write:

    With Forms!frmMainInd!fsubGrtOrg.Form
        ' do stuff on the form...
        With !cboWhatever
            If .Value <> !txtSomeID Then   ' <-- fails
                .Value = Null
            End If
        End With
    End With

This is one of the reasons why some programmers never use With/End With, and write instead:

    Dim frm As Form, cbo As ComboBox

    Set frm = Forms!frmMainInd!fsubGrtOrg.Form
    ' do stuff on the form (frm)...
    Set cbo = frm!cboWhatever
    If cbo <> frm!txtSomeID Then   ' <-- explicit!
        cbo.Value = Null
    End If
    Set cbo = Nothing
    set frm = Nothing

Of course, you can also mix both methods.


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question