2 unique Values

[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

 

[Larry Linson]

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

 

[Anthony Viscomi]

This is an Accounts Payable system, thus the invoice #'s already exist.

 

[John Vinson]

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.

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]

 

[Anthony Viscomi]

Thanks all!

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]