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:
Code:
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