Calculated Field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott - 08 May 2005 19:39 GMT

Greetings
I have a form and sunform and would like to calculate a control in the
subform. The subform displays records in a Datasheet view.
I place an unbound control on the subform and set its control source to do
the calculation. Some of the data required for the calculation is from a
table not in the form so I used the DLookUp function to get the external
data. So the control source look like this:

=DLookUp("[Fit0]","tblRmpsCoil","[CellNo]=" & Forms!frmTestRamp!Cell_No &
"And" & "[SeqNo]=" & Forms!frmTestRamp!Seq_No)*[I]

This method works fine.

The actual calculation is a fourth order polynominal so the control source
gets rather long with lots of DLookUp's and is kinda bulky and slow. I need
to do some error checking as well

As an alternative I am trying to do the calculation in a procedure in the
subforms On Current event. Instead of DLookUp I create an ADO record set to
get the external data.

My problem is when I do the calculation and assign it to the unbound control
all the records have the same calculated value based on the current record.
As you move thru thre records all the calculated controls are the same and
based on the current record. The On Current event is as follows:

Private Sub Form_Current()
Dim CurCellNo As Integer
Dim CurSeqNo As Integer
Dim rsRmpsCoilPara As ADODB.Recordset

If CurCellNo <> [Forms]![frmTestRamp]![CellNo] Or CurSeqNo <>
[Forms]![frmTestRamp]![CellNo] Then
CurCellNo = [Forms]![frmTestRamp]![CellNo]
CurSeqNo = [Forms]![frmTestRamp]![SeqNo]

'Open Rmps Coil Pramaters Record Set

Set rsRmpsCoilPara = New ADODB.Recordset
With rsRmpsCoilPara
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockReadOnly
.Open "SELECT tblRmpsCoil.CellNo, tblRmpsCoil.SeqNo,
tblRmpsCoil.Alpha, tblRmpsCoil.Fit0, tblRmpsCoil.Fit2, tblRmpsCoil.Fit4 FROM
tblRmpsCoil WHERE tblRmpsCoil.CellNo = " & CurCellNo & " AND " &
"tblRmpsCoil.SeqNo= " & CurSeqNo, CurrentProject.Connection
.ActiveConnection = Nothing
End With
End If

'This is where the problem is
Text44 = [I] * rsRmpsCoilPara!Fit0

End Sub

Where Text44 is the calculated controi, And [I]is a field on the subform
used in the calculation
Am I using the wrong event or something.

Thanks for any help

Scott

Reply to this Message

jl5000 - 08 May 2005 20:28 GMT

Try moving the formula to the query, still using the dlookup, add an index to
the table tblRmpsCoil for fields [CellNo] and [SeqNo], to have this working
use the query fields in the subform to feed the criteria in the dlookup

Signature

jl5000
<a href="http://joshdev.com"></a>

> Greetings
> I have a form and sunform and would like to calculate a control in the
[quoted text clipped - 59 lines]
>
> Scott

Reply to this Message

SteveS - 08 May 2005 21:18 GMT

> Try moving the formula to the query, still using the dlookup, add an index to
> the table tblRmpsCoil for fields [CellNo] and [SeqNo], to have this working
[quoted text clipped - 63 lines]
> >
> > Scott

Scott,

No, you are not doing anything wrong, but there is a better way.

If you look at the form in design view, there is one control (textbox) per
field that you want to display. If you have 10 records, then you see 10
values for each bound control. An unbound control will also be shown 10 times
for the 10 records, but since it has no control source, it shows the same
value in it (be it a constant or a calculation) 10 times.

Since you want to do error checking, put a user defined function (UDF) in a
standard module and pass the values from whatever query fields or form
controls needed and do the calculation/error checking in the function. The
values from a different table are needed, they can be retreived using a DAO
recordset.

The record source for the sub-form should be a query. In the query, add
something like:

calcRmpsCoilPara: MyCustomCalc([I],[some val])

where "MyCustomCalc" is the name of the UDF and two values are being passed
(in this example).

In the UDF, you can open a recordset (one or more) and use those values and
values passed to the function to do the calculations. (don't you just love
those custom functions???)

The last step is to bind the unbound control to the (query) field
"calcRmpsCoilPara" (without the quotes). Just think of queries as "virtual
tables".

All should be well with the universe......<g>

HTH
---
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Reply to this Message

SteveS - 09 May 2005 00:22 GMT

Boy, I messed up on the previous post. Typed in Notepad and pasted without
really looking at where I was replying (using the web based interface at work
during lunch).
Sorry
---
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Reply to this Message

Scott - 09 May 2005 13:46 GMT

Thanks for your help folks. Your comments were right on the money. Using the
query as a virtiual table is the ticket. I see how I can improve some other
things in my application. The Access light is getting brighter and the
\borland\corel\paradox light is getting dimmer. Maybe I'll even be able to
answer some questions soon.

Steve S I didnt see anything wrong with your post, It worked for me

Thanks again.

Reply to this Message

SteveS - 09 May 2005 16:57 GMT

> Thanks for your help folks. Your comments were right on the money. Using the
> query as a virtiual table is the ticket. I see how I can improve some other
[quoted text clipped - 5 lines]
>
> Thanks again.

I just meant that I was trying to get the reply done before my lunch break
was over; I meant to reply to your post, not jl5000's post.

> things in my application. The Access light is getting brighter and the
> \borland\corel\paradox light is getting dimmer.

Ditto with me, but I was in the Xbase arena... took a loooong time for the
event driven model to sink in.

;-)
---
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Reply to this Message