2 unique Values

  • Thread starter Thread starter Anthony Viscomi
  • Start date Start date
A

Anthony Viscomi

I have an Invoice frm (& tbl) and I want to allow the user to enter an
invoice # and select a vendor from a cboVendor. But if that Vendor and
Invoice# combination already exist; then display msg.

Simply put, how do I prevent entering a duplicate invoice from th same
vendor?

Thanks in advance!
Anthony
 
How are Invoice #s determined? You could create the Invoice # from code to
assure that it did not conflict with an existing one.

Larry Linson
Microsoft Access MVP
 
I have an Invoice frm (& tbl) and I want to allow the user to enter an
invoice # and select a vendor from a cboVendor. But if that Vendor and
Invoice# combination already exist; then display msg.
Click to expand...

You could create a unique Index on the combination of the two fields
to absolutely prevent addition of such a record.

If you just want to check, use the Form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("InvoiceNo", "Invoices", "InvoiceNo = " _
& Me.txtInvoiceNo & " AND VendorID = " & Me.cboVendor) Then
MsgBox "This vendor has already been entered for this invoice"
Cancel = True
End If
End Sub

John W. Vinson[MVP]
 
Thanks all!
John Vinson said:
You could create a unique Index on the combination of the two fields
to absolutely prevent addition of such a record.

If you just want to check, use the Form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("InvoiceNo", "Invoices", "InvoiceNo = " _
& Me.txtInvoiceNo & " AND VendorID = " & Me.cboVendor) Then
MsgBox "This vendor has already been entered for this invoice"
Cancel = True
End If
End Sub

John W. Vinson[MVP]
Click to expand...
 
Back
Top