VBA/Access RecordSet per form record problem

Answer 1

You could try the current event of the form, as suggested previously :)

Answer 2

I guess the Me.Key refers to a control located in the details section of your form. In this case, and in order to list all values taken by the control, you will need to browse all the records. One of the ways to do so can be:

Dim m_position as Long
for m_position = 1 to Me.recordset.recordcount
   me.seltop = m_position
   debug.print me.key
next m_position

Unfortunately your will see your screen blincker while browsing all the lines. You can off course find some 'screenFreezer' utilities for VBA on the net (there is one called LockWindowUpdate, as long as I can remember).

Another solution is to browse the clone of the underlying recordset (browsing the recordset will provoke the same screen behaviour as before). Supposing that the Me.Key control is bound to the "Key" column of the recordset, code could be:

Dim rsClone as DAO.recordset
set rsClone = Me.recordsetclone
if rsClone.EOF and rsClone.BOF then
Else
    rsClone.moveFirst
    Do while not rsClone.EOF
        debug.print rsCLone.fields("Key")
        rsClone.moveNext
    Loop
Endif
set rsClone = nothing

My favorite is the first one, with the "freeze"option added. Your code can manage the seltop and selheight values of the form. This means you can browse specifically records selected by users and/or, once all records browsed, go back to the original record selection.

EDIT:

Following @Ben's comment, I shall add that if your "myControl" control is in the details section and is unbound, you then will not be able to manage one value per row. The control will have the same value for all lines when the form is displayed as "continuous".

If your "myControl" control is bound to the "myField" field of a recordset, any of the following codes will increment "myControl" control value and "myField" field value at the same time. You will be than able to have a different value on each row:

Solution 1:

Dim m_position as Long
for m_position = 1 to Me.recordset.recordcount
   me.seltop = m_position
   me.controls("myControl") = m_position
next m_position

Solution 2:

Dim rsClone as DAO.recordset, _
    i as long

set rsClone = Me.recordsetclone
if rsClone.EOF and rsClone.BOF then
Else
    rsClone.moveFirst
    i = 1
    Do while not rsClone.EOF
        rsClone.fields("myField") = i
        rsClone.update
        rsClone.moveNext
        i = i+1
    Loop
Endif
set rsClone = nothing