RE: [MS_AccessPros] Check for duplicate Invoices

 

Bob,

The PK of this table is the VoucherID. Are you saying to make a combination PK of VoucherID, Invoice Number and Vendor Number?

Doyce

---In MS_Access_Professionals@yahoogroups.com, <bob@...> wrote :

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

 

__._,_.___

---

Posted by: winberry.doyce@roadsysinc.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___