I'm working on a database to keep track of Asset Protection violations at my company. I have a form with bound textboxes and combo boxes that include the date, officer reporting, employee's first and last name, employee ID, bulding, floor, cube number, violation type, and violation number in the form. What I'm looking to do, and I have not been able to find any information on this anywhere on the web is to have the results of the employee ID and violation type dictate what violation number one can enter. Basically if one enters the ID a123456 for the violation "unsecured product" and tries to enter 1st violation, but that is already in the databse, an error message pops up telling them to try another violation number before the record can be entered. Can anyone help me out on this? Once I figure this out, I can publish my database for my team to use.

Write a query that looks up if there is a first violation and have it run on your form...probably after update of that field. If the result is positive, display a msgbox and clear the field - if not, let it accept.

Consider making employee id and violation type the primary key.
Handle the error when some enters a row which violates that.
You could check ahead as soon as the user enters the employee id and violation type in the respective after update events for the controls