Why can't I Update a subform recordset?

Please post the code that creates the recordset and sets the form's recordsource.

Hi Peter,
In this case the code is quite simple and could easily be handled by Filter and FilterOn properties but on other forms the filtering can be quite complex. The subform record source is set by"
Me.frmSmallGroupSubform.Form.RecordSource = AGSQLSelect & AGSQLWhere & AGSQLOrderby in the Sub CreateFilter where the three variables are public and are set in Form_Load

It doesn't seem to be the SQL as I can cut from the Form Properties and  paste it into the code. It seems to be that as soon as you go me.subform.form.recordsource =  , me.subform.form.requery some other property on the form must be being reset but I can't figure what and how to fix it.

Option Compare Database

Public ComboSQLSelect As String
Public ComboSQLWhere As String
Public ComboSQLOrderby As String
Public AttSQLSelect As String
Public AttSQLWhere As String
Public AttSQLOrderby As String
Public AGSQLSelect As String
Public AGSQLWhere As String
Public AGSQLOrderby As String

Private Sub CreateFilter()

On Error GoTo Err_CreateFilter

If Me.End_dtFilter.Value = False Then
    ComboSQLWhere = " WHERE " & GFilter
    GSQLWhere = " WHERE " & GFilter
    AGSQLWhere = " "
Else
    ComboSQLWhere = " WHERE " & GFilter & " And ((G.End_dt) Is Null)"
    GSQLWhere = " WHERE " & GFilter & " AND ((G.End_dt) Is Null) AND ((F.End_dt) Is Null) AND ((A.End_dt) Is Null)"
    AGSQLWhere = " WHERE((AG.End_dt) Is Null)"

End If
Me.findgroupcombo.RowSource = ComboSQLSelect & ComboSQLWhere & ComboSQLOrderby
MsgBox Me.findgroupcombo.RowSource
Me.Form.RecordSource = GSQLSelect & GSQLWhere & GSQLOrderby
Me.frmSmallGroupSubform.Form.RecordSource = AGSQLSelect & AGSQLWhere & AGSQLOrderby
Me.findgroupcombo.Requery
Me.Requery
Me.frmSmallGroupSubform.Form.Requery
Me.Refresh
    
Exit_CreateFilter:
    Exit Sub

Err_CreateFilter:
    MsgBox Err.Description
    Resume Exit_CreateFilter
End Sub

Private Sub Form_Load()
On Error GoTo Err_Form_Load

Dim Action As Integer
Dim ActionDuration As Integer
Dim ActionType As String
Dim con As New ADODB.Connection

ActionType = "Staff"
Action = 1

' GAQType_ID is ActionType_ID "Supervisor" was read from table roles
If GlCurrentUserActionPartner(GAQType_ID) <> "Supervisor" Then
    MsgBox "You don't have permission to access this form."
    DoCmd.OpenForm "frmswitchboard"
    DoCmd.Close acForm, "frmSmallGroup", acSavePrompt
    DoCmd.Maximize
    Exit Sub
End If

Me![FormHeaderLabel].Caption = SName & " " & GGroup & " Growth Groups"

ComboSQLSelect = "SELECT G.Group_ID, G.Code, G.Name FROM tblGroup AS G"
ComboSQLWhere = " WHERE " & GFilter & " And ((G.End_dt) Is Null)"
ComboSQLOrderby = " ORDER BY G.Code;"
GSQLSelect = "SELECT G.Address, F.Address1, F.Suburb, F.Postcode, G.Group_ID, G.Code, G.GroupType_ID, G.Name, G.Description, G.Day, G.Time, G.Frequency, G.Purpose, G.Notes, G.Start_dt, G.End_dt, G.Update_dt, G.Update_by, G.Person_responsible, A.Mobile, A.Email1, G.GrowthGroup, GT.Type_code FROM ((tblGroup AS G INNER JOIN tblFamily AS F ON G.Address = F.Family_ID) INNER JOIN tblAttendee AS A ON G.Person_responsible = A.Attendee_ID) INNER JOIN tblGroupType AS GT ON G.GroupType_ID = GT.GroupType_ID"
GSQLWhere = " WHERE " & GFilter & " AND ((G.End_dt) Is Null) AND ((F.End_dt) Is Null) AND ((A.End_dt) Is Null)"
GSQLOrderby = " ORDER BY G.Code;"
AGSQLSelect = "SELECT AG.AttendeeGroup_ID, AG.Attendee_ID, AG.Group_ID, AG.Start_dt, AG.End_dt, AG.Role, AG.Notes, AG.Update_dt, AG.Updated_by, AG.Attendance, G.Code, A.Mobile, F.HPhone, F.Family_ID, F.DirectoryEntry, AG.LastAttended, AP.Pager_no, A.Employer, A.Grade FROM ((tblAttendeeGroup AS AG INNER JOIN (tblAttendee AS A INNER JOIN tblFamily AS F ON A.Family_ID = F.Family_ID) ON AG.Attendee_ID = A.Attendee_ID) INNER JOIN tblGroup AS G ON AG.Group_ID = G.Group_ID) LEFT JOIN tblAttendeePager As AP ON A.Attendee_ID = AP.Attendee_ID"
AGSQLWhere = " WHERE((AG.End_dt) Is Null)"
AGSQLOrderby = " ORDER BY A.LastName, A.PreferredName;"
CreateFilter
Me.Refresh
DoCmd.Maximize
Me.Requery
Me!findgroupcombo.SetFocus

con.ConnectionString = ADODBConnString
con.Open

If getActVar(con, ActionType, Action, ActionDuration) Then
    Me![Days] = ActionDuration
Else
    Err.Raise Number:=21110, _
    Description:="Failed to get Staff Action 1 time"
End If

con.Close
Set con = Nothing

Exit_Form_Load:
    Exit Sub

Err_Form_Load:
    MsgBox Err.Description
    Resume Exit_Form_Load
End Sub

Select allOpen in new window

The error was mine. I thought I had gone back to first principles and checked it all out put had an extra join in the SQL which shouldn't have been there.

Thanks Peter for your help.