DCount Help (1 Viewer)

[sneuberg]

I don' t think this should be happening if the queries have the same criteria. I need to look at the queries. Are they the "qry_update_chq_brcd" and "qry_delete_chq_brcd" and are they the same since you last uploaded your database. If not then please upload the revised version.

 

[lookforsmt]

Thanks Steve for looking into the issue
I have attached the latest version v1.4.
I have replaced the below code to the one in the DB.

Park:
    If Err.Number > 0 Then
        MsgBox Err.Number & " " & Err.Description
    End If

    Set qdef = CurrentDb.QueryDefs("qry_delete_chq_brcd")
    qdef.Execute
    MsgBox qdef.RecordsAffected & " " & "temp chq_brcd records deleted"
    Set qdef = Nothing

End Sub

I have kept copy of "temp_chqbrcd__copy" & "temp_envbrcd__copy" for testing purpose

 

[sneuberg]

I suggest first making a select query that matches the update query qry_update_chq_brcd. You can find what I think that is in the attached database as qry_select_chq_brcd. As an aside I don't think you need the tblCaptureDate in these queries but I'll leave that up to you. This select query returns the chgbrcd that were updated in the tbl_Master so you can use its results to determine what gets deleted in the temp_chqbrcd by putting it in the delete query as a subquery in the WHERE clause. The basic outline of this idea is

DELETE *
FROM temp_chqbrcd
WHERE  chqbrcd IN (qry_select_chq_brcd);

except that you can't put a querydef in a subquery like that so in place of qry_select_chq_brcd in the above the SQL of qry_select_chq_brcd needs to go. I change the qry_delete_chq_brcd in the attached database to this but I didn't test this so if you used it make sure it's doing what you want.

You might notice that this delete query looks strange in the grid mode. The Access query designer doesn't have a good way of representing subqueries in this mode. Microsoft really didn't help us in this area and they let us down for Union queries too.

 

[lookforsmt]

Hi! Steve, I have done the below as a work around.

Step1: Added field in temp_chqbrcd with the name "Status"

Sep2: Run a new update query to update the field matching MICR between tbl_Master & temp_chqbrcd to update the "Status" in temp_chqbrcd

Step3: Run the delete query with criteria "Match" to be deleted.

This will avoid deleting the UnMatch items and the user can investigate the mis-match

I want to thank you for your advice and support in helping me through

regards

 

[lookforsmt]

hi Steve, thank you for your support, apologies for delay in replying after so long time. as I was away on long leave and limited access to my laptop

thanks again