Access 2016 Subform VBA Using Double Constant as Object?

0
votes

So I have a subform VBA function that looks like this:

Function GetVariable() As Double

    '  <control>   = DLookup("<table var>","<table>", "<other table var>  = <control>" 
    [someFunction] = DLookup("[Var]", "[tblExample]", "[tblExampleVar] = [subFormControl]")

    ' (return)  = (   <control>    - <otherControl>) / 12345.12
    GetVariable = ([finalPosition] - [someFunction]) / 12345.12

End Function

And when I open the parent form (the form that contains this subform), I get the error, "Run-time error '2447' There is an invalid use of the . (dot) or ! operator or invalid parentheses."

What I gather from this is that Access is interpreting 12345.12 as an object and I do not understand why. When I run this subform on its own it works, but when it is a subform it does not. Has anyone dealt with this before?

Extra information: I have two subforms in this parent form that use the same calculation, both repeated in their form-specific VBA, and I do not think that they would conflict with one another because they do not share scope. So my conclusion remains that Access is trying to use 12345.12 as (object).member.

Thanks for reading.

2
vbaobjectms-accessdouble
Access isn't using the 12345.12 value - it's not even seeing it. Access only sees/evaluates the bracketed expressions. IIRC a function that is meant to be invoked from an Access document (form, report, ..query) needs to return a Variant - but don't quote me on that, I don't use Access. Does it still blow up if you change the function's return type to Variant? -- edit: scratch that, I missed the part where it works when used on its own. – Mathieu Guindon
Also the culprit might be [someFunction] – Mathieu Guindon
Show some examples of values for each of these fields. You have a table named Table? – June7
So you are referencing controls (Var, Table, tblExampleVar) on form to dynamically change fields and table? So these are all variable inputs? Why would there be more than 1 table? I think you now show the type of value retrieved but not what those values look like? Unless the values are literally the strings table var and other table var? – June7

2 Answers

0
votes

Try to take care of Null values and to be more specific.

Also, a decimal value must be concatenated as a string with dot decimal separator:

Function GetVariable() As Double

    If Not IsNull(Me![subFormControl].Value) Then
        Me![someFunction].Value = DLookup("[Var]", "[tblExample]", "[tblExampleVar] = " & Str(Me![subFormControl].Value) & "")

        If Not IsNull(Me![finalPosition].Value - Me![someFunction].Value) Then
            GetVariable = (Me![finalPosition].Value - Me![someFunction].Value) / 12345.12#
        End If
    End If

End Function
0
votes

References that work when a form opens independent won't necessarily work when that same form is used as a subform. That requires referencing to include the subform container name.

I don't see how running form as standalone could return correct data. I presume [subFormControl] is a field or control on form. This is a variable input. Variable must be concatenated in the DLookup() WHERE condition expression.

It seems these fields return names of controls or table. Do these names have spaces or punctuation/special characters (other than underscore). Really should not use in names nor use reserved words as names. If you do, need to delimit with [].

If you are referencing fields/controls on form to build a DLookup(), then all the inputs are variables and should be concatenated.

[someFunction] = DLookup("[" & [Var] & "]", "[" & [Table] & "]", "[" & [Condition] & "] = '" & [subformControl] & "'")

Whether or not delimiters are needed (and which ones) depends on the field type of the field that will be returned by the Condition input in the filter criteria.