Error 2447 - There is an invalid use of the . (dot) or ! operator or invalid parentheses

[DC CS]

Hi all,
I have a form with multiple Textboxes. One of the textbox "arrays" (as I call them), has a control source as below:
=Val([Forms]![Frm_TyreForecast]![Frm_TyreForecastSubForm].[Form]![MM0_1])

In the form's Load Event, I do some calculations, but I receive the error "2447 - There is an invalid use of the . (dot) or ! operator or invalid parentheses"

Below the code

For i = 1 To 22 ===>   If Val(Me("QtyNeed" & i)) - Val(Me("SOH" & i)) < 0 Then
        Me("QtyPO" & i) = "0"
    Else
        Me("QtyPO" & i) = (Val(Me("QtyNeed" & i)) - Val(Me("SOH" & i)))
    End If
Next i

The line where the error occurs, is marked "===>" (Val(Me("QtyNeed" & i)))
The second part of the If statement (Val(Me("SOH" & i)) ) is fine and has the correct value.

If I put a physical number (ex. 2) into the control source of "QtyNeeded1", the calculations work fine, but of course the answers are wrong.



Much appreciated.

[June7]

Does calc in QtyNeeded1 return correct result?

This 'array' is on the main form?

I can't see issue with code. Could provide your db for analysis.

[Minty]

Is it QtyNeed or QtyNeeded ?
One or other is a typo?

[orange]

I don't think (Me("QtyNeed" & i)) is valid syntax.
You could use Me.QtyNeed or Me!QtyNeed

But perhaps you have a naming issue as Minty has asked.

[June7]

Good eye Minty!

Orange, it is valid syntax - shortened version of: Me.Controls("QtyNeed" & i)

[orange]

@June7
Thanks for the info.

[Micron]

I saw that too but didn't post because I didn't know what the error result would be. Now that I've tested with that syntax, it is 2465 "Microsoft Access can't find..." and not 2447.

[DC CS]

Hi all,
Sorry for the delay.

June7, the calculation in QtyNeed1, gives the Error 2447, but if I remove the control source and make the control source to a fix number (eg. 2) the calculation is done correctly, but the value will change as the data is analyzed, so I had a formula as the control source and then tried to do a calculation on the textbox. This seems to be the issue. Once I removed the formula from the textbox's control source, all calculations were running and is correct.

Thank you for everybody's input.

[Micron]

Not 100% sure of what your design structure is but it seems clear that a form/subform is involved.
I was able to create the error with improper design in two ways.
1) By using the subform reference syntax and pointing to the wrong place i.e. a control on the main form (thus pointing to the wrong form).
2) By using the correct reference (path) but misspelling the control name.

My point is that as long as the reference cannot be correctly evaluated by an expression I think you'd get that error. I wonder if that comes back to

Is it QtyNeed or QtyNeeded ?
One or other is a typo?

[June7]

I don't see how formula in textbox would be an issue. Code is populating field.

[Micron]

The code refers to the control. The control has an invalid expression. The code barfs.

[June7]

Oh, so back to the apparently bad control name reference. Got it. So how does removing calc from textbox get correct data saved to field since VBA needs calc value?

[orange]

Just a side note for consideration
In the

Me("QtyPO" & i) = "0"

This is assigning a text , NOT a numeric--- is that a contributing factor??

Also, DC DS was it/is it QtyNeed or QtyNeeded???

[Micron]

So how does removing calc from textbox get correct data saved to field since VBA needs calc value?

I think you're asking the OP because I have no idea. Without reviewing I have to wonder that if one is using vba, why not just do the calc in vba and assign it to the control but don't bind it to a field, otherwise a calc is being stored.
@orange - except that the issue was eliminated by replacing the other portion of the expression with a static value? I did wonder about the text zero but didn't think it was relevant to the reported error.

[orange]

You are correct Micron---it did work with a defined value(2).