How to link current form with expression in Microsoft Access?

Question

I am not an access expert and I have (I hope!) A simple question ...

I have a form with several entries. In some text blocks, I simply represent the values ​​from the base table, so they are bound to the corresponding fields.

But some text fields must contain calculated values. Some calculations are complex and contain many fields from the table. I am writing a calculation as a function of VBA. I could enter something like "Control Source":

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

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

 =MyFunction([Forms]![MyForm]) 

But I do not like to call a form in a call. There is no way to send the "current form" as an argument to a function? In VBA, you simply use the keyword "I", for example, "I! [Field1]". But it seems that the "I" is not accepted in the expression.

Is there any other way to refer to the current form in an expression?

(This is a cosmetic question, I know, but it’s not very good programming to use “[Form]! [MyForm].” Later you will copy the controls to another form and forget to change the name in the expression ...)

Thank you for your help !:-)

/ Anders

Answer 1

You can use:

 =MyFunction([Form]) 

The code:

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

Answer 2

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

My favorite way to access 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 can create a custom object. The required fields can then be considered as internal properties defined in the Class_Initialize subclass of the corresponding object.

Answer 3

If you use code on the form, then "me" refers to the current form. So

 Call SomeFunction(me) 

However, the self cannot be used in the context of an expression bound to a text field.

In this case, you can either select the current screen in the routine with screen.activeform as suggested.

I often go:

 Dim f as form Set f = screen.ActiveForm 

Answer 4

 Screen.ActiveControl.Parent ' or someControlVariable.Parent 

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

Answer 5

In ACC2013, I could not use fionnuala's answer. So I changed to:

Event handler in the Property-Window:

 =MyFunction() 

My code is:

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