Use custom function in Expression Builder

[MattLewis]

I created a custom function in a VBA module. How do I now use that function for a table column that is type "Calculated". All the tutorials online seem to show the custom functions as magically showing up in the "Expression Builder" window, but mine is not listed there.

[MattLewis]

Nevermind. I'll just call the function from a query. Man.....Access is frustrating.

[pbaldy]

Which tutorials? It is documented that you can't use custom functions in a calculated field.

[SierraJuliet]

I personally would not use a calculated field. There are many reasons but one is migrating backends and the host db architecture does not like it. Instead of using a calculated field just leave the attribute name (i.e. column) the same and change the data type to whatever data it will be holding (i.e. integer). Now set that text box's [Event Procedure] on GotFocus() by selecting it on the form. Open VBA editor for that GotFocus [Event Procedure] and type out the code. Here is example.

Private Sub txtCalculatedTextBox_GotFocus()

Me!txtCalculatedTextBox = ((txtValueTextBox1 + txtValueTextBox2) * txtValueTextBox3)

End Sub

Now every time the field is clicked it will automatically calculate based on the values of those fields (assuming you already entered all of those values first) and yet your table attribute can be a standard data type compatible with most database architectures. It will work like a charm every time. Note: if you are driving for a custom function then possibly set the calculated attribute data type to text (i.e. string) instead of integer.