Loop through subform records

[asmith]

Hey Guys,

I'm having a dumb moment, but can someone help me to remember how to use the docmd.gotorecord ...... next record on a subform. I can code it fine to go through the records on the main form but not the subform.

I think you have to set the focus on the subform first but i'm forgetting how to do that also.

Your help is greatly appreciated.

Thanks
Andrew

[TheShabz]

me.subform.setfocus ?

[ajetrumpet]

I don't think you can use that command on a main form, can you?

it's part of the 'docmd'. there is no argument for a subform, just a form name. maybe I'm mistaken...

[asmith]

Thanks guys,

the focus command worked perfectly but for some reason the standard docmd.gotorecord didn't work but DoCmd.RunCommand acCmdRecordsGoToNext did. I have no idea why but it works.

Thanks again
Andrew

[ajetrumpet]

it doesn't work because I believe the docmd, when used with objects and no variables, only refers to the active object. but again, I could be wrong.

could you post your solution please? I may need it in the near future. thanks!

[asmith]

Sure,

This is part of the code i'm building. I will finish the loop and post the finished product tomorrow.

Private Sub Comment_Change()
'declare variables
Dim rcount As Integer

'count number of records in subform - may be more effecient to do after set focus
rcount = DCount("[LOC/Trust Number]", "[SICS ID Detail]", "[SICS ID Detail]![LOC/Trust Number] = Forms![Input Form]![LOC/Trust Number]")

'set focus on subform
Me.Input_SubForm.SetFocus

'put loop code here
        
        'put copy code here to push records to history table
        
        'go to next record
        DoCmd.RunCommand acCmdRecordsGoToNext

End Sub

[asmith]

Hi guys,

As promised, here is the updated code with the loop. What this does is finds the number of records in the subform, and loops through them, copying information from the table that feeds the subform to a history table so that the subform can be overwritten. This is all done when someone hits the "finalized button". I'm also working on moving information from the history table to a backup table incase someone hit's finalize and says yes to the prompt accidentaly.

Thanks
Andrew

Private Sub Status_Change()
'variables
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Status As String
Dim SQL As String
Dim Time As Date
'default for variables
Status = Me.Status
'if statement to determine if it is approved in TT or Finalized
If Status = "Pending TT Approval" Then
    'do nothing
    
ElseIf Status = "TT Approved" Then
    'loop to confirm ticket is approved
    If MsgBox("Please confirm this LOC is approved in Team Track", vbYesNo, "TT Approved") = vbNo Then
        Me.Status = "Pending TT Approval"
    Else
        'no action needed
    End If
     
    
