Assistance needed for query/form in database

[ramez75]

Hi,

Well I am getting stuck or maybe looking at it in a wrong way. So will appreciate any comments/feedback or help on this matter. I have attached the database if someone have time to look at it.

Well I have a table that list all the Suppliers critical and non-critical "tblAVL". I have to audit the critical suppliers every 2 years. So I am trying to put together this database to be able to track the supplier audits that needs to be performed on schedule and the auditors auditing them.
I created a Make table query "qrySup" with criteria of looking at only the critical suppliers and the ones that have been more than 2 years. The make table query "qrySup" creates "tblSup. On the other hand I created a form "frmSupplierAuditSchedule" that will represent my Vendor Audit Schedule. The form has a subform "frmSupplierAuditScheduleSubform". The form and subform well this where I dont know how to go about but for now are linked to table "tblSupplierAuditSchedule" and "tblSupplierAuditScheduleSubform". The form also tracks revision history.

Ok so the problem I am having or cant figure out is I want the form "frmSupplierAuditSchedule" to also use also "tblSup" which have the vendors that needs to be audited and what material they provide and where they are located and dump that info in the same fields I created in "tblSupplierAuditScheduleSubform". If this is done then I can go in and assign the appropriate Auditor, Target Quarter and Type of Audit. Ofcourse the other trick is if for some reason I had to add a new or remove critical/non-critical supplier from "tblAVL". I will have to run the Make table query again to re-fill "tblSup" and then feed it back to "tblSupplierAuditScheduleSubform" but keeping the info of the Auditor, Target Quarter and Type of Audit for the suppliers that werent impacted in "tblSupplierAuditScheduleSubform" undeleted.

I hope I am making sense and not confusing

Thanks in advance

RB

 

[ramez75]

Ok so I have now a working vba to delete duplicate records since I am using an append query. The problem I am trying to resolve now is how do I delete the newer record duplicated as a result of the append query and keep the older one since I have other fields with
or example

I have a supplier name (field=Supplier) XYZ assigned to be audited by John Doe (field=Auditor).
So when I run the append query through a vba attached to tblAVL Onclick event (close) it will create duplicate supplier XYZ. So the next part of the vba will delete the duplicate. I want it to delete the row that of Supplier=XYZ with Auditor field = blank

Any help on this is greatly appreciated