I'm new to VBA and Access in general and ran into this problem whilst trying to use the proposed alternate implementation from another question I'd asked (DLookup in Access not running until textBox clicked on in Form)
The code below runs, the issue is that Me.Key is different for each record being displayed in the form and running this in the form open event means it grabs only the first value assigned to Me.Key from the first record. How can i make this run so that Me.Key is different for each record/line being displayed?
Dim rs As DAO.Recordset Dim db As Database Dim qdf As QueryDef Dim prm As Parameter Set db = CurrentDb Set qdf = db.QueryDefs("[MF INCOME - STREAM MONTHLY]") For Each prm In qdf.Parameters prm.Value = Eval(prm.Name) Next prm Set rs = qdf.OpenRecordset(dbOpenDynaset) rs.FindFirst "[MyMonth]=10 AND [Org_Type]='" & Me.Key & "'" Me.Oct = rs!SumVal '...other month assignments
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