This is probably very simple, but I was hoping to be able to easily ensure that a combination of a specific "vendor" and "invoice number" are not entered in more than once. A pop up box notifying when such a record exists while entering data in a form would be sufficient. Could someone please help me with this. Thanks!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Message when duplicate of combined data entered
- Thread starter charm820
- Start date
LittleSmudge
Programmer
It sounds like the combination of "vendor" and "invoice number" form the PrimaryKey for this table. Making them the Primary key will solve the problem.
If changing the PK at this point is a real no-no then:-
In the BeforeUpdate event of BOTH fields.. ..
Check that the other field is not null ( else accept it without question )
then open a recordset like
rst.Open "SELECT fldVendor, fldInvoiceNumber FROM tblName WHERE fldVendor = " & Vendor & " AND fldInvoiceNumber = " & InvoiceNumber
If rst.EOF then
'Go ahead and accept data
ELSE
'Reject both data fields
'Send Message to User
End If
rst.Close
etc.. ..
G LS
If changing the PK at this point is a real no-no then:-
In the BeforeUpdate event of BOTH fields.. ..
Check that the other field is not null ( else accept it without question )
then open a recordset like
rst.Open "SELECT fldVendor, fldInvoiceNumber FROM tblName WHERE fldVendor = " & Vendor & " AND fldInvoiceNumber = " & InvoiceNumber
If rst.EOF then
'Go ahead and accept data
ELSE
'Reject both data fields
'Send Message to User
End If
rst.Close
etc.. ..
G LS
- Status
