I am trying to loop through each client in the tblclients table and for each client delete some records in another table.
I have a string select statement that changes for each client BUT the docmd.runsql method says it needs an argument consisting of an sql string which I though I had passed it
if i add brackets i get the same.
Private Sub BtnRemoveDeletedCWCodesFromClients_Click()Dim db As DatabaseSet db = CurrentDbDim rs As DAO.RecordsetDim clientId As IntegerDim cwcodetounmatch As StringDim StrSelect As StringDim strFrom As StringDim strWhere As StringDim newsql As StringDim sqlstr As StringSet rs = db.OpenRecordset("tblclients")StrSelect = "SELECT * "With rsrs.MoveFirst Do While Not rs.EOF Select Case rs.Fields("clientid").Value Case 1 strFrom = "FROM AbiToMvris " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = AbiToMvris.[mvris code]))<>False));" Case 2 strFrom = "FROM GlassesToMvris " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = GlassesToMvris.[mvris code]))<>False));" Case 3 strFrom = "FROM CW_to_IDS " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CW_to_IDS.[mvriscode]))<>False));" Case 4 strFrom = "FROM CWCode_To_Continental " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CWCode_To_Continental.[cwcode]))<>False));" Case 5 strFrom = "FROM HalfordsToMvris " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = HalfordsToMvris.[mvriscode]))<>False));" Case 6 strFrom = "FROM CWCode_To_Michelin " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CWCode_To_Michelin.[cwcode]))<>False));" Case 7 strFrom = "FROM TechDocToMvris " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = TechDocToMvris.[mvris code]))<>False));" Case 8 strFrom = "FROM VividToMvris " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = VividToMvris.[mvriscode]))<>False));" Case 9 strFrom = "FROM CapToMvris-CW " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CapToMvris-CW.[mvriscode]))<>False));"End Select sqlstr = StrSelect & strFrom & strWhere DoCmd.RunSQL sqlstr LoopEnd WithEnd Sub
it was me being stupid I was testing but change the query to a select query which I subsequently discovered docmd.runsql does not run, now its changed to delete ..... its working.
Private Sub BtnRemoveDeletedCWCodesFromClients_Click()Dim db As DatabaseSet db = CurrentDbDim rs As DAO.RecordsetDim clientId As IntegerDim cwcodetounmatch As StringDim StrSelect As StringDim strFrom As StringDim strWhere As StringDim newsql As String'Set qd = db.QueryDefs(stringfull)Dim sqlstr As StringDim qd As DAO.QueryDefSet qd = db.QueryDefs("QryDelFromMatchTable")sqlstr = qd.SQL'DoCmd.SetWarnings FalseSet rs = db.OpenRecordset("tblclients")' DoCmd.SetWarnings = FalseWith rsrs.MoveFirst Do While Not rs.EOF Select Case rs.Fields("clientid").Value Case 1 StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = AbiToMvris.[mvris code]), * " strFrom = "FROM AbiToMvris " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = AbiToMvris.[mvris code]))<>False));" Case 2 StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = GlassesToMvris.[mvris code]), * " strFrom = "FROM GlassesToMvris " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = GlassesToMvris.[mvris code]))<>False));" Case 3 StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CW_to_IDS.[mvriscode]), * " strFrom = "FROM CW_to_IDS " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CW_to_IDS.[mvriscode]))<>False));" Case 4 StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CWCode_To_Continental.[cwcode]), * " strFrom = "FROM CWCode_To_Continental " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CWCode_To_Continental.[cwcode]))<>False));" Case 5 StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = HalfordsToMvris.[mvriscode]), * " strFrom = "FROM HalfordsToMvris " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = HalfordsToMvris.[mvriscode]))<>False));" Case 6 StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CWCode_To_Michelin.[cwcode]), * " strFrom = "FROM CWCode_To_Michelin " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CWCode_To_Michelin.[cwcode]))<>False));" Case 7 StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = TechDocToMvris.[mvris code]), * " strFrom = "FROM TechDocToMvris " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = TechDocToMvris.[mvris code]))<>False));" Case 8 StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = VividToMvris.[mvriscode]), * " strFrom = "FROM VividToMvris " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = VividToMvris.[mvriscode]))<>False));" Case 9 StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CapToMvris-CW.[mvriscode]), * " strFrom = "FROM CapToMvris-CW " strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CapToMvris-CW.[mvriscode]))<>False));"End Select newsql = StrSelect & strFrom & strWhere' Debug.Print newsql' qd.SQL = newsql' DoCmd.OpenQuery ("QryDelFromMatchTable") DoCmd.RunSQL newsql rs.MoveNext LoopEnd With'DoCmd.SetWarnings TrueEnd Sub
Open in new window