Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

Why get error when delete all records on a sub-form

I get an error when deleting ALL the records in a sub-form.  The error is "The value you entered isn't valid for this field".

And when I try to compile, the line "Me.Parent.Parent.Form.txtTotal = rst.Fields("Total")" is highlighted in yellow.

The code is:

Private Sub RecalcValues()

    Dim rst As DAO.Recordset
    Dim strSQL As String

    If Not IsNull(Me.Parent.Parent.Form.txtMNCMNUM) Then
        strSQL = "SELECT CRMAIN.MNCMNUM, Sum(WRDETAIL.QUANTITY*WRDETAIL.DNPRICE) AS SumOfDNPRICE, Sum(WRDETAIL.DNLABOR) AS SumOfDNLABOR, ([SumOfDNPRICE]+[SumOfDNLABOR]) AS Total "
        strSQL = strSQL & "FROM (CRMAIN INNER JOIN WRHEADER ON CRMAIN.MNCMNUM = WRHEADER.HNCMNUM) INNER JOIN WRDETAIL ON WRHEADER.HNWRNUM = WRDETAIL.DNWRNUM "
        strSQL = strSQL & "GROUP BY CRMAIN.MNCMNUM "
        strSQL = strSQL & "HAVING (((CRMAIN.MNCMNUM)=" & Me.Parent.Parent.Form.txtMNCMNUM & "));"
    
        Set rst = CurrentDb.OpenRecordset(strSQL)
    
        Me.Parent.Parent.Form.txtTotal = rst.Fields("Total")
    
        rst.Close
        
    Else
        Me.Parent.Parent.Form.txtTotal = 0
    End If

    Set rst = Nothing

End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SteveL13

ASKER

Nice!  Thanks.