Continuous Form - Calculaing a Value

[ETCallHome]

Hi,

I tried searching the internet and tried a few examples, but still cannot get it working. Here is my problem! I have a continuous form that displays Tbl.fldA, Tbl.fldB, Tbl.fldC, Form.fldD. what I am trying to do, is put a calculated value into Form.fldD based on the information in fldA - C, i.e.

Form.fldD = (Tbl.fldA - Tbl.fldB) * Tbl.fldC

I put this into the Open Form event, but the value it calculates is based on the first record in the form and populates this value to all the other records on the form.

I then created a private Function within the form as follows:

Function MyTest(A as double, B as double, C as double) as double
MyTest = (A - B) * C
End Function

In the controlsource of Form.fldD I put the following expression:

=MyTest([Tbl.fldA]+[Tbl.fldB]+[Tbl.fldC])

It accepts the expression, but when I run the form #Name? appears in Form.fldD

I would like to do various are things with the records on the continuous form, such has hi-lite fields if fldD is less then Zero, etc

Please help... I am aware that in a continuous form you only have 1 current record, even if it is displaying 100's. but not all the records will have a zero value...

Thanks


Earl

[nicknameoscar]

I tried doing similar things with continuous forms and ran into the same problem. I never found a solution (not that one doesn't exist, I just never found it). If memory serves me correctly the forms I used were based on queries. I ended up modifying the query to give me the totals I was looking for.

For highlighting of certain controls look at Conditional Formatting. It is under the Design tab when the form is in Design View.

[June7]

Not understanding the issue. Continuous form or otherwise, table or query, expression in textbox ControlSource should calculate just fine. Although might be better to name the other controls different from the field they are bound to then refer to the textbox names in the expression:
=(tbxA - tbxB) * tbxC

[nicknameoscar]

Sorry, my mistake. I somehow thought the subject was pertaining to unbound controls on a continuous form.

[ETCallHome]

Hi,

I did call my textbox names differently, let me give a better example than the one before.

Table1 - 3 fields call OldQty, NewQty,Price
Texbox Name - txtOldQty bound to OldQty, txtNewQty bound to NewQty, txtPrice bound to Price

Form1 - 1 field call txtTotal and is unbound

Function MyTest(A as Double, B as Double, C as Double) as Double
MyTest = (A -B) * C
end function

ControlSource of txtTotal
=MyTest([txtOldQty]+[txtNewQty]+[txtPrice])

As mentioned, the expression is accepted, but when I open the form txtTotal displays #Name?

Maybe this will clarify the problem

Earl

[ETCallHome]

Hi,

Figured the problem out...

ControlSource Property of txtTotal should be

=MyTest([txtOldQty],[txtNewQty],[txtPrice] and not

=MyTest([txtOldQty]+[txtNewQty]+[txtPrice] and not

Thanks for all your advice and taking the time to investigate my problem.

Earl

[June7]

I should have noticed that error in the function call syntax but I was focusing on the point there should be no need for the function. This just slows down the process. This is a simple calculation and can be accomplished right in the ControlSource.