Access 2003 Prevent duplicate entries based on 3 criteria

[valeriej42]

I have a form to enter training by employee. If the training number, employee and date all together have already been entered I need it to not allow it. If any one of the three is different it can be entered.

[June7]

Options:

1. set those fields as a compound key in the table

2. VBA code in form BeforeUpdate event to validate by checking the entries against data in table, something like:
If DLookup("ID", "tablename", "trainingNum=" & Me.box1 & " AND EmpNum=" & Me.box2 & " AND datefield=#" & Me.box3 & "#") > 0 Then
MsgBox "Data already entered."
Cancel = True
End If

[valeriej42]

I guess part of the problem lies in that these don't come from a single table. That seems to be where a big part of the problems lies. This is a pretty large, old database. I didn't create it I just have to fix it.

[JoeM]

I guess part of the problem lies in that these don't come from a single table.

DLOOKUP works against queries too. So if you create a query that returns all the values you need from your various tables, it seems you should be able to use June's second suggestion .

[valeriej42]

I am very much a noob when it comes to this. I ran a query which made a new table with the data I needed. I used it in the code and got an error that I cancelled the operation.

[Missinglinq]

Is 'training number' defined as an actual Number, or as Text? It makes a difference in the syntax!

The code June7 gave presumes that it is defined as a Number Datatype, but things like ID numbers, telephone numbers, etc, often are defined as Text. Unless you're planning on doing math with an all-digit Field, it probably should be defined as Text!

Linq ;0)>

[JoeM]

I am very much a noob when it comes to this. I ran a query which made a new table with the data I needed. I used it in the code and got an error that I cancelled the operation.

You don't need a Make-Table Query. You can just leave it as a simple Select Query, and have the DLOOKUP reference that (the second argument can be the name of a query instead of the name of a table).

If you are still having problems, post your DLOOKUP formula attempt.

[valeriej42]

Training number is an autonumber. It actually comes from another form where new training is entered and gives it a number. Employee ID is text.

[June7]

Yes, criteria for text fields must have apostrophe delimiters, like:

[Training]='" & Me.Training & "' AND

I don't think the message was an error, but was a notice that the record update was canceled because the DLookup returned a value so the criteria were found.

[valeriej42]

I am so lost. I tried again and now I'm getting a syntax error. I'm about ready to chunk the whole PC. This isn't even the only problem I have. Just one I'm trying to solve today. Code is below.

If DLookup("Employee_ID", "TBL_EmpTrainDate", "Train_Num=" & Me.CB_TrainNo & " AND Employee_ID='" & Me.CB_AssociatedEmployee & " AND Date_Completed=#" & Me.Date_Completed & "#") > 0 Then
MsgBox "Data already entered."
Cancel = True
End If

[June7]

Employee_ID field is text?

Delimiters must be in pairs. You are missing the second one. Look at the example again.

[valeriej42]

I guess I don't understand what you are saying.

[June7]

Missing the second apostrophe:

& " AND Employee_ID='" & Me.CB_AssociatedEmployee & "' AND

[valeriej42]

Guess I'm still getting it wrong. I'm getting a syntax error on Employee_ID.

[June7]

If you corrected the code as instructed should work.

Otherwise, provide db for analysis. Follow instructions at bottom of my post. Identify the form involved in issue.