The problem I am having is that, the if statement does not seem to be correct as it’s not actually checking to match up a claim # for one table and matching it to another. If there is a match then it’s supposed to run a delete query to remove all these matches. If there is a discrepancy it’s supposed to preview the claims that the claim # does not match up

Private Sub Delete_Claim_CMD_Click()
'matches records and deletes ones that have been inputted into the Customer Return Database
Dim stDocname As String
stDocname = "RptAllCustRet"
'Matches up claim number on the master list and the OP Customer returns
If [tbl_master_data]![RPT_CLAIM_NO] = [TblAllCustRet]![RPT_CLAIM_NO] Then
'Runs the query to delete all non matching / missing / complete claims
DoCmd.OpenQuery (DeleteClaim)
ElseIf [tbl_master_data]![RPT_CLAIM_NO] <> [TblAllCustRet]![RPT_CLAIM_NO] Then
' Opens the report to show the incomplete claims
DoCmd.OpenReport stdDocName, acPreview
End If

End Sub

My delete query works but it removes all fields once run

Also one more thing since I am at it
I am looking for a command line that will do this
Query to find a field “metric flag” ‘M’ and a weight  done
Now I want to convert this weight only flagged with the “M”
If Metric_Flag = “M” then Bundle_Weight = Bundle_Weight*2.20
Is this possible?

By looking at your code, I see a lot of potential problems.

First, where is this code located? It looks like it's a button on your form called Delete_Claim_CMD. It also looks like the code is run when someone clicks on the button.

If so, which table is the record source for your form? Is it tbl_master_data or TblAllCustRet or something else or nothing (an unbound form)?

Which tables are open at the time the person clicks the button? Is there another form open that has another table as its record source? Is the code you posted mentioning tables that are not open by any form at all at the time the user clicks the button?

Putting a criteria in your query in the claim number field mentioning any open form's record's claim number value will also simplify the code. But I'd have to know which form is open when the query is run.

The answers to these questions will determine what your code should look like.

__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division