Hello Forum,
I'm trying to update a form's recordset (after I update it) using VBA. My code doesn't break at any point during execution but the form doesn't display the new values.
As you can see in the code below, I have written a simple loop to print the underlying records to the Immediate window, which shows the data as I expect it to be. As you can also see I've tried to refresh the form using all methods I can think of.
Code:
Public Sub CompareTableValues(ByRef rs1 As ADODB.Recordset, ByVal rs2 As ADODB.Recordset, ByRef FrmToUpdate As Form) 'rs1 is the one to update; rs2 is the reference.
rs1.Close
rs1.LockType = adLockBatchOptimistic
rs1.Open
If Not rs1.BOF Then rs1.MoveFirst
Do Until rs1.EOF
rs2.Find rs2.Fields(0).Name & "='" & rs1.Fields(0).value & "'", 0, adSearchForward, adBookmarkFirst
If rs2.EOF = True Or rs2.BOF = True Then
rs1.Fields(3).value = "No"
Else
rs1.Fields(3).value = "Yes"
End If
rs1.MoveNext
Loop
'move cursors to first record
rs1.MoveFirst
rs2.MoveFirst
rs1.Update
Set FrmToUpdate.Recordset = rs1
FrmToUpdate.Requery
FrmToUpdate.Repaint
FrmToUpdate.Recalc
FrmToUpdate.Refresh
'checks values in form recordset
Do Until FrmToUpdate.Recordset.EOF
For i = 0 To FrmToUpdate.Recordset.Fields.Count - 1
Debug.Print FrmToUpdate.Name & "; Record (" & frmToUpdate.AbsolutePosition & "); " & FrmToUpdate.Recordset.Fields(i).Name & ": " & FrmToUpdate.Recordset.Fields(i).value
Next i
FrmToUpdate.Recordset.MoveNext
Loop
End Sub
If anyone can see what I'm missing (i.e. why isn't the data displayed on my form updating while its recordset is appearing to have been updated) I'd be most grateful (so I can move onto the next issue!).
Many thanks