Recordset is not updateable when try from form but work fine if only subform loaded

Question

I have a form with datasheet subform. When I try to load the main form and update some of the fields in the subform it gives me the error : (Recordset is not updateable)

but when I load the subform by itself it works fine. Iam using Access 2007.

Code under one of the files is:

Private Sub No_open_quality_issues_Click()
If (Me.No_open_quality_issues.Value = True) Then
    Me.No_open_quality_issues.Locked = True
End If
End Sub

it looks like the problem happen when I change the source record source from :

SELECT Production_Readiness.*
FROM Production_Readiness;

To :

SELECT DISTINCT Production_Readiness.*
FROM Production_Readiness
WHERE (((Production_Readiness.Material) Like '' & "*" Or (Production_Readiness.Material) Is Null) AND ((Production_Readiness.[All Engine Models]) Like '' & "*" Or (Production_Readiness.[All Engine Models]) Is Null) AND ((Production_Readiness.Commodities) Like '' & "*" Or (Production_Readiness.Commodities) Is Null) AND ((Production_Readiness.[Buyer Name]) Like '' & "*" Or (Production_Readiness.[Buyer Name]) Is Null) AND ((Production_Readiness.Vendor) Like '' & "*" Or (Production_Readiness.Vendor) Is Null) AND ((Production_Readiness.[Vendor Name]) Like '' & "*" Or (Production_Readiness.[Vendor Name]) Is Null) AND ((Production_Readiness.[MinOfStat-Rel Del Date     Next coming delivery])>=#5/16/1892# And (Production_Readiness.[MinOfStat-Rel Del Date     Next coming delivery])<=#3/15/2016#) AND ((Production_Readiness.[Buyer Badge])=FctUserID()) AND ((Production_Readiness.Status)<>'Processed'));

to be more specific it happen when I try to use this function on loading the form :

Private Function LoadHOQuery()

progComb_Filter = "Like '' & ""*"" Or (Production_Readiness.[All Engine Models]) Is Null"
partNoCmb_Filter = "Like '' & ""*"" Or (Production_Readiness.Material) Is Null"
commodCmb_Filter = "Like '' & ""*"" Or (Production_Readiness.Commodities) Is Null"
custCmb_Filter = "Like '' & ""*"" Or (Production_Readiness.[Buyer Name]) Is Null"
subCodeCmb_Filter = "Like '' & ""*"" Or (Production_Readiness.[Vendor]) Is Null"
subCmb_Filter = "Like '' & ""*"" Or (Production_Readiness.[Vendor Name]) Is Null"
startDateTxt_Filter = "5/16/1892"
endDateTxt_Filter = Date
Dim sqlCmd As String

Dim isFormLoades As Boolean
If (openCondition = "Buyer") Then
    isFormLoades = IsLoaded("Handoff_Frm")
    If (isFormLoades) Then
        sqlCmd = "SELECT DISTINCT Production_Readiness.* " & _
                 "FROM Production_Readiness " & _
                 "WHERE (((Production_Readiness.Material) " & partNoCmb_Filter & ") AND ((Production_Readiness.[All Engine Models]) " & progComb_Filter & ") AND " & _
                        "((Production_Readiness.Commodities) " & commodCmb_Filter & " ) AND ((Production_Readiness.[Buyer Name]) " & custCmb_Filter & " ) AND " & _
                        "((Production_Readiness.Vendor) " & subCodeCmb_Filter & ") AND ((Production_Readiness.[Vendor Name]) " & subCmb_Filter & ") AND " & _
                        "((Production_Readiness.[MinOfStat-Rel Del Date     Next coming delivery])>=#" & startDateTxt_Filter & "#) AND ((Production_Readiness.[MinOfStat-Rel Del Date     Next coming delivery])<=#" & endDateTxt_Filter & "#) And" & _
                        "(((Production_Readiness.[Buyer Badge])=FctUserID()) And ((Production_Readiness.[Status]) <>  'Processed') ) ); "

        'MsgBox sqlCmd


        Me.ManageHanOff.Form.RecordSource = sqlCmd
        Me.ManageHanOff.Form.Requery
     End If
 ElseIf (openCondition = "Manager") Then

 End If

End Function

Can you help me.

Thanks

Answer 1

I found the error and it was actually logic one. The problem in the DISTINCT. This is why I can not update because it is more then one record with the same values and the access have no clue which to update.