Recordsetclone Not working on Subform

Question

I'm having issues getting the this bit of code adjusted for looping through the records in my subform. when the subform loads, it loops through the currently filtered records, adjusting Field 6 based on its status respective status in another table (tDPARSHEET). However im unable to get it to work when i call it from my refresh button on the Main form

With Me.RecordsetClone
If Not (.BOF And .EOF) Then
.MoveFirst
While Not .EOF
Debug.Print .Fields(6).Name, .Fields(6).Value
If .Fields(6).Value <> DLookup("OverallStatus", "tDPARSHEET", "[LocalPartNumber]='" & .Fields(3).Value & "' AND [LocalRevision]='" & .Fields(4).Value & "'") Then
'Debug.Print "No Match"
.Edit
.Fields(6).Value = DLookup("OverallStatus", "tDPARSHEET", "[LocalPartNumber]='" & .Fields(3).Value & "' AND [LocalRevision]='" & .Fields(4).Value & "'")
.Update
Else
'Debug.Print "!!Match Found!!"
End If
.MoveNext
Wend
.Requery
End If
End With

I believe this works on load due to the Me.Recordsetclone being called as the subform loads, if i wanted to call this from the mainform, specifically from a button that i use to refresh the main form, then i would need to call it out directly

Forms!frmMain.frmsub.Form.recordsetclone is the syntax that i have been seeing. Along with any other syntax i've seen around the web [me.frmsub.form.recorsetclone // frmMain.subfrmctrlname.form.recordsetclone // me.frmMain.frmsub.forms.subfrmctrlname.recorsetclone ] Some of them just seemed wrong, but i tried them none-the-less. I've included some form information down below for use in syntax

Mainform: frmDPARTOP
SubForm Form name: frmDPARPARTSSubform
SubForm Ctl Name: subDPARTOP

What am i doing wrong that is not allowing me to get this recordset to work, currently i have Me.subDPARTOP.Form.RecordsetClone in, i don't receive errors, it just doesn't work

Below is The Current Code I am working with after following instructions on here. Current Code does not Reach Debug.Print in Line 5, as far as i can tell, as nothing is shown in immediate window

Private Sub Command31_Click()
'Loops through Subform and updates DPAR Status from tDPARSHEET
'Main Form Name is frmDPARTOP
'Sub Form Name is frmDPARPARTSSubform
'Sub Form Control is subDPARTOP
Dim Field6 As Variant
With Me!subDPARTOP.Form.RecordsetClone
While Not .EOF
    Field6 = DLookup("OverallStatus", "tDPARSHEET", "[LocalCustomer]='" & .Fields(2).Value & "' AND [LocalPartNumber]='" & .Fields(3).Value & "' AND [LocalRevision]='" & .Fields(4).Value & "'")
    Debug.Print .Fields(6).Name, .Fields(6).Value, Field6
    If .Fields(6).Value <> Field6 Then
        Debug.Print "No Match"
       .Edit
           .Fields(6).Value = Field6
       .Update
    Else
        Debug.Print "!!Match Found!!"
    End If
    .MoveNext
Wend
.Requery
End With
'Refreshes DPAR Counter
Me.Customer.SetFocus
Me.Dirty = True
Me.Refresh

End Sub

Gustav Gustav · Accepted Answer · 2015-11-03T13:21:31

It should read:

Dim Field6 As Variant
With Me!SubformCONTROLName.Form.RecordsetClone
    If .RecordCount > 0 Then
        .MoveFirst
    End If
    While Not .EOF
        Field6 = DLookup("OverallStatus", "tDPARSHEET", "[LocalPartNumber]='" & .Fields(3).Value & "' AND [LocalRevision]='" & .Fields(4).Value & "'") The
        Debug.Print .Fields(6).Name, .Fields(6).Value, Field6
        If .Fields(6).Value <> Field6 Then
            Debug.Print "No Match"
           .Edit
               .Fields(6).Value = Field6
           .Update
        Else
            Debug.Print "!!Match Found!!"
        End If
        .MoveNext
    Wend
    .Requery
End With

Recordsetclone Not working on Subform

1 Answers