Check if already entered

[jamesormi]

Hi,

I have a form into which the user enters details from customer invoices. I would like to add a check to make sure that an invoice number is not duplicated for the customers. The table has the fields OurRef (Prim Key), Customer and Invoice Number.

Is there a way I can check that for a selected customer the invoice number entered is not already in the table?

Thanks in advance.

 

[neileg]

Create an index based on the customer and invoice and set its properties to No Duplicates.

 

[DCrake]

Normally invoice numbers are strictly sequential across all customers so in theory no ivoice numbers can be repeated across customers. What you are implying is that this can happen. Are my assumptions correct or not?

David

 

[jamesormi]

What I'm looking to do is to stop the user from entering the same invoice twice by mistake. Belt and Braces if you like...

 

[DCrake]

First of all you need to look at how you first generate the next intended invoice number. If this is based on one number higher than the last one in the table you need to generate this at the point of adding the record.

Example: VBA

Table.AddNew
Table("InvoiceNo") = GetNextInvoiceNumber()
...
Table.Update


Public Function GetNextInvoiceNumber() As Long

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("Select * From Invoices Order By InvoiceNo DESC;")

GetNextInvoiceNumber = Rs("InvoiceNo") +1

Rs.Close
Set Rs = Nothing

End Function

Example : DMax()

Invno = DMax("InvoiceNo","Invoices")+1

Using either method should exclude the need to check for duplicates