Populating Fields of Continuous Form from VBA

[Miles R]

I have a continuous form with some fields populated directly from the data source of the form.


However, some fields need complicated calculation and I was hoping to populate them from VBA using something of the form:

DoCmd.GoToRecord , , acFirst
Do Until Recordset.EOF = True
  calculate value
  populate relevant field with value
  Recordset.MoveNext
Loop

This did not work too well as it seemed to populate each row with the same value - the last one it calculated.
Is it not possible to do this - does each field need to be bound directly to a field from the record source?
I seem to have forgotten much.

[CJ_London]

in principle the process is correct although docmd.gotorecord is a form action whilst the rest are recordset actions so the devil will be in the detail. A problem with your calculation perhaps, not saving the calculated value maybe, some other factor not disclosed. Or perhaps you really are mixing up forms and recordsets.

suggest replace DoCmd.GoToRecord , , acFirst with recordset.movefirst, see if that solved the problem.

[June7]

What are calculate value and populate relevant field with value?

Could probably just make a function and call from a textbox when needed instead of saving calculated value.

How are you declaring Recordset object? Probably need RecordsetClone.

Moving to first record on form has nothing to do with movement within recordset.

If you just want to update existing records with calculated data, it is usually not necessary to have those records displayed on form. What is on form that is needed for these calcs that is not in table?

[Micron]

Calculated fields cannot be bound. Unbound fields on a continuous form will all have the same values. You will have to do your calcs in a query and bind the form to that. You could do simple calcs using query fields, or the calculated query field could call a function (not a sub). Your continuous form should work OK then.

[Miles R]

Thanks to everyone who has replied. I think Micron has explained it, in that Unbound fields on a continuous forms all have the same value. That is what seems to be happening.
I can't do the calculations in a query, as some of the data I want to show involves looking up metadata from image files.
Probably the best I could do is to populate a temporary table with the data and then link the continuous form to that. This is a bit off an effort for something that I wanted to be quick and dirty.
The other option is to use a normal form and populate the data directly, but then I run into the other problem with the page length. I think I probably would need a form 100 inches long, but the maximum is about 22 inches.
Actually, I might investigate the idea of using functions in the continuous form and see if I can get that to work.

[Miles R]

Further to this, I have followed Micron's suggestion to use functions for some of the fields in the continuous form. These do all the calculations and look ups etc. based on parameters of the function set in the form field.
This now works as I wanted.