ElseIf Status = "Final" Then
    'If MsgBox("Are you sure that this LOC is fully approved?" & vbCrLf & "Note that once finalized historical data is overwritten", vbYesNo, "Final") = vbYes Then
        
        'declare constant variables (oxy moron i know)
        Dim rCount As Integer
        Dim sicsID As String
        Dim LOCID As String
        
        'declare variables for copy to history
        Dim oslrFund As Double
        Dim uprFund As Double
        Dim ibnrFund As Double
        Dim otherFund As Double
        Dim user As String
        Dim modTime As Date
        Dim tt As String
        Dim Comment As String
        
        'declare variables for copy to backup
        Dim BsicsID As String
        Dim BoslrFund As Double
        Dim BuprFund As Double
        Dim BibnrFund As Double
        Dim BotherFund As Double
        Dim Buser As String
        Dim Btt As String
        Dim Bcomment As String
        Dim BHistDate As Date
        Dim BHistUser As String
        
        
        'count number of records in subform - may be more effecient to do after set focus
        rCount = DCount("[LOC/Trust Number]", "[SICS ID Detail]", "[SICS ID Detail]![LOC/Trust Number] = Forms![Input Form]![LOC/Trust Number]")
        'set LOC/Trust ID
        LOCID = Forms![Input Form].[LOC/Trust Number]
        'set focus on subform and go to first record
        Me.Input_SubForm.SetFocus
        DoCmd.RunCommand acCmdRecordsGoToFirst
        
        'loop through records
        Do While rCount > 0
         
        'copy info on historical table to backup table
            'set variable values to copy record
            BsicsID = [Input SubForm].Form![SICS ID & UWY]
            BoslrFund = DLookup("[OSLR Funding in Place]", "Historical SICS ID Detail", "[SICS ID & UWY] ='" & BsicsID & "'")
            BuprFund = DLookup("[UPR Funding in Place]", "Historical SICS ID Detail", "[SICS ID & UWY] ='" & BsicsID & "'")
            BibnrFund = DLookup("[IBNR Funding in Place]", "Historical SICS ID Detail", "[SICS ID & UWY] ='" & BsicsID & "'")
            BotherFund = DLookup("[Other Funding in Place]", "Historical SICS ID Detail", "[SICS ID & UWY] ='" & BsicsID & "'")
            Buser = DLookup("UserMod", "Historical SICS ID Detail", "[SICS ID & UWY] ='" & BsicsID & "'")
            Btt = DLookup("TeamTrack", "Historical SICS ID Detail", "[SICS ID & UWY] ='" & BsicsID & "'")
            Bcomment = DLookup("Comment", "Historical SICS ID Detail", "[SICS ID & UWY] ='" & BsicsID & "'")
            BHistDate = DLookup("HistoryDate", "Historical SICS ID Detail", "[SICS ID & UWY] ='" & BsicsID & "'")
            BHistUser = DLookup("HistoryUser", "Historical SICS ID Detail", "[SICS ID & UWY] ='" & BsicsID & "'")
            
            
            'add record to backup table
            Set Db = CurrentDb
            Set Rs = Db.OpenRecordset("Backedup Historical SICS ID Detail", dbOpenDynaset)
        
                ' Create a new record.
                Rs.AddNew
                
                'Assign the field values to the new record
                Rs![SICS ID & UWY] = BsicsID
                Rs![LOC/Trust Number] = LOCID
                Rs![OSLR Funding in Place] = BoslrFund
                Rs![UPR Funding in Place] = BuprFund
                Rs![IBNR Funding in Place] = BibnrFund
                Rs![Other Funding in Place] = BotherFund
                Rs![UserMod] = BHistUser
                Rs![modTime] = BHistDate
                Rs![TeamTrack] = Btt
                Rs![Comment] = Bcomment
                Rs![BackedUp] = Now()
                Rs![BackedUpUser] = fOSUserName
                
                ' Save the record.
                Rs.Update
            
            
        'copy information on subform to history table
            sicsID = [Input SubForm].Form![SICS ID & UWY]
            LOCID = [Input SubForm].Form![LOC/Trust Number]
            oslrFund = [Input SubForm].Form![OSLR Request]
            uprFund = [Input SubForm].Form![UPR Request]
            ibnrFund = [Input SubForm].Form![IBNR Request]
            otherFund = [Input SubForm].Form![Other Request]
            user = [Input SubForm].Form![SICSMod]
            tt = Forms![Input Form].[TeamTrack]
            Comment = [Input SubForm].Form![Comment]
            
            
            'add record to History table
            Set Db = CurrentDb
            Set Rs = Db.OpenRecordset("Historical SICS ID Detail", dbOpenDynaset)
            SQL = "DELETE FROM [Historical SICS ID Detail] WHERE [SICS ID & UWY] ='" & BsicsID & "'"
        
                ' Create a new record.
                Rs.OpenRecordset
                
                'Assign the field values to the new record
                Rs![SICS ID & UWY] = sicsID
                Rs![LOC/Trust Number] = LOCID
                Rs![OSLR Funding in Place] = oslrFund
                Rs![UPR Funding in Place] = uprFund
                Rs![IBNR Funding in Place] = ibnrFund
                Rs![Other Funding in Place] = otherFund
                Rs![TeamTrack] = tt
                Rs![Comment] = Comment
                Rs![HistoryDate] = Now()
                Rs![HistoryUser] = fOSUserName
        
               ' Save the record.
                Rs.Update
        'go to next record
        DoCmd.RunCommand acCmdRecordsGoToNext
        rCount = rCount - 1
        Loop
    
Else
 '   msgboxResponse = MsgBox("Error", vbOKOnly, "Error")
  '  Me.Status = "TT Approved"
End If
End Sub