How do I reference the current form in an expression in Microsoft Access?

Question

I'm no Access expert, and I have an (I hope!) simple question...

I have a form with a number of records. In some textboxes I just present values from the underlying table - so they are bound to the corresponding fields.

But some textboxes should contain calculated values. Some calculations are complicated and involves many fields from the table. I write the calculation as a VBA function. I could enter something like this as "Control Source":

=MyFunction([Field1], [Field2], [Field3] ...)

But I don't want to list dozens of fields in the function call. Instead, I want to send the whole form (or the current record) as a parameter, and let the function reference the fields it needs. I can do it like this:

=MyFunction([Forms]![MyForm])

But I don't like having to name the form in the call. Isn't there a way to send the "current form" as a function argument? In VBA, you just use the "Me" keyword, for exampel "Me![Field1]". But it seems like "Me" isn't accepted in an expression.

Is there some other way to reference the current form in an expression?

(It's a cosmetic question, I know. But it's not good programming to use "[Form]![MyForm]". Later on you copy the controls to another form and forget to change the name in the expression...)

Grateful for your help! :-)

/Anders

Antimonic

Answer 1

You can use:

=MyFunction([Form])

The code would be:

Function MyFunction(frm As Form)
MsgBox frm.Name
End Function

Theresita

Answer 2

'me' can only be called from within the corresponding form object (ie in the object's procedures, functions and events).

My favorite way to refer to the current form is to call the screen.activeForm object...

screen.activeForm.recordset.fields(myFieldname).value
screen.activeForm.controls(myControl).value
screen.activeForm.name
....

Of course you can send the form object to a custom function

my Result = myCustomFunction(screen.activeForm)

Or you could build a user-defined object. The needed fields could then be considered as internal properties, set in the Class_Initialize sub of the corresponding object.

Fouts

Screen.ActiveForm.Name can be used in a global function! Thanks for that! – Schilit

I think this should be marked as the answer since it will work to accomplish the goal in far more situations than passing the form name as a parameter and will eliminate the need to pass a parameter at all--even neater, less confusing code. Kudos to Philippe Grondier! This solved a problem I was addressing very neatly. – Hereby

Answer 3

If you are using code in the form, then "me" referrers to the current form. So

Call SomeFunction(me)

However, "me" can't be used in the context of a expression that is bound to a text box.

In that case you can either pick up the current screen in the routine with screen.activeform as suggested.

I often go:

Dim f     as form
Set f = screen.ActiveForm

Stinkpot

It is quite possible to set an event or even the control source of an object to a function using the property sheet for the control, as I am sure you know, and in that case =AFunction([Form]) can be quite useful. It is particularly useful if you wish to cut-and-paste a control to several forms. I believe it is this functionality that the OP was considering. – Theresita

Golly, I had no idea that [Form] could be used in place of the keyword [me] in an expression. This indeed works and does pass the form name and does so without hard coding. This tip certainly opens up some possibilites I was not aware of. – Stinkpot

Yeah, I didn't know that "Form" in an expression was analogous to the "Me" keyword in VBA. Very handy. I can't seem to find documentation of this technique, but it works! – Galilee

Answer 4

Screen.ActiveControl.Parent
' or
someControlVariable.Parent

I use this if I want the current subform (or form if not currently in a subform) because using Screen.ActiveForm does not provide the current subform, but only the form containing that subform. Do be aware of your context, if the control is within a tab control then its parent is that tab control and not the form.

Circumcision

Answer 5

In ACC2013 I couldn't use fionnuala's answer. So I changed to:

Event handler in Property-Window:

=MyFunction()

My code would be:

Function MyFunction()
  Dim frm As Form   
  Set frm = Screen.ActiveForm
  MsgBox frm.Name
End Function

Naominaor

As noted, you can pass “me” or [form], ubt you have to replace “[form]” with the actual name of the form. So fionnuala answer will work, but you NEVER use [Form], but replace form with the form name such as FORMS!SomeFormName. I think it best to pass "me" to the routines since then code can run correctly from a form in the event that some other form is launched or gets the focues then your code will not pick up the wrong from. However you use of Screen.ActiveForm is ok. – Stinkpot

Why is it a problem to use [Form] in Expression Builder? Is ActiveForm even an option in this instance? I see a downside to explicitly using the form's name since these names can change, or you may want to steal a form's expressions or code, but use a different name for the form. – Galilee

Answer 6

The approach followed by Philippe Grondier above is a more universal one particularly when you need to reference a field on a form that is a new instance of a form object and a combo control query criteria references the value of another field on the same form. As long as it's the active form then you use a function that return the value. E.g.

Public Function ActiveFormControlValue(strControlName As String) As Variant
    ActiveFormControlValue = Screen.ActiveForm.Controls(strControlName).Value
End Function

Hakan