Apply Check on Duplicate Entry (1 Viewer)

W

Waheed2008

Registered User.

Local time

Tomorrow, 02:10

Joined

Jul 17, 2008

Messages

57

• Aug 27, 2008

• #1

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

Click to expand...

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: Aug 27, 2008

M

MStef

Registered User.

Local time

Today, 23:10

Joined

Oct 28, 2004

Messages

2,251

• Aug 27, 2008

• #2

This two fields put in INDEX with properties UNIQUE = YES.
Look at attachment.

 

Attachments

• Index1.zip

  16.9 KB · Views: 119

C

CyberLynx

Stuck On My Opinions

Local time

Today, 15:10

Joined

Jan 31, 2008

Messages

585

• Aug 27, 2008

• #3

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

Code:Copy to clipboard

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

.

 

W

Waheed2008

Registered User.

Local time

Tomorrow, 02:10

Joined

Jul 17, 2008

Messages

57

• Aug 27, 2008

• #4

Thanks a lot to both of you.

I have tried both methods and they work pretty well.

Thank you so much.

 

C

CyberLynx

Stuck On My Opinions

Local time

Today, 15:10

Joined

Jan 31, 2008

Messages

585

• Aug 28, 2008

• #5

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.

.