Calculated field in recordset

[bilalo]

Hi, I have a calculated field named RemainingStock in a subform called MyItems
When I try to print the values of RemainingStock, it shows an error "Item not found in this collection". But if I try to print any other non-calculated field, everything works fine.

I am using the following code:

Dim rs As Recordset
Set rs = MyItems.Form.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
   MsgBox rs!RemainingStock
   rs.MoveNext
Loop
rs.Close

Any help please?

[Welshgasman]

So it would be unbound and not in the recordset?

[ranman256]

in the query is the calc field:
RemainingStock: field1+field4
or
RemainingStock: forms!fMyForm!txtBox

[Gicu]

If you do the calculation in the subform itself (in the control source of the textbox control named RemainingStock) then it will not be part of the recordset. You can redo the calculation in your loop using the recordset fields or do it in the subform's record source query as suggested by Ranman.

Cheers,

[bilalo]

in the query is the calc field:
RemainingStock: field1+field4
or
RemainingStock: forms!fMyForm!txtBox

In the form, it is a dlookup value

[bilalo]

If you do the calculation in the subform itself (in the control source of the textbox control named RemainingStock) then it will not be part of the recordset. You can redo the calculation in your loop using the recordset fields or do it in the subform's record source query as suggested by Ranman.

Cheers,

Yes, I tried this method, I added my dlookup equation inside the loop and it worked, but I think it is time consuming because the computer is making the calculations twice. One time in the calculated field and another time inside the loop.
So, I was thinking, the values that I want are already displayed in the calculated field, so can I have a direct access to them instead of recalculating them?

[bilalo]

So it would be unbound and not in the recordset?

It is a dlookup equation in a subform.

[Gicu]

You're not really showing or explaining what you're trying to do, but you can try to reference the control itself (Me.RemainingStock) but not in a loop like you have in the original post; the control does not get calculated until the record is made current and the previous value is long forgotten. So I don't think you would have any other option other than the one discussed, do it in the record source query or again in your loop. And I wouldn't worry about the wasted resources with nowadays computer power unless your dLookup is based on hundreds of thousand of records or more....
Cheers,