ACCESS FEED: RE: [MS_AccessPros] Check for duplicate Invoices

Selasa, 09 Juni 2015

RE: [MS_AccessPros] Check for duplicate Invoices

How about using a unique index on a combination of the vendor ID and invoice number.

Force a save after the invoice number is entered with me.dirty=false then catch the error when Access tries to save.

Actually the outer form will save when focus moves to the sub form and you can catch the error there. Tell them the mistake with a message box and move focus back to the invoice number control.

Bob Peterson

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, June 09, 2015 2:38 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Check for duplicate Invoices

Hello friends,
I have a form where we enter invoices to be paid into Accounts Payables. I have a new entry clerk that keeps entering the same invoice to be paid. I need to add some data verification checks to stop this. The form starts with a combo box to select the vendor. Then the user keys in an invoice number. Is it better to put some code in the afterupdate event of the Invoice Number control or in the before update event of the form or both? This form has a subform where the line item details are added but I want to stop the user before they get that far. Also, would someone please provide some code sample? I have created a query that will identify the duplicate invoices from the invoiceheader table. Here it is:
SELECT InvoiceHeader.[VendorID], InvoiceHeader.[InvoiceNumber], InvoiceHeader.[InvoiceDate], InvoiceHeader.[InvoiceAmount], InvoiceHeader.[Voucher#]
FROM InvoiceHeader
WHERE (((InvoiceHeader.[VendorID]) In (SELECT [VendorID] FROM [InvoiceHeader] As T mp GROUP BY [VendorID],[InvoiceNumber] HAVING Count(*)>1 And [InvoiceNumber] = [InvoiceHeader].[InvoiceNumber])))
ORDER BY InvoiceHeader.[VendorID], InvoiceHeader.[InvoiceNumber];

Thanks,
Doyce