Subform not displaying VBA record change

Question

I have an unbound form with 3 subforms. Each of the subforms have the same recordsource but are filtered on a field called CrntPhaseNmbr, that carries a simple numerical value of 1, 2, or 3 to show 3 different sets of data from the same query. I use VBA to actually update that field because the query used is a non-updateable query. I have a command button on subforms 1 and 2 that when clicked increases the value by 1 for a selected record, promoting it to the next group. On subforms 2 and 3 a command button reduces the value by 1 demoting the selected record to the previous group.

I have tried setting the recordsource for all 3 subforms to the query and then set the form filter to the particular value needed for each form. I have also tried not using the the filter and setting the recordsource to a SQL string with a where statement to filter the recordset. Either way makes no difference in the result.

The problem is that the CrntPhaseNmbr value gets updated without a problem but the subforms do not display the updated recordsets.

ResidentPhaseMove is stored in a module and for all 3 forms works without problem.

Here is the code for subform 2 which has both demote and promote command buttons (The code is identical for subforms 1 and 3 except they only have 1 button each, promote and demote, respectively):

Private Sub cmdDemote_Click()

    Dim intNewPhase As Integer
    Dim lngResidentID As Long
    Dim rstList As Object
    Dim strRcrdSrc As String
    Dim strRcrdSrc2 As String

    lngResidentID = txtResidentID
    intNewPhase = txtCrntPhaseNmbr - 1
    
    Application.Echo False

    strRcrdSrc = Me.RecordSource
    Me.RecordSource = ""
    strRcrdSrc2 = Forms!frmHouseAssignedPhases!ResidentsPhase1.Form.RecordSource
    Forms!frmHouseAssignedPhases!ResidentsPhase1.Form.RecordSource = ""
    
    Call ResidentPhaseMove(lngResidentID, intNewPhase)
    
    Call Me.Parent.ChkUpdateStatus
    
    Me!txtFocus.SetFocus
    Me.RecordSource = strRcrdSrc

    Me.Requery
    
    Forms!frmHouseAssignedPhases!ResidentsPhase1.Form!txtFocus.SetFocus
    Forms!frmHouseAssignedPhases!ResidentsPhase1.Form.RecordSource = strRcrdSrc2

    Forms!frmHouseAssignedPhases!ResidentsPhase1.Form.Requery
    Application.Echo True

End Sub

Private Sub cmdPromote_Click()

    Dim intNewPhase As Integer
    Dim lngResidentID As Long
    Dim rstList As Object
    Dim strRcrdSrc As String
    Dim strRcrdSrc2 As String

    lngResidentID = txtResidentID
    intNewPhase = txtCrntPhaseNmbr + 1
    
    Call ResidentPhaseMove(lngResidentID, intNewPhase)

    Application.Echo False

    strRcrdSrc = Me.RecordSource
    Me.RecordSource = ""
    strRcrdSrc2 = Forms!frmHouseAssignedPhases!ResidentsPhase3.Form.RecordSource
    Forms!frmHouseAssignedPhases!ResidentsPhase3.Form.RecordSource = ""
   
    Call ResidentPhaseMove(lngResidentID, intNewPhase)
    
    Call Me.Parent.ChkUpdateStatus

    Me!txtFocus.SetFocus
    Me.RecordSource = strRcrdSrc

    Me.Requery
    
    Forms!frmHouseAssignedPhases!ResidentsPhase3.Form!txtFocus.SetFocus
    Forms!frmHouseAssignedPhases!ResidentsPhase3.Form.RecordSource = strRcrdSrc2

    Forms!frmHouseAssignedPhases!ResidentsPhase3.Form.Requery
    Application.Echo True

End Sub

Sub ChkUpdateStatus()

    Dim varState

ChkAgain:
    varState = SysCmd(acSysCmdGetObjectState, acStoredProcedure, "ResidentPhaseMove")

    If varState = 0 Then
        WaitSeconds 2
    Else
        GoTo ChkAgain
    End If

    Me.Requery

End Sub

Sub ResidentPhaseMove(lngResidentID As Long, intPhase As Integer)

    Dim sqlMove As String
    Dim cnnMain As ADODB.Connection
    Dim cmdMove As ADODB.Command
    Dim rstMove As ADODB.Recordset
    
    Set cnnMain = New ADODB.Connection
    cnnMain.ConnectionString = cnstCnctMain
    cnnMain.Open

    sqlMove = "UPDATE Residents SET Residents.CrntPhaseNmbr = " & intPhase _
                    & " WHERE (((Residents.ResidentID)=" & lngResidentID & "));"

    Set cmdMove = New ADODB.Command
    cmdMove.ActiveConnection = cnnMain
    cmdMove.CommandText = sqlMove
    cmdMove.Execute

    Set cmdMove = Nothing
    cnnMain.Close
    Set cnnMain = Nothing

End Sub

I'm sure the problem is that the subforms are displaying before the change is made, but I haven't figured out how to get the subforms to requery after the change is made.

Thank you for any help you may be able to provide.

Answer 1

I haven't found any way to cause the data to be displayed after updates while the RecordSource is a non-updatable query where the actual edits were done with VBA not directly on the form. As a result, I have created a code routine to create a temp table composed of the fields in the non-updatable query. I'm using that as the RecordSource for the subforms.

With this RecordSource the answer that was given by Gustav works fine. One small adjustment. With each subform the requery should include itself. i.e., for the code in subform 1 the following requery statements should appear:

Me.Requery
Me.Parent!NameOfOtherSubformControl2.Form.Requery
Me.Parent!NameOfOtherSubformControl3.Form.Requery

Answer 2

I have the feeling, that you skip most of this if you simply updated the RecordsetClone of the subform like this:

Private Sub cmdDemote_Click()

    Dim intNewPhase     As Integer
    Dim lngResidentID   As Long

    lngResidentID = Me!txtResidentID.Value
    intNewPhase = Me!txtCrntPhaseNmbr.Value - 1

    ResidentPhaseMove lngResidentID, intNewPhase

End Sub

' Vice-versa for promoting.


Private Sub ResidentPhaseMove(ByVal lngResidentID As Long, ByVal intPhase As Integer)

    Dim Records     As DAO.Recordset

    Dim Criteria    As String

    Set Records = Me.RecordsetClone

    Criteria = "ResidentID = " & lngResidentID & ""
    Records.FindFirst Criteria
    Records.Edit
        Records!CrntPhaseNmbr.Value = intPhase
    Records.Update 
    Records.Close

    ' Exclude the current subform.
    Me.Parent!NameOfOtherSubformControl1.Form.Requery
    'Me.Parent!NameOfOtherSubformControl2.Form.Requery
    Me.Parent!NameOfOtherSubformControl3.Form.Requery

End Sub