DAO Recordset being updated, but updated fields do not reflect the change.

[GraeagleBill]

I have two DAO recordsets, "rsFr" and "rsTo". The code below is supposed to populate fields in "rsTo" from "rsFr" and set the remaining fields to zero in "rsTo" when "rsFr" becomes empty. The "rsTo" will always be >= "rsFr".

The innermost loop While Not rsFr.EOF runs okay in debug, but execution trace seems to disappear when the innermost Wend is reached.

Two issues remain: 1) The display does not update in spite of the Requery, but does when the focus is moved elsewhere.
2) When "rsFr" becomes empty (EOF), the remaining records in "rsTo" are not cleared (Set to zero, etc) I.e., outermost While doesn't loop.

intLocID is set earlier in the app as well as the global variable gblRetreatYear. They are not problematic in this issue.

Here's the function:

Public Function Populate()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=**=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Ready or not, here the selections made are used to populate current retreat with the rooms that are available
' to be assigned to the retreat attendees.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=**=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim strMsg As String
Dim strHdr As String

Dim rsFr As DAO.Recordset
Dim rsTo As DAO.Recordset

Dim strSQLFrom As String
Dim strSQLTo As String

If DMax("[RoomNumber]", "Groupings", "[Retyear] = """ & gblRetreatYear & """") > 0 Then
    strMsg = " already populated.  Do you want to completely RE-POPULATE?"
    strHdr = "Populate retreat with available rooms"
    If MsgBox(gblRetreatYear & strMsg, vbYesNo, strHdr) = vbNo Then Exit Function
End If

strSQLFrom = "SELECT LocID, RoomID, RoomCap, RoomPrem, RoomSel FROM tblLocRooms " & _
             "WHERE (((LocID)=" & intLocID & ") AND ((RoomSel)=ChrW(9658))) ORDER BY RoomID;"
Set rsFr = DBEngine(0)(0).OpenRecordset(strSQLFrom)

strSQLTo = "SELECT GrpID, RetYear, RoomNumber, RoomCapacity, WithView FROM GROUPINGS WHERE (((RetYear)=""" & gblRetreatYear & """)) ORDER BY GrpID;"
Set rsTo = DBEngine(0)(0).OpenRecordset(strSQLTo)

rsFr.MoveFirst
rsTo.MoveFirst

    While Not rsTo.EOF
        While Not rsFr.EOF
        rsTo.Edit
        rsTo!RoomNumber = rsFr!RoomID
        rsTo!RoomCapacity = rsFr!RoomCap
        rsTo!WithView = rsFr!RoomPrem
        rsTo.Update
        
        rsTo.MoveNext
        rsFr.MoveNext
        Wend
    rsTo!RoomNumber = 0           < Beginning here, the remaining records in "rsTo" should be essentially reinitialized.
    rsTo!RoomCapacity = 0
    rsTo!WithView = False
    rsTo.Update
    
    rsTo.MoveNext
    Wend


rsFr.Close
Set rsFr = Nothing

rsTo.Close
Set rsTo = Nothing

Me.Child58.Form.Requery       < I expected this requery to update the display and reveal the updated fields

End Function

[pbaldy]

I can't recall if it's necessary, but you don't have

rsTo.Edit

for the remaining records. I would also expect the requery to work, presuming that's the name of the subform control. Can you attach a sample of the db here?

[Micron]

When you get here

rsTo!RoomNumber = 0
rsTo!RoomCapacity = 0
rsTo!WithView = False
rsTo.Update

you have reached EOF, so at most I presume you can update only one record. Then you MoveNext on rsTo but not on rs.From, so you are out of sync ...

rsTo.MoveNext
Wend

I don't get why you'd make
rsTo!RoomNumber = rsFr!RoomID
rsTo!RoomCapacity = rsFr!RoomCap
then make rsTo!RoomNumber and rsTo!RoomCapacity = 0 in the next step. Why not just set rsTo to be 0 in the first place? Or are you supposed to be setting rs!From to zero?

[pbaldy]

you have reached EOF, so at most I presume you can update only one record. Then you MoveNext on rsTo but not on rs.From, so you are out of sync ...

I have a gut feeling I know what Bill is trying to do, though not why. At the point you mention the "from" recordset is at EOF, but the "to" recordset is not (necessarily). So the remaining code is to update the "to" records that were not affected during the loop of "from" records. In other words, the "to" recordset has 10 records and the "from" recordset has 7, so that code is meant to update records 8-10.

An alternative might be running an update query that affected all the to records, then updating the records in the from loop as is currently done.

[Micron]

As long as there will always be more To records than From, I see your point. I also now think you nailed the 0 update cause, because when you exit the inner loop, there is no call to update the recordset in the outer loop. Since moving off of the record works, the reference to the subform may be the issue. I would try a more explicit reference to the subform, such as

Forms("frmName").Controls("subFormControlName").Fo rm.Requery

I also wondered why it's necessary to use this complex approach over queries.
**For some reason, I cannot eliminate the space in .Form; it does not appear in either standard or advanced views, just after posting.

[pbaldy]

Bill's code is certainly dependent on there being more "to" than "from".

The space you reference is something I've seen before. It seems like the forum software will only allow a certain number of characters without spaces before inserting one itself. Testing, no spaces:

12345678901234567890123456789012345678901234567890 12345678901234567890123456789012345678901234567890 1234567890

Edit: apparently the number is 50.

[Micron]

Aha! So maybe code tags would prevent that...

[pbaldy]

I think so:

12345678901234567890123456789012345678901234567890123456789012345

Edit: Yup, you're right!

[GraeagleBill]

The "From" recordset is guaranteed to have less or equal number of records as in the "To" recordset................because it's both inherent in the DB plus I check it as part of initial conditions.

After I did a "compress and repair", the problem went away. For the life of me, I have no idea what might have been causing the faulty behavior. I tested the functionality every way I could think of and cannot get the originally posted failures to reoccur.

I did not dabble with the alternative idea about the Requery that Micron posted.