CODEWITHSUNDEEP
formsms-accessreference
UglySwede
UglySwede

Reputation: 459

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

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

Upvotes: 9

Views: 40680

Answers (6)

David Letham
David Letham

Reputation: 1

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

Upvotes: 0

Albert D. Kallal
Albert D. Kallal

Reputation: 48999

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

Upvotes: 3

DevMasterBob
DevMasterBob

Reputation: 1

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

Upvotes: 0

Jeremy Cook
Jeremy Cook

Reputation: 22063

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.

Upvotes: 1

Philippe Grondier
Philippe Grondier

Reputation: 11138

'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.

Upvotes: 7

Fionnuala
Fionnuala

Reputation: 91326

You can use:

=MyFunction([Form])

The code would be:

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

Upvotes: 10

Related Questions