Avatar of john_paterson_kbc
john_paterson_kbc
Flag for Australia asked on

Why can't I Update a subform recordset?

Hi
In Access 2003 I have a form with a subform which works as expected. However as soon as I set the subforms record source from VBA and do a requery of the subform the recordset us not updateable, even if the record source is identical to that set in the subform properties. Is there a way around this?
Microsoft Access

Avatar of undefined
Last Comment
john_paterson_kbc

8/22/2022 - Mon
peter57r

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

ASKER
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

Open in new window

ASKER CERTIFIED SOLUTION
peter57r

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
john_paterson_kbc

ASKER
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy