I have an issue with duplicate invoice numbers.
I have a form built on tblPayments and [InvoiceNo] is the field I enter the invoice number.
I built a duplicates query as follows:
SELECT tblPayments.InvoiceNo, tblPayments.Amount, tblPayments.PaymentDate, tblPayments.ExpectedPmtDate, tblPayments.NotPaid, tblPayments.DateSentHB
WHERE (((tblPayments.InvoiceNo) In (SELECT [InvoiceNo] FROM [tblPayments] As Tmp GROUP BY [InvoiceNo] HAVING Count(*)>1 )))
ORDER BY tblPayments.InvoiceNo;
When I tab out of [InvoiceNo] the AfterUpdate event fires and the query appears but it shows all duplicates and I dont want to see them all. I want filter on the the current record [tblPayments.InvoiceNo], which would be the record I am entering.
Let me know if it is not clear.