Apply Check on Duplicate Entry

Waheed2008

Registered User.
Local time
Today, 19:20
Joined
Jul 17, 2008
Messages
57
Hi all,

I need some help in making a check on a form. The situation is that a company get invoices and these need to be maintained into a database. For that I have made a table with fields:

ID, Code, From, InvoiceNo, Date, Amount, Status

On the bases of this table, I have designed a form having all the above fields. Now I want that if a person try to make a duplicate entry of same invoice received, by mistake, this form recognize this entry and prompt the user. The decision would be on the bases of From & InvoiceNo. No two entries will have same From & InvoiceNo.
What can be the way to apply this check? Or is there any way to work around?
By the way, ID is PK.

Thanks in anticipation
 
Last edited:
This two fields put in INDEX with properties UNIQUE = YES.
Look at attachment.
 

Attachments

Place this into the BeforeUpdate Event for the InvoiceNo TextBox on Form:

Code:
If Nz(DLookup("[ID]", "[[COLOR="Red"][I]YourTableName[/I][/COLOR]]", "From='" & _
              Me.[COLOR="Red"][I]FromFieldOnForm[/I][/COLOR] & "' AND InvoiceNo=" & _
              Me.[COLOR="Red"][I]InvoiceNoFieldOnForm[/I][/COLOR]), 0) > 0 Then
   MsgBox "The Invoice Number you have supplied is already in use.", _
           vbExclamation, "Invoice Number Error"
   Cancel = True
   Exit Sub
End If

.
 
Thanks a lot to both of you.

I have tried both methods and they work pretty well.

Thank you so much.
 
The method MStef has provided is the better way to go and should be done. The method I provided allows you to trap the situation before it reaches the table and therefore provide your own message rather than the default MS-Access message and to perhaps handle other things such as provide assistance to the User.

Your Invoice numbers should be created automatically as well so that there is no possibility of a duplicate.

.
 

Users who are viewing this thread

Back
Top Bottom