How To Read SubForm Records In Order, From Main Form

Does anyone know why reading the records in the subform only works on the first record shown?

The only row you can reference in a form from outside the form is the "current" record.  If you want to process subform records, you would need to use the subform's recordsetClone.

Processing a recordset this way is unnatural.  Forms are meant for human interaction.  If you want to process a recordset in VBA, use DAO (or ADO if you prefer) to open a recordset and read through it one record at a time.  As long as the recordset is based on a querydef rather than a tabledef, you can select rows based on criteria and you can use an order by clause to ensure it is always sorted.  The qd.Parameters (I showed 2, you can have more or less or even none) allow you to pass criteria into the query.

But more often than not, you don't need VBA or a code loop, you can use an action query to add, change, or delete whatever you want.

Dim db AS DAO.Database
Dim qd AS DAO.Querydef
Dim rs AS DAO.Recordset

Set db = CurrentDB()
Set qd = db.Querydefs!nameofquery
       qd.Parameters!field1 = Me.somefield
       qd.Parameters!field2 = Me.someotherfield
Set rs = qd.OpenRecordset

    Do until rs.EOF
        '' do something
        rs.MoveNext
    Loop

This modification is tested and works here:

Private Sub readThruTASubformEntries()

    Dim rs As DAO.Recordset

    Set rs = Me![TAX AUTHORITIES ON THIS PLAN].Form.RecordsetClone

    If rs.RecordCount > 0 Then
        rs.MoveFirst
        While Not rs.EOF
            MsgBox "User Assigned Priority: " & Nz(rs!UserAssignedPaymntPriority)
            rs.MoveNext
        Wend
    End If
    rs.close

    Set rs = Nothing

End Sub

Select allOpen in new window

/gustav

Thanks you both for your suggestions.

Pat:  I'm not familiar with the querydef technique you describe.  I set up a query 'qryInstallPay_TaxAuthorities_Sub' as I think it should be, just need one parameter:

PARAMETERS ParmInstallPayHdrID Long;
SELECT tblInstallPay_TaxAuthorities.InstallPayTAID, tblInstallPay_TaxAuthorities.InstallPayHdrID, tblInstallPay_TaxAuthorities.PropertyID, tblInstallPay_TaxAuthorities.TaxAuthorityID, tblInstallPay_TaxAuthorities.UserAssignedPaymntPriority, tblInstallPay_TaxAuthorities.MuniCode, tblInstallPay_TaxAuthorities.LotBlock, tblInstallPay_TaxAuthorities.TieBreaker, tblInstallPay_TaxAuthorities.BlockSuffix, tblInstallPay_TaxAuthorities.TaxTypeID, tblInstallPay_TaxAuthorities.Full_Int_Add_TypeID, tblInstallPay_TaxAuthorities.PayoffAtTimeOfCreation, tblInstallPay_TaxAuthorities.PayStatusID, tblInstallPay_TaxAuthorities.DateAdded, tblInstallPay_TaxAuthorities.UserAdded, tblInstallPay_TaxAuthorities.DateRevised, tblInstallPay_TaxAuthorities.UserRevised, tblInstallPay_TaxAuthorities.InstallPayTATimeStamp
FROM tblInstallPay_TaxAuthorities
WHERE (((tblInstallPay_TaxAuthorities.InstallPayHdrID)=[ParmInstallPayHdrID]))
ORDER BY tblInstallPay_TaxAuthorities.UserAssignedPaymntPriority, tblInstallPay_TaxAuthorities.TaxTypeID;

Select allOpen in new window

And used this code to try and read, passing the parameter from the main form:

Private Sub readThruTASubformEntries_wQueryDef()

Dim db As DAO.Database
 Dim qd As DAO.QueryDef
 Dim rs As DAO.Recordset

 Set db = CurrentDb()
 Set qd = db.QueryDefs!qryInstallPay_TaxAuthorities_Sub
        qd.Parameters!ParmInstallPayHdrID = Me.InstallPayHdrID
     '   qd.Parameters!Field2 = Me.someotherfield
 Set rs = qd.OpenRecordset

     Do Until rs.EOF
            MsgBox "User Assigned Priority: " & Nz(rs!UserAssignedPaymntPriority)
         rs.MoveNext
     Loop
    '
rs.Close
Set rs = Nothing
     
End Sub

Select allOpen in new window

I got an error message on ' Set rs = qd.OpenRecordset'.  The message is:  You must use the dbSeeCHnages option with OPenRecordset when accessing a SQL Server table that has an identity column.


Gustav:
Yours works as is but is there a way to order the results other than the order they appear in the subform?  Any revisions  I attempted to accomplish this did not even compile.

Private Sub readThruTASubformEntries()

    Dim rs As DAO.Recordset

    Set rs = Me![TAX AUTHORITIES ON THIS PLAN].Form.RecordsetClone
    
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        While Not rs.EOF
            MsgBox "User Assigned Priority: " & Nz(rs!UserAssignedPaymntPriority)
            rs.MoveNext
        Wend
    End If
    rs.Close

    Set rs = Nothing

End Sub

Select allOpen in new window

You need to do exactly what the error message says:

Set rs = qd.OpenRecordset(dbSeeChanges)

There was nothing in the post to indicate the BE was SQL Server

Thanks for the follow up but the new statement  'Set rs = qd.OpenRecordset(dbSeeChanges)'  is giving me a '3001' invalid argument.

Thanks you

You are welcome!

/gustav